SQL projects

WITH open_invoices AS (
  SELECT *,
    CASE credit_risk WHEN 'High' THEN 1.30
      WHEN 'Medium' THEN 1.12 ELSE 1.00 END AS risk_weight,
    CASE WHEN dispute_flag = 1 THEN 0.72 ELSE 1.00 END AS collectability,
    CASE WHEN days_past_due >= 91 THEN 1.65
      WHEN days_past_due >= 61 THEN 1.45
      WHEN days_past_due >= 31 THEN 1.25
      WHEN days_past_due >= 1 THEN 1.08 ELSE 0.35 END AS lateness_weight
  FROM ar_status WHERE payment_date IS NULL
)
SELECT *,
  invoice_amount_gbp * risk_weight * collectability * lateness_weight AS action_score,
  DENSE_RANK() OVER (PARTITION BY account_owner
    ORDER BY invoice_amount_gbp * risk_weight * collectability * lateness_weight DESC) AS owner_rank
FROM open_invoices;

WITH segment_performance AS (
  SELECT region, channel, product,
         SUM(revenue_gbp) AS revenue_gbp,
         100.0 * SUM(gross_margin_gbp)
           / NULLIF(SUM(revenue_gbp), 0) AS margin_pct
  FROM retail_margin_transactions
  GROUP BY region, channel, product
), ranked AS (
  SELECT *,
         NTILE(4) OVER (ORDER BY revenue_gbp DESC) AS revenue_quartile,
         DENSE_RANK() OVER
           (PARTITION BY region ORDER BY margin_pct) AS margin_risk_rank
  FROM segment_performance
)
SELECT * FROM ranked
WHERE revenue_quartile = 1 AND margin_risk_rank <= 3;

WITH sector_history AS (
  SELECT sector,
    AVG(CASE WHEN outcome = 'Won' THEN 1.0 ELSE 0.0 END)
      FILTER (WHERE outcome IN ('Won', 'Lost')) AS base_probability
  FROM tender_pipeline GROUP BY sector
), scored AS (
  SELECT t.*,
    estimated_value_gbp_m * GREATEST(0.05,
      base_probability
      - GREATEST(competitor_count - 5, 0) * 0.012
      - GREATEST(risk_score - 60, 0) * 0.002
    ) AS expected_value_gbp_m
  FROM tender_pipeline t JOIN sector_history USING (sector)
  WHERE outcome = 'Open'
)
SELECT * FROM scored ORDER BY expected_value_gbp_m DESC;