“What percentage of sales does each category represent?” and “How far toward the total do we get when accumulating categories from the top?” are the core questions of Pareto and ABC analysis for identifying priority products. Window functions let you place the grand total and a running total next to each detail row without removing the detail rows.

SUM(sales) OVER ()                       -- empty OVER = one window over all rows; attach the grand total to each row
SUM(sales) OVER (ORDER BY sales DESC)   -- running total in the specified order

From the category_sales table, which contains pre-aggregated sales by category, calculate the sales mix (sales_pct) and cumulative sales mix in descending sales order (cum_pct). Return category, sales, sales_pct, cum_pct in descending sales order, and round the ratios to two decimal places.

※ Grand total = 5000+3000+1500+800+700 = 11000.

Expected output (sales descending):

At 72.73% cumulative, the top two categories account for about seven-tenths of the total (the Pareto A group).

SELECT
  category,
  sales,
  ROUND(sales * 100.0 / SUM(sales) OVER (), 2) AS sales_pct,  -- sales mix = each row ÷ grand total
  ROUND(
    SUM(sales) OVER (ORDER BY sales DESC) * 100.0      -- running total
    / SUM(sales) OVER (), 2
  ) AS cum_pct                                              -- cumulative sales mix
FROM category_sales
ORDER BY sales DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM category_sales                    → read the rows
  2. SUM(sales) OVER ()                     → attach the grand total to every row
  3. SUM(sales) OVER (ORDER BY sales DESC)  → calculate the running total in sales-descending order
  4. SELECT calculates the ratios            → calculate the sales mix
  5. ORDER BY sales DESC                    → output in descending sales order
  */

Group-internal rankings such as “the top three products in each category” and “rank within a department” are implemented with PARTITION BY. There are three ranking functions, and the most important point is that they handle ties differently.

RANK()       OVER (PARTITION BY category ORDER BY sales DESC)  -- ties share a rank; skip the next  1,1,3
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC)  -- ties share a rank; do not skip  1,1,2
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC)  -- force consecutive numbers      1,2,3

From the product_sales table, assign the within-category sales rank in three ways: RANK, DENSE_RANK, and ROW_NUMBER. Return category, product, sales, rnk, dense_rnk, row_num ordered by category ascending, sales descending, and product ascending.

※ Food's Apple and Bread both have sales of 500 (a tie). This is where the three functions differ.

Expected output (category ascending, sales descending):

Apple and Bread tie: RANK gives both 1 and makes the next rank 3 (skipping 2), DENSE_RANK makes the next rank 2, and ROW_NUMBER forces the numbers 1 and 2.

SELECT
  category,
  product,
  sales,
  RANK()       OVER (PARTITION BY category ORDER BY sales DESC)          AS rnk,        -- ties share a rank; skip the next
  DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC)          AS dense_rnk,  -- ties share a rank; do not skip
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC, product) AS row_num     -- force consecutive numbers (tie-break with a second key)
FROM product_sales
ORDER BY category, sales DESC, product;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM product_sales          → read the rows
  2. PARTITION BY category       → split by category
  3. ORDER BY sales DESC (inside the window) → sales descending within each partition
  4. RANK/DENSE_RANK/ROW_NUMBER  → assign ranks
  5. ORDER BY                    → arrange the final display order
  */

HAVING narrows the result by an aggregate condition, such as “repeat customers with at least two completed orders.” Understanding its division of labor with WHERE, which filters individual rows, is essential. PostgreSQL's FILTER clause also lets you specify the rows counted by each aggregate function in a readable way.

-- WHERE  : filter “rows” before grouping
-- HAVING : filter “groups” after grouping (aggregate values can be used)

COUNT(*) FILTER (WHERE status = 'completed')   -- count only rows matching the condition
-- = equivalent to SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END), but more readable

From the orders table, extract heavy users with at least two completed orders, and return their completed-order count and completed-amount total. Return user_id, completed_orders, completed_amount ordered by completed_amount descending.

Expected output (completed_amount descending):

U2 has one completed order (<2), and U4 has zero completed orders, so both are excluded. U1's cancelled O3 is excluded, leaving two completed orders totaling 3500.

SELECT
  user_id,
  COUNT(*) FILTER (WHERE status = 'completed')   AS completed_orders,  -- count only completed orders
  COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0) AS completed_amount  -- sum completed amounts only; use 0 when none match
FROM orders
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE status = 'completed') >= 2   -- filter groups after aggregation
ORDER BY completed_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                                   → read 8 rows
  2. GROUP BY user_id                              → form four groups: U1, U2, U3, U4
  3. Aggregation (FILTER targets completed only)   → U1:2/3500, U2:1/500, U3:3/6500, U4:0/0
  4. HAVING completed_orders >= 2                  → exclude U2(1) and U4(0)
  5. SELECT / ORDER BY completed_amount DESC       → output in descending amount order
*/

Churn is the reverse side of retention and a vital KPI for SaaS and subscription businesses. To find “users who were present last month but absent this month,” the standard pattern is an anti-join: LEFT JOIN and keep only rows with no matching row on the other side (IS NULL).

LEFT JOIN ... ON condition
WHERE right_table.key IS NULL   -- no match = extract the set difference
-- present in prev (last month) but absent from curr (this month) = churn

From monthly_active, which records monthly active users, identify users who were active in 2024-03 but inactive in 2024-04. Return user_id in ascending user_id order. Also confirm the churn rate in the explanation.

※ March={U1,U2,U3,U4}; April={U1,U3,U5}. U5 is new, not churned.

Expected output (churned users, user_id ascending):

U2 and U4 were present in March but absent in April, so they churned. Churn rate = 2 churned users ÷ 4 March active users = 50.00%.

SELECT prev.user_id                -- present in March but absent in April = churned user
FROM      monthly_active AS prev
LEFT JOIN monthly_active AS curr
  ON  curr.user_id = prev.user_id
  AND curr.month   = '2024-04'     -- put the right-table filter in ON
WHERE prev.month = '2024-03'
  AND curr.user_id IS NULL         -- no match = inactive in April
ORDER BY prev.user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM prev LEFT JOIN curr      → match the next month to prev (keep unmatched rows as NULL)
  2. WHERE prev.month               → limit to users in the reference month
  3. AND curr.user_id IS NULL       → keep only rows with no match in the next month = churn
  4. SELECT prev.user_id / ORDER BY  → output in ascending order

  -- Churn rate (extra):
  --   COUNT(*) FILTER (WHERE curr.user_id IS NULL) * 100.0 / COUNT(*)
  --   = 2 / 4 * 100 = 50.00 (denominator: March active users)
*/