SQL projects

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;