Statistical Analysis — Learn Ranking, period comparisons, cumulative analysis, and correlation coefficients in Practice

QUESTION 1

ROW_NUMBER / RANK / DENSE_RANK — Compare ranking behavior when scores are tied

ROW_NUMBERRANKDENSE_RANKRanking AnalysisTie Handling

Background ▼

There are 3 window functions for ranking, and they handle ties differently. Because the right choice depends on how well it fits the business requirement, it is important to understand the differences precisely.

ROW_NUMBER() OVER (ORDER BY col)  -- Always assigns a sequence, even for ties. The ORDER BY sequence matters
RANK()       OVER (ORDER BY col)  -- Ties receive the same rank. The next rank skips by the number of tied rows
DENSE_RANK() OVER (ORDER BY col)  -- Ties receive the same rank. The next rank is always +1 (no gaps)

Which one should you use: Choose DENSE_RANK when you need consecutive ranks (for example, display 1st, 2nd, and 3rd), ROW_NUMBER when you need each row's position in the full result (for example, pagination or deduplication), and RANK when gaps in the ranking carry business meaning (for example, Olympic standings).

Problem ▼

From the score_board table, assign each player 3 rankings—ROW_NUMBER, RANK, and DENSE_RANK—in descending score order. Return the columns player_id, score, row_num, rank, dense_rank, ordered by score descending and player_id ascending.

Source table

▸ score_board (6 rows)

player_id	score
P1	980
P2	750
P3	980
P4	620
P5	750
P6	880

Expected Output ▼

Expected output (score descending, player_id ascending):

player_id	score	row_num	rank	dense_rank
P1	980	1	1	1
P3	980	2	1	1
P6	880	3	3	2
P2	750	4	4	3
P5	750	5	4	3
P4	620	6	6	4

P1 and P3 are tied at 980: RANK → both 1st (skips to 3rd next) / DENSE_RANK → both 1st (2nd next) / ROW_NUMBER → sequential 1, 2 (determined by ascending player_id)

Model Answer ▼

SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id)  AS row_num,     -- Sequential even for ties (deterministic by player_id)
  RANK()       OVER (ORDER BY score DESC)              AS rank,       -- Ties share a rank; the next rank is skipped
  DENSE_RANK() OVER (ORDER BY score DESC)              AS dense_rank  -- Ties share a rank; the next rank is consecutive
FROM   score_board
ORDER BY score DESC, player_id;

/*
  Logical SQL evaluation order:
  1. FROM score_board                → read 6 rows
  2. Evaluate window functions       → assign ROW_NUMBER/RANK/DENSE_RANK
  3. SELECT                          → select 5 columns
  4. ORDER BY score DESC, player_id  → final sort
  */

Explanation (table transitions & key points) ▼

SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id)  AS row_num,
  RANK()       OVER (ORDER BY score DESC)              AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC)              AS dense_rank
FROM   score_board
ORDER BY score DESC, player_id;

LEGEND ▼

Rows read / loaded

① FROM score_board — read 6 input rows ▼

FROM score_boardRead all 6 rows from the score_board table. At this point the scores are in arbitrary order. P1 and P3 both have score=980, while P2 and P5 both have score=750, so 2 ties exist.

1 / 5

player_id	▸ score
P1	980
P2	750
P3	980
P4	620
P5	750
P6	880

score_board: 6 rows (2 tie groups: 980×2 and 750×2)

LEARNING POINTS

What the gap in RANK means: When 2 people have score=980, RANK returns 1, 1, 3 (there is no 2nd place). This explicitly indicates that even the 3rd-strongest player has 2 people ahead. This is common for sports standings and exam rankings. DENSE_RANK returns 1, 1, 2 and shows which consecutive rank band a player belongs to after duplicates are removed.

ROW_NUMBER is nondeterministic unless ORDER BY is unique: With only ROW_NUMBER() OVER (ORDER BY score DESC), which of the tied players (P1/P3) becomes 1st may change between executions. Add a unique key such as player_id to ORDER BY to guarantee the same result every time. This is especially important for pagination.

Combine with PARTITION BY for within-group rankings: Add PARTITION BY as in RANK() OVER (PARTITION BY department ORDER BY salary DESC) to create rankings within each department. To extract the top person in each department, use WHERE rank = 1 through a subquery or CTE. This is one of the most frequently used ranking patterns in practice.

ANTI-PATTERNS

Omit ORDER BY when trying to select the “top N rows” with ROW_NUMBER: If you use ROW_NUMBER without ORDER BY, as in WHERE ROW_NUMBER() OVER () = 1, which row becomes 1st is nondeterministic. Always specify ORDER BY to make the ranking criterion explicit.

Use RANK to extract the “top N rows” and get a variable count: Even if you try to get the top 3 ranks with WHERE rank <= 3, 4 rows are returned when 2 people tie for 3rd. Use ROW_NUMBER when you need exactly N rows; use RANK or DENSE_RANK when you want everyone tied through 3rd place. The choice must match the requirement.

Practical Column: Combining ranking functions with subqueries

When you filter on a ranking-function result in a WHERE clause, you cannot write the window function directly in WHERE because window functions are calculated at the SELECT stage. Filter through a CTE or subquery instead. Remember the pattern WITH ranked AS (SELECT *, RANK() OVER (...) AS r FROM t) SELECT * FROM ranked WHERE r = 1 to handle many practical cases, such as retrieving the maximum-valued row in each group or selecting the latest record during deduplication.

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

LAG / LEAD — Calculate month-over-month growth in a single query

LAGLEADPeriod ComparisonsMoM GrowthNULLIF

Background ▼

LAG / LEAD are window functions that reference the value N rows before or after the current row. They calculate differences from the previous or next period without a self-join through GROUP BY.

LAG(col, 1)  OVER (ORDER BY date_col)  -- Value 1 row earlier (default offset=1)
LAG(col, 3)  OVER (ORDER BY date_col)  -- 3 rows earlier (for quarter-over-quarter comparisons, for example)
LEAD(col, 1) OVER (ORDER BY date_col)  -- Value 1 row later

-- Prevent division by zero: NULLIF(val, 0) → returns NULL when val is 0
NULLIF(prev_revenue, 0)   -- Returns NULL instead of dividing by 0 (essential in production)

NULL on the first and last rows: LAG returns NULL for the first row, where no previous row exists. LEAD returns NULL for the last row, where no next row exists. Whether NULL should be treated as 0% growth or excluded depends on the requirement. You can also specify a default value as the 3rd argument: LAG(col, 1, 0).

Problem ▼

From the monthly_revenue table, calculate monthly revenue, previous-month revenue, and month-over-month growth rate (%). Return the columns month, revenue, prev_revenue, growth_rate_pct (round the growth rate to 2 decimal places), ordered by month ascending. For the first month, both prev_revenue and growth_rate_pct are NULL.

Source table

▸ monthly_revenue (6 rows)

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	1100000
2024-04	1350000
2024-05	1500000
2024-06	1420000

Expected Output ▼

Expected output (month ascending):

month	revenue	prev_revenue	growth_rate_pct
2024-01	1000000	NULL	NULL
2024-02	1200000	1000000	20.00
2024-03	1100000	1200000	-8.33
2024-04	1350000	1100000	22.73
2024-05	1500000	1350000	11.11
2024-06	1420000	1500000	-5.33

Growth rate = (current month − previous month) / previous month × 100. 2024-03 is -8.33% versus the previous month's 1,200,000 (down 100,000) / 2024-06 is -5.33% month over month (down 80,000)

Model Answer ▼

WITH lag_data AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)  AS prev_revenue  -- Previous month's revenue (NULL on the first row)
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    100.0 * (revenue - prev_revenue)
           / NULLIF(prev_revenue, 0),  -- Return NULL when prev_revenue=0 to avoid division by zero
    2
  ) AS growth_rate_pct
FROM   lag_data
ORDER BY month;

/*
  Logical SQL evaluation order:
  1. CTE lag_data    → add the previous month's revenue
  2. Outer query     → calculate month-over-month growth and ROUND it
  3. ORDER BY month   → ascending month order
  */

Explanation (table transitions & key points) ▼

WITH lag_data AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)  AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    100.0 * (revenue - prev_revenue)
           / NULLIF(prev_revenue, 0),
    2
  ) AS growth_rate_pct
FROM   lag_data
ORDER BY month;

LEGEND ▼

Rows read / loaded

① CTE: FROM monthly_revenue — read monthly revenue data ▼

FROM monthly_revenue (in CTE lag_data)Read all 6 rows from the monthly_revenue table. There is no previous-month data at this stage. In the next step, LAG references the revenue from the preceding row for each row.

1 / 5

month	▸ revenue
2024-01	1000000
2024-02	1200000
2024-03	1100000
2024-04	1350000
2024-05	1500000
2024-06	1420000

monthly_revenue: 6 rows

LEARNING POINTS

Why calculate LAG only once in a CTE: Without a CTE, writing ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) evaluates LAG twice and significantly reduces readability. When reusing a window-function result, calculate it once in a CTE and then reference it. This is the standard production pattern.

Control NULL with the 3rd argument of LAG(col, N, default): Writing LAG(revenue, 1, 0) treats the first row, where no previous month exists, as 0 instead of NULL. However, this makes the growth-rate denominator 0 and causes division by zero, so NULLIF protection is still always required. Clarify whether the requirement calls for leaving NULL, filling 0, or carrying forward the previous value.

LEAD enables the same structure for next-month actual-versus-plan comparisons: LEAD(revenue) OVER (ORDER BY month) references the value in the next row (the following month). After joining a budget table with actuals, it is often used in dashboard metrics that calculate the gap between current-month actuals and next-month budget. LAG and LEAD are symmetric concepts, so understanding one lets you use both.

ANTI-PATTERNS

Omit NULLIF and miss a division-by-zero error: Real data occasionally contains a month with 0 previous-month revenue, such as a service outage. Writing (revenue - prev_revenue) / prev_revenue makes PostgreSQL return a division by zero error. Always wrap the denominator in NULLIF(denominator, 0).

Confuse ORDER BY inside LAG's OVER with ORDER BY in the outer query: The ORDER BY in LAG(revenue) OVER (ORDER BY month) determines processing order within the window, while FROM ... ORDER BY month determines output row order. The calculation is correct if the outer ORDER BY is omitted as long as OVER contains ORDER BY, but output order is not guaranteed. Always specify both explicitly.

Practical Column: Extending to YoY (year-over-year) comparisons

When monthly data covers more than 1 year, year-over-year (YoY) comparisons are often more meaningful for the business than month-over-month comparisons. Set the offset to 12 with LAG(revenue, 12) OVER (ORDER BY month) to retrieve revenue from the same month in the previous year. This makes it possible to compare growth while removing seasonal effects such as year-end shopping, and to judge whether a trend is real or seasonal. Production dashboards commonly display both MoM and YoY.

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

Running Totals and Cumulative Ratios — Use SUM OVER for Pareto analysis of sales

SUM OVERUNBOUNDED PRECEDINGRunning TotalPareto AnalysisCumulative Ratio

Background ▼

A running total is the sum accumulated from the first row through the current row, implemented with a window-frame specification. Combining it with a cumulative ratio (%) lets you check the 80:20 rule (the Pareto principle) in a single query.

SUM(col) OVER (
  ORDER BY col DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Sum from the first row through the current row
)                                                  → Running total for each row

SUM(col) OVER ()                                    → Grand total of all rows (without PARTITION BY)

The trap of ROWS BETWEEN versus the default frame: If you omit the frame specification from an OVER clause with ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (value-based). When multiple rows have the same value, RANGE includes all tied rows in the frame, which changes the running-total result. Always specify ROWS BETWEEN for a running total.

Problem ▼

From the category_sales table, calculate a running total and cumulative ratio (%) in descending sales order. Calculate the running total and grand total in a CTE, then calculate the ratio in the outer query. Return category, sales_amount, cum_sales, cum_pct (round cum_pct to 2 decimal places), ordered by sales_amount descending.

Source table

▸ category_sales (5 rows)

category	sales_amount
Electronics	4800000
Apparel	3200000
Food	2100000
Books	1400000
Others	900000

Expected Output ▼

Expected output (sales_amount descending):

category	sales_amount	cum_sales	cum_pct
Electronics	4800000	4800000	38.71
Apparel	3200000	8000000	64.52
Food	2100000	10100000	81.45
Books	1400000	11500000	92.74
Others	900000	12400000	100.00

Grand total=12,400,000 / Electronics + Apparel = 64.52%, so the top 2 categories account for two-thirds of sales. Including Food, the top 3 categories account for 81.45% (a distribution close to the Pareto principle).

Model Answer ▼

WITH running AS (
  SELECT
    category,
    sales_amount,
    SUM(sales_amount) OVER (
      ORDER BY sales_amount DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- Running total from the first row through the current row
    )                             AS cum_sales,
    SUM(sales_amount) OVER ()     AS grand_total  -- No frame → total of all rows
  FROM category_sales
)
SELECT
  category,
  sales_amount,
  cum_sales,
  ROUND(100.0 * cum_sales / grand_total, 2)  AS cum_pct  -- Calculate the cumulative ratio (%)
FROM   running
ORDER BY sales_amount DESC;

/*
  Logical SQL evaluation order:
  1. CTE running
  2. Outer query
  3. ORDER BY sales_amount DESC  → sales descending
  */

Explanation (table transitions & key points) ▼

WITH running AS (
  SELECT
    category,
    sales_amount,
    SUM(sales_amount) OVER (
      ORDER BY sales_amount DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                             AS cum_sales,
    SUM(sales_amount) OVER ()     AS grand_total
  FROM category_sales
)
SELECT
  category,
  sales_amount,
  cum_sales,
  ROUND(100.0 * cum_sales / grand_total, 2)  AS cum_pct
FROM   running
ORDER BY sales_amount DESC;

LEGEND ▼

Rows read / loaded

① CTE: FROM category_sales — read category sales data ▼

FROM category_sales (in CTE running)Read all 5 rows from the category_sales table. The next step adds a running total to each row with SUM OVER. The grand total is 4,800,000+3,200,000+2,100,000+1,400,000+900,000 = 12,400,000.

1 / 5

category	▸ sales_amount
Electronics	4800000
Apparel	3200000
Food	2100000
Books	1400000
Others	900000

category_sales: 5 rows (grand total = 12,400,000)

LEARNING POINTS

The important difference between ROWS BETWEEN and RANGE BETWEEN: When the frame is omitted with ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (value-based). If multiple rows have the same sales_amount, RANGE includes them in the same frame and makes the cumulative total add them earlier. Always specify ROWS BETWEEN for a running total.

Using SUM OVER () as a “grand total”: Because SUM(col) OVER () specifies neither PARTITION BY, ORDER BY, nor a frame, the sum of all rows is copied to every row. This technique calculates each row's share of a group total in one query. Adding PARTITION BY extends it to shares within each department.

Applying Pareto analysis in SQL: Identify rows where the cumulative ratio exceeds 80% to find the categories, products, or customers that account for 80% of sales. Filtering with WHERE cum_pct <= 80 retrieves only the rows within the Pareto line. This pattern applies to inventory management (key items), customer segments (key customers), and bug management (high-impact bugs).

ANTI-PATTERNS

Omit ORDER BY and get a grand total instead of a running total: SUM(sales_amount) OVER () and SUM(sales_amount) OVER (ORDER BY sales_amount DESC) are completely different. Omitting ORDER BY copies the grand total (the same value as grand_total) to every row; it does not produce a running total. Always specify ORDER BY and ROWS BETWEEN for a running total.

Use 100 * cum_sales / grand_total (integer division) and get a ratio of 0: When both operands are integers, PostgreSQL performs integer division, producing 0 or a truncated value. Use a decimal literal such as 100.0 * cum_sales / grand_total, or cast with cum_sales::numeric / grand_total * 100.

Practical Column: Cumulative ratios and ABC analysis

In inventory management, ABC analysis classifies products by cumulative sales ratio: A (top 70%), B (70–90%), and C (90–100%). Add CASE WHEN cum_pct <= 70 THEN 'A' WHEN cum_pct <= 90 THEN 'B' ELSE 'C' END AS abc_rank to complete the ABC classification. This single SQL pattern supports inventory-optimization decisions such as tightly managing stockout risk for A items and reducing ordering frequency for C items.

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

NTILE — Classify user purchasing segments by quartile

NTILECASE WHENSegment AnalysisQuartilesCTE

Background ▼

NTILE(n) assigns each row a bucket number (1–n) after ordering the rows with ORDER BY. It divides the rows into n groups by frequency, which is fundamentally different from CASE WHEN with fixed thresholds.

NTILE(4) OVER (ORDER BY col DESC)
-- Order all rows from high to low and divide them into 4 groups. If the row count is not a multiple of 4, distribute the remainder to the higher buckets
-- Example: divide 9 rows by 4 → bucket 1 has 3 rows, buckets 2–4 have 2 rows each ← distribute the remainder one row at a time from the front

-- Difference from WIDTH_BUCKET (see Basic Q4)
WIDTH_BUCKET: equal-width buckets (intervals have the same width; row counts may be uneven)
NTILE:        equal-frequency buckets (row counts are even; interval widths may be uneven)

Label NTILE results with CASE WHEN: In practice, it is standard to convert the bucket number into business terminology (1=VIP, 2=Gold, and so on) rather than display the number directly. Calculate NTILE in a CTE and then apply CASE WHEN to avoid duplicating the same OVER clause.

Problem ▼

Classify purchase amounts in the user_spending table into quartiles with NTILE(4) in descending order, and assign the segment labels VIP / Gold / Silver / Bronze. Return user_id, total_spend, quartile, segment, ordered by total_spend descending.

Source table

▸ user_spending (8 rows)

user_id	total_spend
U01	15000
U02	8500
U03	52000
U04	3200
U05	28000
U06	6400
U07	41000
U08	1800

Expected Output ▼

Expected output (total_spend descending):

user_id	total_spend	quartile	segment
U03	52000	1	VIP
U07	41000	1	VIP
U05	28000	2	Gold
U01	15000	2	Gold
U02	8500	3	Silver
U06	6400	3	Silver
U04	3200	4	Bronze
U08	1800	4	Bronze

8 rows divided into 4 equal groups → 2 rows per bucket. VIP: U03(52000)/U07(41000) / Gold: U05(28000)/U01(15000) / Silver: U02(8500)/U06(6400) / Bronze: U04(3200)/U08(1800)

Model Answer ▼

WITH ranked AS (
  SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)  AS quartile  -- Divide into 4 groups from the top
  FROM user_spending
)
SELECT
  user_id,
  total_spend,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM   ranked
ORDER BY total_spend DESC;

/*
  Logical SQL evaluation order:
  1. CTE ranked
  2. Outer query
  3. ORDER BY total_spend DESC  → amount descending
  */

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)  AS quartile
  FROM user_spending
)
SELECT
  user_id,
  total_spend,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM   ranked
ORDER BY total_spend DESC;

LEGEND ▼

Rows read / loaded

① CTE: FROM user_spending — read user purchase data ▼

FROM user_spending (in CTE ranked)Read all 8 rows from the user_spending table. total_spend is widely distributed, from 1,800 to 52,000.

1 / 5

user_id	▸ total_spend
U01	15000
U02	8500
U03	52000
U04	3200
U05	28000
U06	6400
U07	41000
U08	1800

user_spending: 8 rows (total_spend: 1,800–52,000)

LEARNING POINTS

Choosing between NTILE and PERCENTILE_DISC: PERCENTILE_DISC(0.25), covered in Basic Q2, returns the value at the 1st-quartile threshold, whereas NTILE(4) returns the bucket number showing which quartile each row belongs to. Use PERCENTILE_DISC when you need the Q1 threshold, and NTILE when you need to classify each user into Q1–Q4. This distinction is standard in practice.

How buckets are distributed when there is a remainder: If the row count is not a multiple of n (for example, 9 rows ÷ 4), the remainder is added one row at a time starting with the higher buckets (bucket 1 has 3 rows; buckets 2–4 have 2 rows). Because the top bucket can contain more rows than the others, take care when the requirement demands exactly equal counts.

Combine GROUP BY to add segment-level statistics: After assigning NTILE in a CTE, GROUP BY segment plus aggregate functions calculates each segment's average, minimum, and maximum purchase amount in one query. For example:

SELECT segment, COUNT(*), AVG(total_spend), MIN(total_spend), MAX(total_spend) FROM ranked GROUP BY segment ORDER BY MIN(total_spend) DESC

. The NTILE → GROUP BY aggregation pattern is a standard customer-analysis technique.

ANTI-PATTERNS

Assume fixed-threshold classification produces equal-sized groups: Fixed thresholds such as CASE WHEN total_spend >= 30000 THEN 'VIP' WHEN total_spend >= 10000 THEN 'Gold' ... concentrate rows in particular buckets when the data is skewed, such as when only 1 user spends a large amount. Use NTILE whenever the business requirement is to equalize row counts.

Omit ORDER BY from NTILE and create a nondeterministic classification: If you omit ORDER BY as in NTILE(4) OVER (), the row-processing order is undefined and bucket numbers may change between executions. Always specify ORDER BY for NTILE. If ORDER BY is not unique because many values tie, add a unique key such as player_id to stabilize the result.

Practical Column: Extending to decile analysis

Marketing often uses decile analysis for finer segmentation than quartiles (NTILE(4)). Use NTILE(10) OVER (ORDER BY total_spend DESC) to classify users from the top 10% (Decile 1) to the bottom 10% (Decile 10), then analyze how many times the average purchase amount of Decile 1 exceeds the overall average. E-commerce sites can turn this directly into tiered initiatives, such as exclusive offers for Decile 1 and churn-prevention measures for Deciles 8–10. Changing NTILE(4) to NTILE(10) immediately implements the pattern.

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

CORR — Analyze investment efficiency statistically with the correlation between ad spend and revenue

CORRCOVAR_SAMPCorrelation AnalysisCorrelation CoefficientStatistical Testing

Background ▼

The correlation coefficient (CORR) expresses the strength of a linear relationship between 2 variables as a value from -1 to 1. PostgreSQL's CORR(y, x) function returns the sample correlation coefficient.

CORR(y, x)        -- Sample correlation coefficient (-1 to 1): strength and direction of a linear relationship
COVAR_SAMP(y, x) -- Sample covariance (divided by n-1): “corresponds to the numerator” of CORR
COVAR_POP(y, x)  -- Population covariance (divided by n)

-- Internal calculation of CORR:
-- CORR(y, x) = COVAR_SAMP(y, x) / (STDDEV_SAMP(x) * STDDEV_SAMP(y))

Guidelines for interpreting the correlation coefficient: |r| ≥ 0.9 → very strong / |r| 0.7–0.9 → strong / |r| 0.5–0.7 → moderate / |r| < 0.5 → weak. However, a correlation coefficient measures only the strength of a linear relationship. Always check for nonlinear relationships and the influence of outliers. It is also important that “correlation does not imply causation” (a third factor may be present).

Problem ▼

From the ad_performance table, calculate the correlation coefficient between ad spend (ad_spend) and revenue, each mean, and the number of data points in 1 row. Return corr_spend_revenue, avg_spend, avg_revenue, data_points (round the correlation coefficient to 2 decimal places and each mean to an integer).

Source table

▸ ad_performance (5 rows)

channel	ad_spend	revenue
Search	500000	2000000
Social	300000	2000000
Display	100000	500000
Video	400000	1800000
Email	200000	700000

Expected Output ▼

Expected output (1 row):

corr_spend_revenue	avg_spend	avg_revenue	data_points
0.88	300000	1400000	5

CORR ≈ 0.879 (strong positive correlation) / avg_spend: (500K+300K+100K+400K+200K)/5=300K / avg_revenue: (2000K+2000K+500K+1800K+700K)/5=1400K / Note: Social has revenue=2000K with ad spend=300K (higher ROI than the others)

Model Answer ▼

SELECT
  ROUND(CORR(revenue, ad_spend)::numeric, 2)  AS corr_spend_revenue,  -- Correlation coefficient (-1 to 1)
  ROUND(AVG(ad_spend))                       AS avg_spend,           -- Average ad spend (rounded to an integer)
  ROUND(AVG(revenue))                        AS avg_revenue,         -- Average revenue (rounded to an integer)
  COUNT(*)                                    AS data_points          -- Number of data points
FROM ad_performance;

/*
  Logical SQL evaluation order:
  1. FROM ad_performance           → read 5 rows
  2. CORR(revenue, ad_spend)       → calculate the correlation coefficient
  3. AVG(ad_spend) / AVG(revenue)  → calculate the means
  4. COUNT(*)                      → aggregate the row count
  5. SELECT                        → output 1 row
  */

Explanation (table transitions & key points) ▼

SELECT
  ROUND(CORR(revenue, ad_spend)::numeric, 2)  AS corr_spend_revenue,
  ROUND(AVG(ad_spend))                       AS avg_spend,
  ROUND(AVG(revenue))                        AS avg_revenue,
  COUNT(*)                                    AS data_points
FROM ad_performance;

LEGEND ▼

Rows read / loaded

① FROM ad_performance — read channel-level advertising data ▼

FROM ad_performanceRead all 5 rows from the ad_performance table. Search (500K→2,000K) and Video (400K→1,800K) have high ad spend and high revenue. Display (100K→500K) has low ad spend and low revenue.

1 / 5

channel	▸ ad_spend	▸ revenue
Search	500000	2000000
Social	300000	2000000
Display	100000	500000
Video	400000	1800000
Email	200000	700000

ad_performance: 5 rows (analyze the relationship between 2 variables)

LEARNING POINTS

Understand the internal structure of CORR: It is equivalent to CORR(y, x) = COVAR_SAMP(y, x) / (STDDEV_SAMP(x) × STDDEV_SAMP(y)). In practice, it is also important to inspect COVAR_SAMP(revenue, ad_spend) to see the covariance (the average of deviation products) and work backward to determine which channel is pushing CORR upward. Even CORR=0.88 can be distorted by an outlier in 1 channel.

Difference between the correlation coefficient and ROI: The correlation coefficient captures an overall tendency, while channel-level ROI (revenue / ad_spend) shows the efficiency of each investment. Social's ROI = 2,000,000 / 300,000 ≈ 6.67 is higher than Search's ROI = 4.0, identifying a candidate channel for increased spending. Using CORR to understand the overall structure and individual ROI for decisions is the standard 2-stage analysis in practice.

Precision of the ::numeric cast and ROUND: PostgreSQL's CORR returns double precision. You must cast to ::numeric before applying ROUND, as in ROUND(CORR(...)::numeric, 2), or you get a type error (the same applies to STDDEV and AVG). This is a common error pattern in practice, so make a habit of adding the ::numeric cast when combining aggregate functions with ROUND.

ANTI-PATTERNS

Conclude causation from CORR alone: It is dangerous to see CORR=0.88 and conclude that increasing ad spend will increase revenue. Both variables may be affected by the economy (a third factor), or the causation may be reversed (ad spend is increased because revenue is high). The correlation coefficient is only a hypothesis-generation tool, and causality must be tested through experimental designs such as A/B tests.

Trust an unstable CORR from a small sample: CORR=0.88 from 5 rows can change substantially because of a single outlier. At least 30 data points are recommended as a rough guideline for treating CORR as reliable (statistically, check the p-value and confidence interval). With small data sets, prioritize visual inspection of a scatter plot over the CORR value.

Practical Column: Marketing mix and correlation analysis

Companies with multiple advertising channels can calculate a correlation matrix at once with a SELECT containing multiple CORR expressions. Listing CORR(revenue, search_spend), CORR(revenue, social_spend), CORR(revenue, display_spend) gives an overview of which channel is most correlated with revenue. However, multicollinearity (correlation between channels) makes interpretation more complex, so it is common in practice to combine this with advanced statistical methods such as multiple regression and MMM (Marketing Mix Modeling). SQL's CORR is the first step in exploratory data analysis (EDA).

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.