SQL Benchmarking — Applied Multiple CTEs, FIRST_VALUE

SELECT
  company_name, quarter, revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY company_name               -- Independent window for each company
      ORDER BY     quarter
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Average the previous 2 rows plus the current row
    ), 1
  ) AS moving_avg_3q,
  SUM(revenue) OVER (
    PARTITION BY company_name               -- Independent window for each company
    ORDER BY     quarter
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Cumulative total from the first row through the current row
  ) AS cumulative_rev
FROM   quarterly_revenue
ORDER BY company_name, quarter;

/*
  Execution order (logical SQL evaluation order):
  1. FROM quarterly_revenue         → Read the rows
  2. AVG/SUM OVER (...)             → Evaluate the window functions (row count is preserved)
  3. SELECT                        → Evaluate the columns
  4. ORDER BY company_name, quarter → Sort and return the result
*/

SELECT
  category, company_name, annual_revenue,
  ROUND(
    (PERCENT_RANK() OVER (
      PARTITION BY category             -- Independent window for each category
      ORDER BY     annual_revenue        -- Ascending: minimum=0%, maximum=100%
    ) * 100)::numeric, 1
  ) AS pct_rank,
  ROUND(
    (CUME_DIST() OVER (
      PARTITION BY category             -- Independent window for each category
      ORDER BY     annual_revenue        -- The share of values at or below the current value
    ) * 100)::numeric, 1
  ) AS cume_dist_pct
FROM   category_performance
ORDER BY category, annual_revenue DESC;

/*
  Execution order (logical SQL evaluation order):
  1. FROM category_performance       → Read the rows
  2. PERCENT_RANK/CUME_DIST OVER     → Evaluate the window functions (row count is preserved)
  3. ROUND(...)                      → Format the values
  4. SELECT                          → Evaluate the columns
  5. ORDER BY category, annual_revenue DESC → Sort and return the result
*/

WITH ranked AS (
  SELECT
    category, company_name, composite_score,
    FIRST_VALUE(company_name) OVER (
      PARTITION BY category                -- Independent window for each category
      ORDER BY     composite_score DESC   -- Descending: first row has the highest score
    ) AS leader_name,
    FIRST_VALUE(composite_score) OVER (
      PARTITION BY category                -- Get the leader score in the same window
      ORDER BY     composite_score DESC
    ) AS leader_score
  FROM   product_scores
)
SELECT
  category, company_name, composite_score,
  leader_name, leader_score,
  ROUND(composite_score - leader_score, 1) AS gap_to_leader  -- Gap from the leader (negative means behind)
FROM   ranked
ORDER BY CASE category
  WHEN 'Cloud' THEN 1
  WHEN 'Security' THEN 2
  WHEN 'Data Analytics' THEN 3
  END, composite_score DESC, company_name;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE ranked        → Evaluate the FIRST_VALUE window functions (row count is preserved)
  2. Outer query               → Evaluate the gap and format it with ROUND
  3. ORDER BY CASE category WHEN 'Cloud' THEN 1 WHEN 'Security' THEN 2 WHEN 'Data Analytics' THEN 3 END, composite_score DESC, company_name → Sort and return the result
*/

WITH share_with_prev AS (
  SELECT
    company_name, period, market_share_pct,
    LAG(market_share_pct) OVER (
      PARTITION BY company_name    -- Independent window for each company
      ORDER BY     period          -- Read the previous period's share in ascending period order
    ) AS prev_share               -- The first period (2023H1) has no previous row, so it is NULL
  FROM   market_share_trend
),
deltas AS (
  SELECT
    company_name, period, market_share_pct, prev_share,
    ROUND(market_share_pct - prev_share, 1) AS share_delta,  -- Period-over-period change
    CASE
      WHEN market_share_pct > prev_share THEN '▲ Growth'
      WHEN market_share_pct < prev_share THEN '▼ Decline'
      ELSE                                    '— Flat'
    END AS trend
  FROM   share_with_prev
  WHERE  prev_share IS NOT NULL           -- Exclude the first-period NULL rows
)
SELECT
  company_name, period,
  market_share_pct, prev_share, share_delta, trend,
  SUM(CASE WHEN trend = '▲ Growth' THEN 1 ELSE 0 END) OVER (
    PARTITION BY company_name          -- Accumulate separately for each company
    ORDER BY     period               -- Accumulate the growth flags in period order
  ) AS expanding_periods
FROM   deltas
ORDER BY company_name, period;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE share_with_prev → Evaluate LAG as a window function (row count is preserved)
  2. Define CTE deltas          → Filter with WHERE and evaluate the change and label
  3. Outer query                → Evaluate the SUM OVER window function (row count is preserved)
  4. ORDER BY company_name, period → Sort and return the result
*/