KPI Analysis — Learn Moving Averages, Funnels, Cohort Retention, and Pareto Analysis in Practice

QUESTION 1

DAU Moving Average — Calculate an N-Day Moving Average with a ROWS BETWEEN Window Frame

AVG OVERROWS BETWEENMoving AverageWindow Frame

Background ▼

Daily DAU often contains substantial noise from day-of-week effects and one-off campaigns, making trends hard to read from a raw line chart. A moving average smooths that noise. LAG retrieves only “one point,” but a window frame lets an aggregate operate on the set of the “most recent N rows, including the current row.”

AVG(dau) OVER (
  ORDER BY metric_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- “the current row” plus “up to two rows earlier” = three rows averaged → 3-day moving average
-- Near the beginning, average only the rows that actually exist, even when fewer than three are in the frame

ROWS and RANGE are different: ROWS defines the frame by the physical number of rows. RANGE treats rows with the same ORDER BY value (peers) as a group, so duplicate dates can unexpectedly bring more rows into the frame. For a moving average, using the row-count-based ROWS frame is the standard approach.

Problem ▼

From the daily_active table, calculate DAU for each date and a three-day moving average (dau_ma3). Return metric_date, dau, dau_ma3 in ascending metric_date order. Round the moving average to two decimal places.

Tables used

► daily_active (7 rows)

metric_date	dau
2024-03-01	100
2024-03-02	120
2024-03-03	90
2024-03-04	150
2024-03-05	160
2024-03-06	130
2024-03-07	200

Note: 03-03 is a dip and 03-07 is a sharp rise. The moving average smooths these swings.

Expected Output ▼

Expected output (metric_date ascending):

metric_date	dau	dau_ma3
2024-03-01	100	100.00
2024-03-02	120	110.00
2024-03-03	90	103.33
2024-03-04	150	120.00
2024-03-05	160	133.33
2024-03-06	130	146.67
2024-03-07	200	163.33

The first two rows use partial frames with only one or two rows. On 03-03, (100+120+90)/3 = 103.33.

Model Answer ▼

SELECT
  metric_date,
  dau,
  ROUND(
    AVG(dau) OVER (
      ORDER BY metric_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- current row + previous 2 rows = 3-row frame
    ), 2
  ) AS dau_ma3
FROM  daily_active
ORDER BY metric_date;

/*
  Execution order:
  1. FROM daily_active                         → read the rows
  2. OVER (ORDER BY metric_date)               → order the rows by date
  3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  → fix the current + previous 2 row frame
  4. AVG(dau) / ROUND(...,2) / ORDER BY        → round the frame average and output it
  */

Explanation (table transitions & key points) ▼

SELECT
  metric_date,
  dau,
  ROUND(
    AVG(dau) OVER (
      ORDER BY metric_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) AS dau_ma3
FROM daily_active
ORDER BY metric_date;

LEGEND ▼

Rows read / loaded

① FROM daily_active (7 rows) ▼

FROM daily_activeRead all daily DAU rows. The dip on 03-03 and sharp rise on 03-07 are noise that the moving average will smooth.

1 / 6

metric_date	dau
03-01	100
03-02	120
03-03	90
03-04	150
03-05	160
03-06	130
03-07	200

7 rows read

LEARNING POINTS

A window frame makes a set of rows the aggregation target: LAG/LEAD retrieve only one point, while ROWS BETWEEN n PRECEDING AND CURRENT ROW targets the set of the most recent n+1 rows, including the current row. Swap AVG for SUM to get a moving total or for MAX to find a recent peak.

At the edge (the beginning), the frame shrinks automatically: 03-01 has no two preceding rows, so its frame has one row; 03-02 has two. The result is not NULL: the average uses only existing rows. If you need a strict three-day average, exclude edge rows with a condition such as WHERE row_number >= 3.

A seven-day moving average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Change only the frame boundary to extend the window to any size. For a centered moving average, include future rows with ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING.

ANTI-PATTERNS

Omit the frame clause and accidentally use the default RANGE: Without a frame on an ORDER BY window, the default is RANGE UNBOUNDED PRECEDING, which produces a cumulative average from the beginning rather than a moving average. Always specify ROWS BETWEEN ... for a moving average.

Build the moving average with a self-join or correlated subquery: A query such as (SELECT AVG(dau) FROM t t2 WHERE t2.date BETWEEN ...) can have quadratic cost and break down on large data. A window function computes it in one pass.

Practical column: Dividing roles between moving averages and WoW

A moving average reveals the trend, while WoW (Week-over-Week) highlights change points. A common dashboard pairs a seven-day moving-average line with WoW bars: the smoothed line shows direction, and the bars help detect anomalies. B2C services with strong day-of-week effects often prefer a seven-day window, while B2B services may prefer a five-day window containing weekdays only.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Funnel Drop-off — Calculate Step-to-Step Conversion with GROUP BY × LAG

GROUP BYLAG OVERFunnelStep OrderNULLIF

Background ▼

In the fundamentals version, COUNT(DISTINCT CASE WHEN...) placed every step horizontally in one row. Here, aggregate the data in long format (one row per step) and use LAG() to retrieve the users who passed the previous step, then calculate step-to-step conversion. This pattern scales without rewriting the query when the number of steps changes.

CASE step
  WHEN 'visit' THEN 1 WHEN 'signup' THEN 2 ...
END AS step_order
-- String step values do not sort into the business order alphabetically → create an explicit order column

LAG(users) OVER (ORDER BY step_order)
-- retrieve “the number of users who passed the previous step” in step_order → use it as the denominator

Define the step order yourself: Sorting step as a string produces alphabetical order such as activate, purchase, signup, visit, which breaks the funnel. Assign a numeric step_order with CASE before sorting.

Problem ▼

From the raw events in funnel_events, output the unique users who passed each step and the pass rate from the previous step (step_cvr) in long format. Return step, users, prev_users, step_cvr in funnel order (ascending step_order). Round step_cvr to two decimal places.

Tables used

► funnel_events (13 rows)

user_id	step
U1	visit
U1	signup
U1	activate
U1	purchase
U2	visit
U2	signup
U2	activate
U3	visit
U3	signup
U4	visit
U4	signup
U5	visit
U6	visit

Note: visit = 6 users, signup = 4, activate = 2, and purchase = 1.

Expected Output ▼

Expected output (funnel order):

step	users	prev_users	step_cvr
visit	6	NULL	NULL
signup	4	6	66.67
activate	2	4	50.00
purchase	1	2	50.00

Signup drop-off = 100 − 66.67 = 33.33%. The largest leak is signup → activate, with a 50% pass rate.

Model Answer ▼

WITH step_counts AS (
  SELECT
    step,
    CASE step                       -- define funnel order numerically
      WHEN 'visit'    THEN 1
      WHEN 'signup'   THEN 2
      WHEN 'activate' THEN 3
      WHEN 'purchase' THEN 4
    END AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM  funnel_events
  GROUP BY step
)
SELECT
  step, users,
  LAG(users) OVER (ORDER BY step_order) AS prev_users,
  ROUND(
    users * 100.0
    / NULLIF(LAG(users) OVER (ORDER BY step_order), 0), 2
  ) AS step_cvr
FROM  step_counts
ORDER BY step_order;

/*
  Execution order:
  1. FROM funnel_events                       → read the rows
  2. GROUP BY step + COUNT(DISTINCT user_id)  → aggregate unique users by step
  3. WITH step_counts                         → complete the CTE
  4. LAG(users) OVER (ORDER BY step_order)    → retrieve the previous-step users
  5. Calculate step-to-step conversion       → divide by the previous step
  */

Explanation (table transitions & key points) ▼

WITH step_counts AS (
  SELECT
    step,
    CASE step
      WHEN 'visit' THEN 1 WHEN 'signup' THEN 2 WHEN 'activate' THEN 3 WHEN 'purchase' THEN 4 
    END AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM funnel_events
  GROUP BY step
)
SELECT
  step, users,
  LAG(users) OVER (ORDER BY step_order) AS prev_users,
  ROUND(
    users * 100.0
    / NULLIF(LAG(users) OVER (ORDER BY step_order), 0), 2
  ) AS step_cvr
FROM step_counts
ORDER BY step_order;

LEGEND ▼

Rows read / loaded

① FROM funnel_events (13 rows) ▼

FROM funnel_eventsRead all raw events. Each user has rows for multiple steps. COUNT(DISTINCT user_id) is required to avoid counting the same user more than once within a step.

1 / 6

user_id	step
U1	visit
U1	signup
U1	activate
U1	purchase
U2	visit
U2	signup
U2	activate
U3	visit
U3	signup
U4	visit
U4	signup
U5	visit
U6	visit

13 rows read

LEARNING POINTS

Long format + LAG scales with the number of steps: In the fundamentals version, the wide format requires adding a new column for every step. With long format + LAG, adding a step adds data but does not change the query. Long format also feeds directly into line and funnel charts in BI tools.

Window functions can operate on aggregated results: LAG is applied to the step rows after GROUP BY has reduced the data to four rows. The two-stage pattern “aggregate → window” is commonly implemented by building the aggregate result in a CTE and applying the window function afterward.

Drop-off rate = 100 - step_cvr: A 66.67% pass rate means 33.33% drop-off. Listing the loss at each step immediately shows the largest improvement opportunity, which here is the 50% signup → activate pass rate.

ANTI-PATTERNS

Order directly by step as a string: ORDER BY step produces the alphabetical order activate→purchase→signup→visit, so LAG retrieves unrelated steps and the conversion rates become meaningless. Define numeric step_order first.

Use the first step as the denominator for every rate: Dividing every step by visit gives an overall reach rate, not the loss between adjacent steps. To identify a bottleneck, step-to-step CVR must use the previous step as its denominator.

Practical column: Funnel time windows and sequence constraints

This question considers only whether a user passed each step. In practice, you may require a signup within seven days of a visit or accept an activate event only when it occurs after signup. Add timestamps to the events, calculate each user's first step timestamp in a CTE, and implement the constraint with a join such as JOIN ... ON next_step_time BETWEEN previous_step_time AND previous_step_time+7.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Date Spine — Generate Missing Dates with a Recursive CTE and Zero-Fill Activity

WITH RECURSIVEUNION ALLDate SpineZero-FillingCOALESCE

Background ▼

On a day with no activity, no row exists in the aggregate table. A chart built from this “gapped” data makes the dates appear compressed and shifts moving averages. Use a recursive CTE (WITH RECURSIVE) to generate a continuous date skeleton (a date spine), LEFT JOIN the real data to it, and use COALESCE(..., 0) to fill missing activity with zero.

WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-02-01' AS d   -- ① anchor: the first row
  UNION ALL
  SELECT d + 1 FROM date_spine        -- ② recursive term: previous row + 1
  WHERE d < DATE '2024-02-05'     -- ③ stop condition: stop at the end date
)

Three elements of a recursive CTE: an anchor (the initial row), a recursive term (which references the previous result to generate the next row), and a stop condition (WHERE). A wrong stop condition can create an infinite loop, so always use a condition that eventually becomes false.

Problem ▼

From active_days, a daily active-user table with missing dates, generate every date from 2024-02-01 through 2024-02-05 with a recursive CTE and output 0 for days without activity. Return activity_date, active_users in ascending date order.

Tables used

► active_days (3 rows, with gaps)

activity_date	active_users
2024-02-01	50
2024-02-02	65
2024-02-04	40

Note: 02-03 and 02-05 have no row because activity was zero.

Expected Output ▼

Expected output (ascending date, 5 rows):

activity_date	active_users
2024-02-01	50
2024-02-02	65
2024-02-03	0
2024-02-04	40
2024-02-05	0

The 3 source rows become 5 rows; 02-03 and 02-05 are filled with 0.

Model Answer ▼

WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-02-01' AS d        -- ① anchor (1 row)
  UNION ALL
  SELECT d + 1                          -- ② recursive term: previous d + 1
  FROM  date_spine
  WHERE d < DATE '2024-02-05'          -- ③ stop condition
)
SELECT
  s.d AS activity_date,
  COALESCE(a.active_users, 0) AS active_users  -- fill unmatched dates with 0
FROM      date_spine s
LEFT JOIN active_days a ON a.activity_date = s.d
ORDER BY s.d;

/*
  Execution order (recursive CTE expansion):
  1. Anchor                              → generate the starting date
  2. Recursion                           → add the next date one day at a time
  3. Recursion ends                      → stop at the upper bound
  4. date_spine LEFT JOIN active_days    → attach activity (missing values are NULL)
  5. COALESCE(NULL,0) / ORDER BY s.d     → fill with 0 and output in date order
  */

Explanation (table transitions & key points) ▼

WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-02-01' AS d
  UNION ALL
  SELECT d + 1
  FROM date_spine
  WHERE d < DATE '2024-02-05'
)
SELECT
  s.d AS activity_date,
  COALESCE(a.active_users, 0) AS active_users
FROM date_spine s
LEFT JOIN active_days a ON a.activity_date = s.d
ORDER BY s.d;

LEGEND ▼

Rows read / loaded

① Source data: active_days (3 rows, with gaps) ▼

Inspect table: active_daysFirst inspect the source data. Only 02-01, 02-02, and 02-04 exist; 02-03 and 02-05 are missing. A recursive CTE will fill the gaps.

1 / 8

activity_date	active_users
2024-02-01	50
2024-02-02	65
2024-02-04	40

3 rows (source data)

LEARNING POINTS

A recursive CTE repeatedly uses the previous result as input: The anchor supplies the first input, and the recursive term uses the row added in the previous iteration to generate the next row. Recursion stops when the WHERE condition becomes false and generates zero new rows. UNION ALL is required; using UNION adds unnecessary duplicate comparisons.

Put the spine on the left and LEFT JOIN the facts: The order FROM date_spine s LEFT JOIN active_days a is the key. Making all dates the left side ensures that an activity-zero day remains as a row. Reversing the order or using INNER JOIN brings the gaps back.

In PostgreSQL, generate_series is the standard shortcut: In production, generate_series(DATE '2024-02-01', DATE '2024-02-05', INTERVAL '1 day') is more concise. A recursive CTE remains a general tool for expanding parent-child hierarchies, generating sequences, and graph searches, so understanding its structure is valuable.

ANTI-PATTERNS

Forget the stop condition and create an infinite loop: If the recursive term has no WHERE condition, or the condition stays true forever, rows are generated without end. Always set a condition that becomes false at the end date, and add a recursion limit when possible.

Calculate moving averages or day-over-day change on gapped data: Without a 02-03 row, the “previous day” for 02-04 becomes 02-02 and the comparison shifts; the moving-average frame shifts too. Always make time-series KPI data continuous with a date spine before applying a window function.

Practical column: A date spine is the foundation of a KPI data platform

Many data warehouses prepare a dim_date date dimension containing every date plus weekday, holiday, and quarter flags, then LEFT JOIN facts to it. This makes activity-zero days, weekday aggregates, and business-day-only aggregates possible through joins alone. A recursive CTE or generate_series is the first step toward building such a dim_date table.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Cohort Retention Matrix — Pivot Elapsed-Week Buckets with FILTER

Multi-CTEFILTERCohort MatrixDate-Difference Buckets

Background ▼

The fundamentals version calculated D1/D7 for a single cohort. The advanced version builds a retention matrix (a cohort triangle) across multiple cohorts and elapsed periods. The two keys are to calculate “elapsed week” as (login date − cohort date) / 7, then pivot each week into its own column with the aggregate filter FILTER (WHERE ...).

(l.login_date - f.cohort_date) / 7 AS week_no
-- PostgreSQL: DATE − DATE = elapsed days (integer); integer division by /7 gives the week number

COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1)
-- FILTER: a WHERE condition that applies only to this aggregate; place each week's count in a column

FILTER is a WHERE clause for an aggregate: COUNT(*) FILTER (WHERE condition) is equivalent to COUNT(CASE WHEN condition THEN 1 END), but makes the intent clearer and is a standard PostgreSQL feature.

Problem ▼

From logins, separate users by their first login date and output a retention-rate matrix for elapsed weeks 0, 1, and 2. Return cohort_date, cohort_size, w0_pct, w1_pct, w2_pct in ascending cohort_date order. Round the rates to two decimal places.

Tables used

► logins (9 rows)

user_id	login_date
U1	2024-01-01
U1	2024-01-08
U1	2024-01-15
U2	2024-01-01
U2	2024-01-08
U3	2024-01-01
U4	2024-01-08
U4	2024-01-15
U5	2024-01-08

Note: the 01-01 cohort is U1, U2, and U3; the 01-08 cohort is U4 and U5. Every date difference is a multiple of seven days.

Expected Output ▼

Expected output (2 cohorts × 3 weeks):

cohort_date	cohort_size	w0_pct	w1_pct	w2_pct
2024-01-01	3	100.00	66.67	33.33
2024-01-08	2	100.00	50.00	0.00

w0 is 100% by definition. For 01-01, w1 = 2/3 and w2 = 1/3; for 01-08, w1 = 1/2 and w2 = 0/2.

Model Answer ▼

WITH first_login AS (              -- ① cohort date = first login date
  SELECT user_id, MIN(login_date) AS cohort_date
  FROM  logins
  GROUP BY user_id
),
activity AS (                          -- ② calculate elapsed week
  SELECT
    f.cohort_date, f.user_id,
    (l.login_date - f.cohort_date) / 7 AS week_no
  FROM      first_login f
  JOIN      logins l USING (user_id)
)
SELECT
  cohort_date,
  COUNT(DISTINCT user_id) AS cohort_size,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 0)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w0_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w1_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 2)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w2_pct
FROM  activity
GROUP BY cohort_date
ORDER BY cohort_date;

/*
  Execution order:
  1. first_login                     → aggregate each user's cohort date
  2. activity                        → calculate the week number
  3. GROUP BY cohort_date            → aggregate by cohort
  4. COUNT(DISTINCT user_id) FILTER  → calculate retention by week
  */

Explanation (table transitions & key points) ▼

WITH first_login AS (
  SELECT user_id, MIN(login_date) AS cohort_date
  FROM logins
  GROUP BY user_id
),
activity AS (
  SELECT
    f.cohort_date, f.user_id,
    (l.login_date - f.cohort_date) / 7 AS week_no
  FROM first_login f
  JOIN logins l USING (user_id)
)
SELECT
  cohort_date,
  COUNT(DISTINCT user_id) AS cohort_size,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 0)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w0_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1) ... ) AS w1_pct,
  ROUND( ... FILTER (WHERE week_no = 2) ... ) AS w2_pct
FROM activity
GROUP BY cohort_date
ORDER BY cohort_date;

LEGEND ▼

Rows read / loaded

① FROM logins (9 rows) ▼

FROM loginsRead the complete login history. U1 logs in three times and U4 twice. First determine each user's first login date, the cohort date.

1 / 7

user_id	login_date
U1	01-01
U1	01-08
U1	01-15
U2	01-01
U2	01-08
U3	01-01
U4	01-08
U4	01-15
U5	01-08

9 rows read

LEARNING POINTS

Build definitions step by step with multiple CTEs: first_login defines the cohort, activity defines elapsed week, and the outer query handles aggregation. Separating responsibilities keeps the query readable and lets you change the cohort basis to something such as first purchase by replacing one CTE.

Date-difference buckets create elapsed periods at any granularity: (login_date - cohort_date)/7 gives weeks, /30 gives approximate months, and /1 gives days. The period number is the vertical axis of cohort analysis; align users by elapsed time from the cohort rather than by absolute dates.

FILTER is a clearer pivot than CASE WHEN: COUNT(*) FILTER (WHERE week_no=1) is equivalent to COUNT(CASE WHEN week_no=1 THEN 1 END), but states the intent directly. It is the first choice when creating a wide matrix with one column per week.

ANTI-PATTERNS

Use the previous week's users instead of cohort size as the denominator: Cohort retention always uses the initial cohort size (the w0 count) as its denominator. Using the previous week produces a different metric—the retention of the retention—and can lead to incorrect business decisions.

Use an inner JOIN and lose weeks with zero return visits: An inner JOIN is sufficient here because every cohort has a w0 event. If you need to show a week with no returning users explicitly as 0, CROSS JOIN a week spine (Q3) and then LEFT JOIN the activity.

Practical column: How to read a cohort triangle

A triangular matrix with cohorts as rows and elapsed weeks as columns is called a cohort triangle. Reading vertically shows whether newer cohorts retain better, indicating product-improvement effects; reading horizontally shows when attrition levels off and where the retention floor lies. A higher plateau generally suggests a product is closer to product-market fit (PMF).

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Pareto Analysis — Measure Revenue Concentration with a Cumulative SUM OVER and RANK

SUM OVER ()Running FramePareto AnalysisABC AnalysisRANK

Background ▼

“Eighty percent of revenue comes from the top twenty percent of customers.” Quantify the Pareto principle with SQL. Calculate each customer's revenue share, the cumulative revenue share accumulated in descending order, and an ABC rank based on the cumulative share. The key is to use two window aggregates.

SUM(revenue) OVER ()
-- OVER() with no frame and no ORDER BY = grand total across all rows (the denominator)

SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING)
-- running total from the first row through the current row; descending order accumulates from the top

A window alias cannot be reused in the same SELECT: To use cum_share for ABC classification, first materialize the column in WITH ranked AS (...), then reference it in the outer query with CASE WHEN cum_share <= 80 ....

Problem ▼

From customer_revenue, output rank, revenue share, cumulative revenue share, and an ABC class in descending revenue order. Return customer_id, revenue, rev_rank, rev_share, cum_share, abc_class. Classify cumulative share ≤80% as A, ≤95% as B, and anything above that as C. Round the percentages to two decimal places.

Tables used

► customer_revenue (5 rows)

customer_id	revenue
C1	5000
C2	3000
C3	1200
C4	500
C5	300

Note: total revenue = 10000. The top two customers (C1 and C2) contribute 8000 = 80%.

Expected Output ▼

Expected output (revenue descending):

customer_id	revenue	rev_rank	rev_share	cum_share	abc_class
C1	5000	1	50.00	50.00	A
C2	3000	2	30.00	80.00	A
C3	1200	3	12.00	92.00	B
C4	500	4	5.00	97.00	C
C5	300	5	3.00	100.00	C

The A group (C1 and C2) accounts for 80% of revenue—the Pareto pattern. Prioritize initiatives for the A group.

Model Answer ▼

WITH ranked AS (
  SELECT
    customer_id, revenue,
    RANK() OVER (ORDER BY revenue DESC) AS rev_rank,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS rev_share,  -- revenue share
    ROUND(
      SUM(revenue) OVER (ORDER BY revenue DESC
                         ROWS UNBOUNDED PRECEDING)            -- cumulative from the top
      * 100.0 / SUM(revenue) OVER (), 2
    ) AS cum_share
  FROM  customer_revenue
)
SELECT
  customer_id, revenue, rev_rank, rev_share, cum_share,
  CASE                                  -- classify by cumulative share
    WHEN cum_share <= 80  THEN 'A'
    WHEN cum_share <= 95  THEN 'B'
    ELSE 'C'
  END AS abc_class
FROM  ranked
ORDER BY rev_rank;

/*
  Execution order:
  1. FROM customer_revenue                          → read the rows
  2. SUM(revenue) OVER ()                           → attach the grand total to every row
  3. SUM(revenue) OVER (ORDER BY revenue DESC ...)  → calculate the running total from the top
  4. rev_share / cum_share                          → calculate the shares
  5. Outer CASE                                     → classify rows as A, B, or C
  */

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT
    customer_id, revenue,
    RANK() OVER (ORDER BY revenue DESC) AS rev_rank,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS rev_share,
    ROUND(
      SUM(revenue) OVER (ORDER BY revenue DESC
                         ROWS UNBOUNDED PRECEDING)
      * 100.0 / SUM(revenue) OVER (), 2
    ) AS cum_share
  FROM customer_revenue
)
SELECT
  customer_id, revenue, rev_rank, rev_share, cum_share,
  CASE
    WHEN cum_share <= 80 THEN 'A'
    WHEN cum_share <= 95 THEN 'B'
    ELSE 'C'
  END AS abc_class
FROM ranked
ORDER BY rev_rank;

LEGEND ▼

Rows read / loaded

① FROM customer_revenue (5 rows) ▼

FROM customer_revenueRead revenue by customer. C1 stands out. Sorting by revenue descending and accumulating from the top reveals where the total reaches 80%.

1 / 7

customer_id	revenue
C1	5000
C2	3000
C3	1200
C4	500
C5	300

5 rows read

LEARNING POINTS

OVER () distributes a whole-table aggregate to each row: An empty OVER() with no ORDER BY or frame covers the entire partition. SUM(revenue) OVER () places the grand total beside every row, creating the denominator for revenue share without a subquery.

Cumulative totals use ROWS UNBOUNDED PRECEDING: Q1 used the most recent N rows for a moving average; a cumulative total uses the rows from the beginning through the current row. ROWS UNBOUNDED PRECEDING is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With descending ORDER BY, the total accumulates from the top, which is the core of Pareto analysis.

A window alias cannot be reused immediately → materialize it in a CTE: The same SELECT cannot reference cum_share in CASE because expressions are evaluated at the same query level. Define it in WITH ranked, then reference it in the outer query. Also understand when to use RANK, DENSE_RANK, and ROW_NUMBER for ties.

ANTI-PATTERNS

Accumulate in ascending order: Pareto analysis assumes accumulation from largest to smallest. ORDER BY revenue starts with the smallest customers, so the “top 20% produce 80%” structure cannot be read. Use DESC.

Omit the cumulative frame and rely on the default RANGE: When several customers have the same revenue, the default RANGE treats peer rows together and the cumulative value can jump. Specify ROWS to add one row at a time. To make equal-revenue ranks unique, add a tie-breaker such as customer_id to ORDER BY.

Practical column: ABC analysis for inventory and customer strategy

ABC analysis applies not only to revenue, but also to inventory management (selecting SKUs for close control) and customer success (selecting customers for high-touch support). A-group customers contribute large revenue and have a large churn impact, so concentrate human resources there; C-group customers are often handled with automation and low-touch operations. Change the query's ORDER BY and classification target to combinations such as product × gross margin or region × order count to reuse the pattern for many concentration analyses.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.