SQL Benchmarking — Applied LAG/LEAD, RANK, Scoring

QUESTION 6

LAG() + CTE — Visualize Quarterly Competitor Trend Acceleration and Slowdown with Period-over-Period Growth

LAG / LEADCTEPeriod-over-Period GrowthNULLIF · Zero-Division Protection

Background ▼

LAG(expr, offset, default) is a window function that returns the value offset rows before the current row. It is an essential pattern for period-over-period and year-over-year calculations.

LAG(revenue) OVER (
  PARTITION BY company_name   -- Do not take the preceding row across companies
  ORDER BY     quarter        -- Define the “one row earlier” position by quarter
)                              -- Default offset=1, default=NULL

Prevent division by zero with NULLIF: if the previous period is 0, revenue / prev_revenue causes a division-by-zero error. NULLIF(prev_revenue, 0) returns NULL when the value is 0, so division by NULL produces NULL (not an error) and is handled safely.

Calculate LAG only once in a CTE: writing LAG directly multiple times in the SELECT for a growth-rate calculation reduces maintainability. First establish prev_revenue in a CTE and reuse it in the outer query as a best practice.

Problem ▼

Using quarterly revenue from three companies, calculate the previous-period revenue (prev_revenue) and period-over-period growth rate (growth_rate_pct, rounded to one decimal place). Return company_name, quarter, revenue, prev_revenue, growth_rate_pct, sorted by company_name ascending and then quarter ascending. Q1 has NULL for both prev_revenue and growth_rate_pct.

Tables

▸ quarterly_revenue

company_name	quarter	revenue
AlphaTech	2023Q1	300
AlphaTech	2023Q2	360
AlphaTech	2023Q3	280
AlphaTech	2023Q4	400
BetaSoft	2023Q1	250
BetaSoft	2023Q2	220
BetaSoft	2023Q3	290
BetaSoft	2023Q4	310
GammaSys	2023Q1	120
GammaSys	2023Q2	150
GammaSys	2023Q3	170
GammaSys	2023Q4	160

※ Revenue unit: JPY 100 million

Expected Output ▼

company_name	quarter	revenue	prev_revenue	growth_rate_pct
AlphaTech	2023Q1	300	NULL	NULL
AlphaTech	2023Q2	360	300	20.0
AlphaTech	2023Q3	280	360	-22.2
AlphaTech	2023Q4	400	280	42.9
BetaSoft	2023Q1	250	NULL	NULL
BetaSoft	2023Q2	220	250	-12.0
BetaSoft	2023Q3	290	220	31.8
BetaSoft	2023Q4	310	290	6.9
GammaSys	2023Q1	120	NULL	NULL
GammaSys	2023Q2	150	120	25.0
GammaSys	2023Q3	170	150	13.3
GammaSys	2023Q4	160	170	-5.9

Model Answer ▼

WITH lagged AS (
  SELECT
    company_name,
    quarter,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name   -- Independent window for each company
      ORDER BY     quarter        -- Define the preceding row in ascending quarter order
    ) AS prev_revenue
  FROM quarterly_revenue
)
SELECT
  company_name,
  quarter,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
    / NULLIF(prev_revenue, 0),   -- Prevent division by zero (Q1 NULL safely remains NULL)
    1
  ) AS growth_rate_pct
FROM   lagged
ORDER BY company_name, quarter;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE lagged                 → Define the CTE and evaluate the LAG window function
  2. Outer SELECT FROM lagged          → Read the CTE
  3. SELECT                            → Evaluate and format the columns (NULLIF, ROUND)
  4. ORDER BY company_name, quarter    → Sort and return the result
*/

Explanation (table transitions & key points) ▼

WITH lagged AS (
  SELECT
    company_name, quarter, revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name
      ORDER BY     quarter
    ) AS prev_revenue
  FROM quarterly_revenue
)
SELECT
  company_name, quarter, revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
    / NULLIF(prev_revenue, 0), 1
  ) AS growth_rate_pct
FROM   lagged
ORDER BY company_name, quarter;

LEGEND ▼

Rows read / loaded

① FROM quarterly_revenue — Read 12 rows ▼

FROM quarterly_revenueRead the 12 rows in quarterly_revenue (3 companies × 4 quarters). Next, LAG() OVER (PARTITION BY company_name ORDER BY quarter) retrieves the previous-period value and stores it in the lagged CTE.

1 / 4

company_name	quarter	revenue
AlphaTech	2023Q1	300
AlphaTech	2023Q2	360
AlphaTech	2023Q3	280
AlphaTech	2023Q4	400
BetaSoft	2023Q1	250
BetaSoft	2023Q2	220
BetaSoft	2023Q3	290
BetaSoft	2023Q4	310
GammaSys	2023Q1	120
GammaSys	2023Q2	150
GammaSys	2023Q3	170
GammaSys	2023Q4	160

12 rows read (3 companies × 4 quarters)

LEARNING POINTS

PARTITION BY in LAG() prevents companies from mixing: without PARTITION BY company_name, the “preceding row” for AlphaTech Q1 could be another company’s final quarter. Use PARTITION BY to create an independent window per company and reference only that company’s time series. LEAD() returns the value from the next row instead.

NULLIF solves division-by-zero and the resulting error: NULLIF(prev_revenue, 0) returns NULL when prev_revenue is 0. Division by NULL produces NULL (not an error), so it safely propagates as ROUND(NULL, 1) = NULL and prevents a division-by-zero error. Q1 prev_revenue is already NULL, so no NULLIF conversion is needed there, but a row with prev=0 is safe as well.

Calculate LAG only once in a CTE: without a CTE, writing growth_rate_pct would require repeating the same LAG expression, such as (revenue - LAG(...) OVER(...)) * 100.0 / NULLIF(LAG(...) OVER(...), 0). Once prev_revenue is established in a CTE, the outer SELECT uses simple column references and is easier to read.

ANTI-PATTERNS

Trying to use LAG() directly in WHERE: WHERE LAG(revenue) OVER(...) > 0 is a SQL syntax error. Window functions cannot be used in WHERE. Calculate the value first in a CTE or derived table, then filter it in the outer WHERE.

Omitting PARTITION BY and taking a previous-period value across companies: without PARTITION BY, LAG treats every row as one window, so BetaSoft’s first quarter—the row after AlphaTech’s final quarter—can receive AlphaTech’s value as prev_revenue. Period-over-period comparisons almost always require PARTITION BY.

Field Note: Period-over-Period vs Year-over-Year — Choosing Between LAG(1) and LAG(4)

Quarterly data commonly calls for two comparisons: the “previous quarter” (QoQ) and the “same quarter last year” (YoY). LAG(revenue, 1) (the default) returns the immediately preceding quarter, while LAG(revenue, 4) returns the value four rows earlier (= the same quarter one year earlier). In seasonal businesses such as retail, travel, and e-commerce, YoY is often a growth-rate indicator that reflects reality better than QoQ. To return both, write LAG(revenue,1) OVER(...) AS prev_q, LAG(revenue,4) OVER(...) AS prev_year_q side by side; two window calculations can be processed in one pass when they use the same partition.

QUESTION 7

RANK() + CTE — Category-Level Competitor Ranking and Top-N Filtering

RANK / DENSE_RANKCTE + WHERECategory Top-NHandling Tied Ranks

Background ▼

RANK(), DENSE_RANK(), and ROW_NUMBER() are all window functions that return ranks, but they handle ties differently.

Function	When two rows tie for second	Next rank
`ROW_NUMBER()`	2, 3 (pseudo-unique assignment)	4
`RANK()`	2, 2 (tie)	4 (skips a number)
`DENSE_RANK()`	2, 2 (tie)	3 (consecutive)

WITH ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category ORDER BY annual_revenue DESC) AS rnk
  FROM product_revenue
)
SELECT * FROM ranked WHERE rnk <= 2;  -- Top rank 2 or better in each category, including ties

The CTE + WHERE top-N extraction pattern: a window-function result cannot be referenced in WHERE in the same SELECT (WHERE comes earlier in the logical evaluation order). Assign the rank in a CTE and filter it in the outer WHERE as the standard pattern.

Problem ▼

From the competitor revenue data for each category, assign the within-category sales rank (rank_in_category) and return only companies ranked 2 or better in each category, including ties. Return category, company_name, annual_revenue, rank_in_category, with categories in the specified AI/ML, Cloud, Security, Data Analytics order, rank_in_category ascending within each category, and company_name ascending for ties.

Tables

▸ product_revenue

company_name	category	annual_revenue
AlphaTech	Cloud	4200
AlphaTech	Security	1800
AlphaTech	AI/ML	950
BetaSoft	Cloud	2800
BetaSoft	AI/ML	1500
BetaSoft	Data Analytics	800
GammaSys	Security	900
GammaSys	AI/ML	650
GammaSys	Data Analytics	420
DeltaNet	Cloud	1200
DeltaNet	Data Analytics	600

※ annual_revenue unit: JPY 100 million

Expected Output ▼

category	company_name	annual_revenue	rank_in_category
AI/ML	BetaSoft	1500	1
AI/ML	AlphaTech	950	2
Cloud	AlphaTech	4200	1
Cloud	BetaSoft	2800	2
Security	AlphaTech	1800	1
Security	GammaSys	900	2
Data Analytics	BetaSoft	800	1
Data Analytics	DeltaNet	600	2

Model Answer ▼

WITH ranked AS (
  SELECT
    category,
    company_name,
    annual_revenue,
    RANK() OVER (
      PARTITION BY category             -- Independent ranking within each category
      ORDER BY     annual_revenue DESC  -- Rank by descending revenue
    ) AS rank_in_category
  FROM product_revenue
)
SELECT
  category,
  company_name,
  annual_revenue,
  rank_in_category
FROM   ranked
WHERE  rank_in_category <= 2            -- Top rank 2 or better in each category, including ties
ORDER BY CASE category
  WHEN 'AI/ML' THEN 1
  WHEN 'Cloud' THEN 2
  WHEN 'Security' THEN 3
  WHEN 'Data Analytics' THEN 4
  END, rank_in_category, company_name;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE ranked                 → Define the CTE and evaluate the RANK window function
  2. Outer SELECT FROM ranked          → Read the CTE
  3. WHERE rank_in_category <= 2      → Filter the rows
  4. SELECT                            → Evaluate the columns
  5. ORDER BY CASE category WHEN 'AI/ML' THEN 1 WHEN 'Cloud' THEN 2 WHEN 'Security' THEN 3 WHEN 'Data Analytics' THEN 4 END, rank_in_category, company_name → Sort and return the result
*/

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT
    category, company_name, annual_revenue,
    RANK() OVER (
      PARTITION BY category
      ORDER BY     annual_revenue DESC
    ) AS rank_in_category
  FROM product_revenue
)
SELECT
  category, company_name, annual_revenue, rank_in_category
FROM   ranked
WHERE  rank_in_category <= 2
ORDER BY CASE category WHEN 'AI/ML' THEN 1 WHEN 'Cloud' THEN 2 WHEN 'Security' THEN 3 WHEN 'Data Analytics' THEN 4 END, rank_in_category, company_name;

LEGEND ▼

Rows read / loaded

① FROM product_revenue — Read 11 rows ▼

FROM product_revenueRead the 11 rows in product_revenue (four categories with different company participation). Each company’s annual_revenue is stored in a row. RANK() OVER will add a within-category ranking.

1 / 4

category	company_name	annual_revenue
AI/ML	AlphaTech	950
AI/ML	BetaSoft	1500
AI/ML	GammaSys	650
Cloud	AlphaTech	4200
Cloud	BetaSoft	2800
Cloud	DeltaNet	1200
Security	AlphaTech	1800
Security	GammaSys	900
Data Analytics	BetaSoft	800
Data Analytics	DeltaNet	600
Data Analytics	GammaSys	420

11 rows read (4 categories)

LEARNING POINTS

How to choose RANK / DENSE_RANK / ROW_NUMBER: if two companies tie for second and both should remain among the top two ranks, use RANK() or DENSE_RANK(). If you need exactly two rows, use ROW_NUMBER() and add a tie-breaker sort. In competitor rankings, RANK often reflects reality best because it handles ties correctly. Its only difference from DENSE_RANK is whether rank numbers are skipped.

CTE + WHERE top-N filtering is a golden pattern: a window-function result cannot be referenced in WHERE in the same SELECT. Establish the rank in a CTE or derived table, then filter it in the outer WHERE; this is the reliable pattern for cases such as the top three departments or top five regions.

RANK() without PARTITION BY produces an overall rank: RANK() OVER (ORDER BY annual_revenue DESC) without PARTITION BY treats all 11 rows as one window and ranks them together. For category-level rankings, always state PARTITION BY category explicitly.

ANTI-PATTERNS

Calling RANK() directly in WHERE: WHERE RANK() OVER (...) <= 2 is a syntax error. Window functions can be used only in the SELECT list or ORDER BY. Establish the rank once in a CTE or derived table, then filter it in WHERE.

Using ROW_NUMBER for top-N and discarding ties: using ROW_NUMBER for “rank 2 or better” gives one tied second-place company row number 2 and the other 3, so one is excluded. For fair treatment of ties in competitor analysis, choose RANK or DENSE_RANK; reserve ROW_NUMBER for requirements that explicitly need exactly N rows.

Field Note: Practical Applications of Category-Level Top-N

“Top N companies in each category” is one of the most common query patterns in competitor benchmarking and market reports. Typical applications include comparing KPIs for the top three share leaders in each product line, generating a focus list from the top five revenue customers in each sales territory, and automatically selecting the top ten CPA campaigns in each advertising channel for a report. Parameterizing N with WHERE rank_in_category <= :n turns the query into a reusable template that produces different report views when users change N.

QUESTION 8

Multiple-CTE Scoring — Automatically Calculate Overall KPI Grades with Staged Aggregation

Multiple WITH CTEsCASE WHENCompetitor ScoringStaged Aggregation and Grading

Background ▼

Multiple CTEs can be defined as comma-separated entries in a WITH clause, and a preceding CTE can be referenced by a later CTE. This decomposes complex aggregation into named, readable stages.

WITH
step1 AS (
  SELECT ..., CASE WHEN col >= 30 THEN 3 ELSE 1 END AS score
  FROM source_table
),
step2 AS (          -- step1 can be referenced here
  SELECT *, score_a + score_b AS total
  FROM step1
)
SELECT * FROM step2;

Be careful when omitting ELSE from CASE WHEN: omitting ELSE in CASE WHEN ... END means ELSE NULL. In scoring logic, always specify ELSE the minimum score.

Problem ▼

From KPI data for six competitors, score revenue scale, growth rate, and NPS from 1 to 3 points, then calculate the total (maximum 9 points) and overall grade (S/A/B/C). Return company_name, revenue_score, growth_score, nps_pts, total_score, grade, sorted by total_score descending and then company_name ascending.

Scoring rules: revenue_score (revenue_bn ≥30→3, ≥10→2, else 1) | growth_score (growth_pct ≥25→3, ≥10→2, else 1) | nps_pts (nps_score ≥65→3, ≥50→2, else 1) | grade (total ≥8→S, ≥6→A, ≥4→B, else C)

Tables

▸ competitor_kpis

company_name	revenue_bn	growth_pct	nps_score
AlphaTech	42.0	18.5	72
BetaSoft	28.0	12.3	58
GammaSys	15.0	31.2	65
DeltaNet	5.0	8.1	48
EpsilonSys	3.0	-2.4	41
ZetaCloud	2.0	5.6	55

※ revenue_bn: revenue (billions of JPY) / growth_pct: year-over-year growth (%) / nps_score: customer NPS

Expected Output ▼

company_name	revenue_score	growth_score	nps_pts	total_score	grade
AlphaTech	3	2	3	8	S
GammaSys	2	3	3	8	S
BetaSoft	2	2	2	6	A
ZetaCloud	1	1	2	4	B
DeltaNet	1	1	1	3	C
EpsilonSys	1	1	1	3	C

Model Answer ▼

WITH
scores AS (
  SELECT
    company_name, revenue_bn, growth_pct, nps_score,
    CASE WHEN revenue_bn >= 30 THEN 3
         WHEN revenue_bn >= 10 THEN 2
         ELSE                       1 END AS revenue_score, -- Revenue-scale score
    CASE WHEN growth_pct >= 25  THEN 3
         WHEN growth_pct >= 10  THEN 2
         ELSE                        1 END AS growth_score,  -- Growth-rate score
    CASE WHEN nps_score >= 65  THEN 3
         WHEN nps_score >= 50  THEN 2
         ELSE                        1 END AS nps_pts        -- NPS score
  FROM competitor_kpis
),
totaled AS (
  SELECT
    *,
    revenue_score + growth_score + nps_pts AS total_score  -- Total (maximum 9 points)
  FROM scores
)
SELECT
  company_name,
  revenue_score, growth_score, nps_pts, total_score,
  CASE WHEN total_score >= 8 THEN 'S'
       WHEN total_score >= 6 THEN 'A'
       WHEN total_score >= 4 THEN 'B'
       ELSE                        'C' END AS grade  -- Overall grade
FROM   totaled
ORDER BY total_score DESC, company_name;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE scores                 → Define the CTE and evaluate each score with CASE
  2. Define CTE totaled                → Define the CTE and calculate total_score
  3. Outer SELECT FROM totaled         → Read the CTE
  4. SELECT                            → Evaluate the columns (CASE determines grade)
  5. ORDER BY total_score DESC, company_name → Sort and return the result
*/

Explanation (table transitions & key points) ▼

WITH
scores AS (
  SELECT
    company_name, revenue_bn, growth_pct, nps_score,
    CASE WHEN revenue_bn >= 30 THEN 3
         WHEN revenue_bn >= 10 THEN 2
         ELSE                    1 END AS revenue_score,
    CASE WHEN growth_pct >= 25  THEN 3
         WHEN growth_pct >= 10  THEN 2
         ELSE                    1 END AS growth_score,
    CASE WHEN nps_score >= 65   THEN 3
         WHEN nps_score >= 50   THEN 2
         ELSE                    1 END AS nps_pts
  FROM competitor_kpis
),
totaled AS (
  SELECT *, revenue_score + growth_score + nps_pts AS total_score
  FROM scores
)
SELECT
  company_name, revenue_score, growth_score, nps_pts, total_score,
  CASE WHEN total_score >= 8 THEN 'S'
       WHEN total_score >= 6 THEN 'A'
       WHEN total_score >= 4 THEN 'B'
       ELSE                   'C' END AS grade
FROM   totaled
ORDER BY total_score DESC, company_name;

LEGEND ▼

Rows read / loaded

① FROM competitor_kpis — Read 6 rows ▼

FROM competitor_kpisRead the six rows in competitor_kpis. The three indicators—revenue_bn, growth_pct, and nps_score—will be converted to scores with CASE WHEN.

1 / 4

company_name	revenue_bn	growth_pct	nps_score
AlphaTech	42	18.5	72
BetaSoft	28	12.3	58
GammaSys	15	31.2	65
DeltaNet	5	8.1	48
EpsilonSys	3	-2.4	41
ZetaCloud	2	5.6	55

6 rows read

LEARNING POINTS

Show calculation dependencies with a CTE chain: in a chain such as WITH scores AS (...), totaled AS (... FROM scores), the named stages “① calculate scores → ② calculate the total → ③ assign the grade” make each transformation readable and allow each CTE to be selected independently for debugging.

CASE WHEN scoring enables ORDER BY and aggregation: converting qualitative conditions such as “growth of at least 25% is excellent” into numeric scores enables quantitative operations such as ORDER BY total_score or AVG(total_score). This is a standard pattern for quantifying qualitative criteria in credit review, CRM ranking, and HR evaluation.

CTEs can reference earlier definitions in order, not later ones: totaled can reference scores because scores is defined first. Later CTEs cannot be mutually referenced with earlier CTEs. Organize complex dependencies as a DAG before deciding the CTE order.

ANTI-PATTERNS

Omitting ELSE in CASE WHEN: without ELSE, a row that matches no condition becomes NULL. If NULL enters a scoring calculation, total_score = 3 + NULL + 2 = NULL and the aggregation breaks. Always state the minimum score in ELSE for scoring logic.

Chaining CTEs too deeply (five or more stages): when a CTE chain reaches five or more stages, query-planner optimization can become unstable. If the design exceeds three or four stages, consider a temporary table, view, or staged processing in the application. PostgreSQL 12 and later allow explicit MATERIALIZED/NOT MATERIALIZED choices for CTEs.

Field Note: Practical Applications of Scoring Models

CASE WHEN scoring combined with CTEs is widely used to “quantify qualitative conditions and evaluate them in one pass.” Typical applications include assigning an overall competitor grade from multiple KPIs as in this question, rating credit risk from financial indicators and transaction history on an A–D scale, calculating CRM lead priority from company size, industry, and behavior history, and rating procurement risk from supplier delivery adherence, quality, and cost. Turning thresholds such as 30→10→else into a parameter table makes it possible to update scoring criteria without changing SQL, producing a highly maintainable design.

QUESTION 9

CASE WHEN Pivot — Convert Quarterly Revenue from Long to Wide and Calculate Within-Year Growth

SUM + CASE WHENGROUP BYLong-to-Wide TransformationConditional Aggregation and NULLIF

Background ▼

Conditional aggregation uses the SUM(CASE WHEN ... END) pattern to convert long-format (row-oriented) data into wide-format (column-oriented) data. PostgreSQL has no dedicated PIVOT syntax, so this is the standard implementation.

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev
FROM   quarterly_sales
GROUP BY company_name;

ELSE 0 vs ELSE NULL: ELSE 0 adds 0 for non-matching rows. With ELSE NULL, SUM ignores NULL, so the result is the same, but AVG and COUNT behave differently. ELSE 0 is the conventional choice for pivot aggregation.

Problem ▼

Using quarterly revenue for three companies in 2024, pivot Q1–Q4 revenue into columns, then calculate the annual total (annual_total) and the within-year Q1→Q4 growth rate (q4_vs_q1_pct, rounded to one decimal place). Return company_name, q1_rev, q2_rev, q3_rev, q4_rev, annual_total, q4_vs_q1_pct, sorted by annual_total descending.

Tables

▸ quarterly_sales

company_name	year	quarter	revenue
AlphaTech	2024	Q1	340
AlphaTech	2024	Q2	410
AlphaTech	2024	Q3	320
AlphaTech	2024	Q4	450
BetaSoft	2024	Q1	260
BetaSoft	2024	Q2	240
BetaSoft	2024	Q3	310
BetaSoft	2024	Q4	340
GammaSys	2024	Q1	130
GammaSys	2024	Q2	165
GammaSys	2024	Q3	185
GammaSys	2024	Q4	170

※ Revenue unit: JPY 100 million / the table is assumed to contain multiple years, so filter with WHERE year = 2024

Expected Output ▼

company_name	q1_rev	q2_rev	q3_rev	q4_rev	annual_total	q4_vs_q1_pct
AlphaTech	340	410	320	450	1520	32.4
BetaSoft	260	240	310	340	1150	30.8
GammaSys	130	165	185	170	650	30.8

Model Answer ▼

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,  -- 0 for every non-Q1 row
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_rev,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_rev,
  SUM(revenue)                                               AS annual_total,  -- Sum all four quarters
  ROUND(
    (SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) -
     SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END)) * 100.0
    / NULLIF(
        SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END), 0
      ),
    1
  )                                                          AS q4_vs_q1_pct  -- Q1→Q4 growth rate
FROM   quarterly_sales
WHERE  year = 2024                                                          -- 2024 only
GROUP BY company_name
ORDER BY annual_total DESC;

/*
  Execution order (logical SQL evaluation order):
  1. FROM quarterly_sales   → Read the rows
  2. WHERE year = 2024      → Filter the rows
  3. GROUP BY company_name  → Group the rows
  4. SELECT                 → Evaluate and format aggregates (SUM, NULLIF, ROUND)
  5. ORDER BY annual_total DESC  → Sort and return the result
  */

Explanation (table transitions & key points) ▼

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_rev,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_rev,
  SUM(revenue) AS annual_total,
  ROUND(
    (SUM(CASE WHEN quarter='Q4' THEN revenue ELSE 0 END)
     - SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END)) * 100.0
    / NULLIF(SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END), 0), 1
  ) AS q4_vs_q1_pct
FROM   quarterly_sales
WHERE  year = 2024
GROUP BY company_name
ORDER BY annual_total DESC;

LEGEND ▼

Rows read / loaded

① FROM quarterly_sales WHERE year=2024 — Read 12 rows ▼

FROM quarterly_sales WHERE year = 2024Read the 12 rows for year=2024 from quarterly_sales (3 companies × 4 quarters). For this long-format data, first evaluate the quarter conditions with CASE WHEN row by row.

1 / 4

company_name	quarter	revenue
AlphaTech	Q1	340
AlphaTech	Q2	410
AlphaTech	Q3	320
AlphaTech	Q4	450
BetaSoft	Q1	260
BetaSoft	Q2	240
BetaSoft	Q3	310
BetaSoft	Q4	340
GammaSys	Q1	130
GammaSys	Q2	165
GammaSys	Q3	185
GammaSys	Q4	170

12 rows (2024, 3 companies × 4 quarters)

LEARNING POINTS

The essence of conditional aggregation — a “filtered SUM”: SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END) adds revenue only for Q1 rows and adds 0 for all others. The result is therefore the Q1 revenue alone. Combining this with GROUP BY performs all condition-specific aggregates within each group, which is the core of the pattern.

How ELSE 0 and ELSE NULL affect aggregation: for SUM, ELSE 0 and ELSE NULL usually produce the same result because SUM ignores NULL. However, COUNT(CASE WHEN ... END) also counts ELSE 0, while it does not count ELSE NULL. ELSE 0 is conventional for pivot aggregation; with AVG or COUNT, ELSE NULL often matches the intended semantics better.

PostgreSQL has no PIVOT syntax — dynamic pivots use crosstab(): SQL Server and Oracle provide PIVOT syntax, but PostgreSQL has no standard implementation. When the number of columns is dynamic, such as a changing number of quarters, the tablefunc extension’s crosstab() or post-processing in the application is practical. For a fixed number of columns, the pattern in this question is the most readable.

ANTI-PATTERNS

Using MAX(CASE WHEN...) by mistake: some implementations use MAX instead of SUM, but MAX(CASE WHEN quarter='Q1' THEN revenue ELSE NULL END) returns the maximum when a company has multiple Q1 rows. If there is exactly one row per company per quarter, the result is the same; with duplicates, SUM and MAX differ. Use SUM to make the intent explicit.

Maintainability drops as the column count grows: changing this to monthly data requires 12 CASE WHEN columns, increasing maintenance cost. When the number of columns grows, consider crosstab() or pivoting in the application, such as with pandas pivot_table.

Field Note: Using Wide-Format Data with BI Tools

A wide-format matrix of “every company × every period” is an ideal input format for BI tools such as Tableau, Looker, and Power BI. BI tools can also pivot long-format data automatically, but converting it to wide format and saving it as a view or table in advance simplifies BI-side queries and improves dashboard response times. It also works well with pandas df.pivot_table() and Excel exports, so it is frequently used in automated “aggregate → export → distribute report” pipelines. Precomputing derived metrics such as q4_vs_q1_pct in SQL also removes the need for BI-side calculated-field definitions and centralizes metric governance.

QUESTION 10

PERCENT_RANK() + LAG() + Two-Stage CTE — Track Annual Changes in Competitor Relative Position

PERCENT_RANKLAG + Two-Stage CTERelative-Position ChangePercentile Tracking

Background ▼

PERCENT_RANK() returns each row’s relative rank as a percentile from 0.0 to 1.0. The formula is (rank - 1) / (total_rows - 1).

Formula	Example with 5 rows (ORDER BY revenue ASC)
`(rank-1) / (5-1)`	Minimum revenue=0.0 / middle=0.5 / maximum revenue=1.0

PERCENT_RANK() OVER (
  PARTITION BY year             -- Independent rank space for each year
  ORDER BY     revenue_bn       -- Ascending: smallest→0.0, largest→1.0
) * 100                         -- Convert to a percentage (0–100)

Dependent processing with two CTE stages: “calculate PERCENT_RANK” → “retrieve the previous year’s PERCENT_RANK with LAG” has a dependency, so the first CTE establishes pct_rank and the second CTE applies LAG.

Problem ▼

From two years of revenue data for five companies, calculate relative revenue position within each year (pct_rank, percentage rounded to one decimal place) and the year-over-year position change (pct_rank_change). Return company_name, year, revenue_bn, pct_rank, prev_pct_rank, pct_rank_change, sorted by year ascending and then pct_rank descending.

Tables

▸ annual_performance

company_name	year	revenue_bn
AlphaTech	2022	35.0
BetaSoft	2022	26.0
GammaSys	2022	18.0
DeltaNet	2022	12.0
EpsilonSys	2022	7.0
AlphaTech	2023	42.0
BetaSoft	2023	28.0
GammaSys	2023	15.0
DeltaNet	2023	16.0
EpsilonSys	2023	9.0

Expected Output ▼

company_name	year	revenue_bn	pct_rank	prev_pct_rank	pct_rank_change
AlphaTech	2022	35.0	100.0	NULL	NULL
BetaSoft	2022	26.0	75.0	NULL	NULL
GammaSys	2022	18.0	50.0	NULL	NULL
DeltaNet	2022	12.0	25.0	NULL	NULL
EpsilonSys	2022	7.0	0.0	NULL	NULL
AlphaTech	2023	42.0	100.0	100.0	0.0
BetaSoft	2023	28.0	75.0	75.0	0.0
DeltaNet	2023	16.0	50.0	25.0	25.0
GammaSys	2023	15.0	25.0	50.0	-25.0
EpsilonSys	2023	9.0	0.0	0.0	0.0

Model Answer ▼

WITH
yearly_ranks AS (
  SELECT
    company_name,
    year,
    revenue_bn,
    ROUND(
      (PERCENT_RANK() OVER (
        PARTITION BY year         -- Independent rank space for each year
        ORDER BY     revenue_bn   -- Ascending: smallest=0.0, largest=1.0
      ) * 100)::numeric,
      1
    ) AS pct_rank
  FROM annual_performance
),
with_lag AS (
  SELECT
    company_name,
    year,
    revenue_bn,
    pct_rank,
    LAG(pct_rank) OVER (
      PARTITION BY company_name  -- Keep each company’s time series independent
      ORDER BY     year          -- Retrieve the preceding year’s row
    ) AS prev_pct_rank
  FROM yearly_ranks
)
SELECT
  company_name,
  year,
  revenue_bn,
  pct_rank,
  prev_pct_rank,
  ROUND(pct_rank - prev_pct_rank, 1) AS pct_rank_change  -- Arithmetic with NULL produces NULL
FROM   with_lag
ORDER BY year, pct_rank DESC;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE yearly_ranks            → Define the CTE and evaluate PERCENT_RANK
  2. Define CTE with_lag                → Define the CTE and evaluate LAG
  3. SELECT                             → Evaluate and format the columns (ROUND)
  4. ORDER BY year, pct_rank DESC             → Sort and return the result
*/

Explanation (table transitions & key points) ▼

WITH
yearly_ranks AS (
  SELECT
    company_name, year, revenue_bn,
    ROUND(
      (PERCENT_RANK() OVER (
        PARTITION BY year
        ORDER BY     revenue_bn
      ) * 100)::numeric, 1
    ) AS pct_rank
  FROM annual_performance
),
with_lag AS (
  SELECT
    company_name, year, revenue_bn, pct_rank,
    LAG(pct_rank) OVER (
      PARTITION BY company_name
      ORDER BY     year
    ) AS prev_pct_rank
  FROM yearly_ranks
)
SELECT
  company_name, year, revenue_bn,
  pct_rank, prev_pct_rank,
  ROUND(pct_rank - prev_pct_rank, 1) AS pct_rank_change
FROM   with_lag
ORDER BY year, pct_rank DESC;

LEGEND ▼

Rows read / loaded

① FROM annual_performance — Read 10 rows ▼

FROM annual_performanceRead the 10 rows in annual_performance (5 companies × 2 years). From 2022 to 2023, GammaSys revenue drops from 18.0 to 15.0, while DeltaNet rises from 12.0 to 16.0.

1 / 4

company_name	year	revenue_bn
AlphaTech	2022	35
BetaSoft	2022	26
GammaSys	2022	18
DeltaNet	2022	12
EpsilonSys	2022	7
AlphaTech	2023	42
BetaSoft	2023	28
GammaSys	2023	15
DeltaNet	2023	16
EpsilonSys	2023	9

10 rows read (5 companies × 2 years)

LEARNING POINTS

How to calculate and interpret PERCENT_RANK(): PERCENT_RANK = (rank − 1) / (total_rows − 1). With five companies, the minimum revenue is 0.0 and the maximum is 1.0 when ORDER BY is ascending. Multiply by 100 to express it as a percentage, where “50.0% = the industry midpoint.” This indicator represents relative position within the industry rather than absolute revenue, so it can show cases where a company’s position does not change even while the whole market grows.

Why two CTE stages are needed — PERCENT_RANK and LAG cannot be used together in one pass: combining PERCENT_RANK and LAG in one SELECT would make LAG try to reference PERCENT_RANK before its result is established, which is a logical error. Establish pct_rank in the first CTE and apply LAG in the second CTE to make the dependency explicit.

LAG’s PARTITION BY preserves each company’s time series: PARTITION BY company_name processes each company’s annual data in an independent window. Without it, the preceding row for “Company A in 2023” could be “Company B in 2022,” causing another company’s position to become prev_pct_rank. Configuring PARTITION BY is critical when comparing time series for multiple entities.

ANTI-PATTERNS

Trying to write PERCENT_RANK and LAG together in one CTE: SELECT ..., PERCENT_RANK() OVER(...) AS p, LAG(PERCENT_RANK() OVER(...)) OVER(...) causes an error in PostgreSQL. Window-function nesting—putting one window function inside the argument of LAG—is not allowed. Split the query into two CTEs and establish pct_rank in the first.

Confusing PERCENT_RANK with CUME_DIST: CUME_DIST() uses the formula rank / total_rows, so the minimum is always greater than 0 and the maximum is 1.0. PERCENT_RANK’s minimum is 0.0. Use PERCENT_RANK when the lowest-ranked company should be 0%, and CUME_DIST when you want the cumulative distribution—“what percentage of companies are below me?”

Field Note: Practical Competitor Positioning with Percentiles

Absolute revenue alone can hide the fact that “our relative position is falling while the entire industry is growing,” but PERCENT_RANK makes the change in relative market position explicit. As with DeltaNet here, it reveals at a glance that “revenue increased from 12.0 to 16.0 billion JPY, while the company rose from third to second in the industry (pct_rank_change=+25.0).” In practice, teams often visualize quarterly percentile tracking with line charts and include the resulting trend in executive reports. Dividing by two axes—PERCENT_RANK() OVER (PARTITION BY year, category ORDER BY revenue)—also enables the same pattern to analyze position changes by product line.