SQL Batch Processing — Applied CTEs, Window Functions

-- WITH: split processing into a CTE
WITH sales_with_prev AS (
  -- ① Use LAG to place previous-month sales on the current row
  SELECT
    month,
    sales,
    LAG(sales, 1, 0) OVER (
      ORDER BY month
    ) AS prev_sales
  FROM monthly_sales
)
-- ② Calculate the difference and growth rate
SELECT
  month,
  sales,
  prev_sales,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE sales - prev_sales
  END AS diff,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE ROUND((sales - prev_sales)::numeric / prev_sales * 100, 2)
  END AS growth_rate
FROM sales_with_prev
ORDER BY month;

/*
  Evaluation order:
  1. CTE sales_with_prev  → attach previous-month sales with LAG
  2. Outer CASE             → calculate the difference and growth rate
  3. ORDER BY month       → return rows in ascending month order
*/

SELECT
  month,
  sales,
  SUM(sales) OVER (                                   -- ① Running sales total: sum from the first row through the current row
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(                                              -- ② Three-month moving average: average from two rows earlier through the current row
    AVG(sales) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 1
  ) AS moving_avg_3m
FROM monthly_sales
ORDER BY month;

/*
  Evaluation order:
  1. FROM monthly_sales  → read rows
  2. SUM() OVER (...)    → calculate the running sales total
  3. AVG() OVER (...)    → calculate the three-month moving average
  4. ORDER BY month      → return rows in ascending month order
*/

-- WITH: split processing into a CTE
WITH ranked_products AS (
  -- ① Rank sales within each category
  SELECT
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC, product_id
    ) AS rn
  FROM product_sales
)
-- ② Extract the top two rows in each category
SELECT
  category,
  product_name,
  sales,
  rn
FROM ranked_products
WHERE rn <= 2
ORDER BY category, rn;

/*
  Evaluation order:
  1. CTE ranked_products    → rank sales within each category
  2. Outer WHERE rn <= 2      → extract the top two rows per category
  3. ORDER BY category, rn  → sort and return the result
*/

SELECT
  assignee,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) AS done,          -- ① Count done tasks
  SUM(CASE WHEN status = 'in progress' THEN 1 ELSE 0 END) AS in_progress,  -- ② Count tasks in progress
  SUM(CASE WHEN status = 'not started' THEN 1 ELSE 0 END) AS not_started,  -- ③ Count tasks not started
  COUNT(*) AS total                                                -- ④ Count all tasks
FROM tasks
GROUP BY assignee
ORDER BY assignee;

/*
  Evaluation order:
  1. FROM tasks         → read rows
  2. GROUP BY assignee  → group by assignee
  3. SUM(CASE...)       → aggregate counts by status (pivot)
  4. ORDER BY assignee  → return rows in ascending order
*/