Subqueries — Learn CASE × SQ, Multiple EXISTS, Top-N Correlation, and Multi-Level CTEs in Practice

QUESTION 6

CASE × subquery — fetch the overall average with CROSS JOIN and classify users into three tiers

CASE+SQCROSS JOINFROM-clause SQUser segments

Background ▼

Using a subquery as the threshold in a CASE expression enables data-driven, dynamic classification. By CROSS JOINing a subquery that returns only one row, you can efficiently attach the same aggregate value (a constant) to every row.

SELECT  s.user_id, s.total_spent, g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'
    ELSE                                          'LIGHT'
  END AS segment
FROM (...) AS s        -- per-user aggregation (FROM-clause SQ)
CROSS JOIN (...) AS g; -- attach the overall average (1 row) to every row

Why CROSS JOIN works here: when you CROSS JOIN a subquery that returns only one row, the Cartesian product is "original row count × 1 = original row count." You attach the same aggregate value to every row with a single evaluation. Writing a scalar SQ inside each CASE WHEN re-evaluates the same aggregate multiple times, but this pattern evaluates it only once.

Problem ▼

Classify users who have a total of completed orders (total_spent) in orders into HIGH_VALUE / NORMAL / LIGHT relative to the overall average. Return user_id, name, plan, total_spent, overall_avg, segment, ordered by total_spent descending. Exclude users with no completed orders.

Tables used

▸ users

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium
4	Jiro Yamada	standard
5	Saburo Ito	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	2	3000	completed

6 completed rows, total = 41,000 → ROUND(AVG) = 6,833 | per user: user1 = 20,000 / user3 = 13,500 / user5 = 4,500 / user2 = 3,000 | user4 has no completed → excluded

Expected Output ▼

Expected output (total_spent descending):

user_id	name	plan	total_spent	overall_avg	segment
1	Taro Tanaka	premium	20000	6833	HIGH_VALUE
3	Ichiro Suzuki	premium	13500	6833	NORMAL
5	Saburo Ito	free	4500	6833	LIGHT
2	Hanako Sato	free	3000	6833	LIGHT

※ HIGH_VALUE = avg×2 (13,666) or more / NORMAL = avg or more / LIGHT = below avg. user1 (20000 ≥ 13666) → HIGH / user3 (13500 < 13666 and ≥ 6833) → NORMAL / user5 and user2 → LIGHT

Model Answer ▼

SELECT
  u.user_id, u.name, u.plan,
  s.total_spent,
  g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'  -- at least twice the average
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'      -- at or above the average
    ELSE                                          'LIGHT'      -- below the average
  END AS segment
FROM   users u
INNER JOIN (                        -- derived table s: per-user completed total
  SELECT user_id, SUM(amount) AS total_spent
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
) AS s ON u.user_id = s.user_id    -- users with no completed are auto-excluded here
CROSS JOIN (                        -- derived table g: overall average in one row
  SELECT ROUND(AVG(amount)) AS overall_avg
  FROM   orders
  WHERE  status = 'completed'
) AS g                               -- one row, so CROSS JOIN attaches it to every row
ORDER BY s.total_spent DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate derived table s (WHERE  → GROUP BY)
  2. Evaluate derived table g
  3. FROM users u INNER JOIN s ON user_id
  4. CROSS JOIN g
  5. SELECT + CASE
  6. ORDER BY total_spent DESC
  */

Explanation (table transitions & key points) ▼

SELECT
  u.user_id, u.name, u.plan,
  s.total_spent, g.overall_avg,
  CASE
    WHEN s.total_spent >= 2 * g.overall_avg THEN 'HIGH_VALUE'
    WHEN s.total_spent >= g.overall_avg     THEN 'NORMAL'
    ELSE  'LIGHT'
  END AS segment
FROM   users u
INNER JOIN (
  SELECT user_id, SUM(amount) AS total_spent
  FROM   orders
  WHERE  status = 'completed'
  GROUP BY user_id
) AS s ON u.user_id = s.user_id
CROSS JOIN (
  SELECT ROUND(AVG(amount)) AS overall_avg
  FROM   orders
  WHERE  status = 'completed'
) AS g
ORDER BY s.total_spent DESC;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① Derived table s (per-user aggregation) ▼

SELECT user_id, SUM(amount) FROM orders WHERE status='completed' GROUP BY user_idAggregate completed orders with GROUP BY user_id. user4 has no completed order, so it is not included in derived table s.

1 / 5

user_id	Target completed orders	total_spent
1	101(8000) + 102(12000)	20000
2	108(3000)	3000
3	104(9500) + 105(4000)	13500
5	107(4500)	4500

→ derived table s: 4 rows (user4 has no completed)

LEARNING POINTS

Use a subquery result as a CASE threshold: by passing an aggregate value computed in advance by a subquery to CASE as a threshold — as in CASE WHEN total_spent >= 2 * overall_avg — you achieve "data-driven, dynamic classification." Unlike a hard-coded number (e.g., 10000), it automatically tracks the data as it changes.

The CROSS JOIN pattern for attaching a "constant" to every row: CROSS JOINing a subquery that returns only one row leaves the row count at "original × 1 = original." Writing a scalar SQ inside each CASE WHEN is evaluated up to three times, but the CROSS JOIN pattern evaluates it only once. In PostgreSQL you can also write the subquery result three times as a scalar in the SELECT clause, but CROSS JOIN is more efficient.

INNER JOIN naturally excludes "users with no target": by INNER JOINing derived table s (the completed aggregate), user4 — which has no completed order at all — is automatically excluded. If you want to include such users too (and display 0), use LEFT JOIN + COALESCE. Expressing the exclusion intent through the JOIN type is an important design point in practice.

ANTI-PATTERNS

Repeating the same scalar SQ in each CASE WHEN: writing a scalar SQ directly in each WHEN, as in WHEN total_spent >= 2*(SELECT AVG(amount)...), evaluates the same aggregate query up to three times. The recommended pattern is this one, which references a value evaluated once via CROSS JOIN (g.overall_avg).

Cartesian-product blow-up when the CROSS JOIN source has multiple rows: in today's pattern g is one row, so there is no problem, but if the CROSS JOIN source has N rows it inflates to "original row count × N." Confirm that a CROSS JOIN "always returns one row" before using it.

Field note: data-driven user segmentation

In e-commerce sites and SaaS products, it is common to classify users by spend and apply VIP measures to HIGH_VALUE and activation measures to LIGHT. By classifying with a "relative comparison against the overall average" rather than fixed values, the threshold automatically tracks the trend even as the data changes. This pattern works the same on BigQuery and Redshift, letting you dynamically generate segment columns for BI dashboards with SQL alone.

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 7

Chaining multiple EXISTS — combine AND/NOT EXISTS to narrow down target customers

Multiple EXISTSNOT EXISTSCorrelated SQCampaign targets

Background ▼

By chaining EXISTS / NOT EXISTS with AND in the WHERE clause, you can chain multiple independent conditions. Because each EXISTS is evaluated as a completely independent correlated subquery, you can build a compound filter such as "has A, and has B, and does not have C."

SELECT u.user_id, u.name
FROM   users u
WHERE  EXISTS     (SELECT 1 FROM orders o1 WHERE o1.user_id = u.user_id AND ...)
  AND  EXISTS     (SELECT 1 FROM orders o2 WHERE o2.user_id = u.user_id AND ...)
  AND  NOT EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u.user_id AND ...);

AND short-circuit evaluation: once the first EXISTS is FALSE, evaluation of the subsequent conditions for that row is skipped. user4 becomes FALSE at ①, so ② and ③ are not evaluated. Placing low-cost conditions first improves efficiency.

Problem ▼

From the users table, retrieve users who satisfy all three of the following conditions:
① has at least one completed order (EXISTS)
② has at least one order on or after 2024-05-16 (EXISTS)
③ has no cancelled order at all (NOT EXISTS)
Return user_id, name, plan, ordered by user_id ascending.

Tables used

▸ users

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium
4	Jiro Yamada	standard
5	Saburo Ito	free

▸ orders (with ordered_at)

order_id	user_id	amount	status	ordered_at
101	1	8000	completed	2024-04-10
102	1	12000	completed	2024-05-20
103	2	3500	pending	2024-05-10
104	3	9500	completed	2024-05-15
105	3	4000	completed	2024-06-01
106	4	2000	cancelled	2024-05-08
107	5	4500	completed	2024-04-20
108	2	5500	completed	2024-06-10

Expected Output ▼

Expected output (all three conditions AND, user_id ascending):

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium

※ user4: ①completed=0 → excluded | user5: ②latest order 107 (04-20) < 05-16 → excluded | user1, 2, 3 pass all three conditions

Model Answer ▼

SELECT u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (                       -- ① has at least one completed order
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id       -- correlation condition (references the outer u.user_id)
    AND  o1.status  = 'completed'
)
  AND  EXISTS (                       -- ② has at least one order on or after 2024-05-16
  SELECT 1 FROM orders o2
  WHERE  o2.user_id   = u.user_id
    AND  o2.ordered_at >= '2024-05-16' -- cutoff for the last 30 days
)
  AND  NOT EXISTS (                   -- ③ has no cancelled order at all
  SELECT 1 FROM orders o3
  WHERE  o3.user_id = u.user_id
    AND  o3.status  = 'cancelled'    -- detect any cancellation history
)
ORDER BY u.user_id;

/*
  Execution order (evaluated for each user row):
  1. FROM users u           → process 5 rows one by one
  2. EXISTS(completed)      → check whether a completed order exists
  3. EXISTS(recent order)   → check whether a recent order exists
  4. NOT EXISTS(cancelled)  → check that there is no cancellation
  5. ORDER BY user_id       → ascending
  */

Explanation (table transitions & key points) ▼

SELECT u.user_id, u.name, u.plan
FROM   users u
WHERE  EXISTS (
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status = 'completed'
)
  AND  EXISTS (
  SELECT 1 FROM orders o2
  WHERE  o2.user_id   = u.user_id
    AND  o2.ordered_at >= '2024-05-16'
)
  AND  NOT EXISTS (
  SELECT 1 FROM orders o3
  WHERE  o3.user_id = u.user_id
    AND  o3.status = 'cancelled'
)
ORDER BY u.user_id;

LEGEND ▼

Rows read / loaded

① FROM users ▼

FROM users uWith all 5 rows of the users table as the base, the three correlated EXISTS are evaluated in turn against them.

1 / 5

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium
4	Jiro Yamada	standard
5	Saburo Ito	free

All 5 rows read

LEARNING POINTS

Chaining multiple EXISTS with AND: you can chain multiple independent conditions as in WHERE EXISTS(...) AND EXISTS(...) AND NOT EXISTS(...). Because each EXISTS is evaluated as an independent correlated SQ, a compound filter of "has A, and has B, and does not have C" reads intuitively.

Cut cost with AND short-circuit evaluation: the SQL engine does not evaluate the right side of an AND when the left side is FALSE (short-circuit evaluation). user4 becomes FALSE at ①, so ② and ③ are not executed. Placing the most selective condition (with the highest FALSE rate) first lowers execution cost.

Make independence explicit with aliases (o1, o2, o3): because the three subqueries reference the same orders table, changing the aliases to o1, o2, o3 makes each SQ's independence explicit. It works even with the same name, but assigning distinct aliases is the best practice to avoid confusing the reader.

ANTI-PATTERNS

The NULL problem with NOT IN: if you use WHERE user_id NOT IN (SELECT user_id FROM orders WHERE status='cancelled') and orders has a row with user_id=NULL, all rows are excluded. NOT EXISTS does not have this problem, so using NOT EXISTS for negative conditions is an ironclad rule in practice.

Forgetting the correlation condition: without the join condition to the outer query, WHERE o1.user_id = u.user_id, inside each subquery, all users pass/are excluded as long as even one matching row exists in orders. With multiple EXISTS, be especially sure to check the correlation condition of each SQ.

Field note: generating a marketing target list

Compound conditions such as "send a coupon only to users who completed a purchase before last month, visited within the last 30 days, and have no cancellation history" are common in practice. The EXISTS chain is the standard pattern for combining such "multiple independent filters." As conditions grow, keeping OR out of the mix and continuing to chain with AND is the key to readability and maintainability.

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 8

Top-N correlated subquery — fetch the top 2 per category with a subquery

Correlated SQTop-N extractionRow numberingRanking

Background ▼

Extracting "the top N per category" is extremely common in practice. By counting "how many rows are higher than me" with a correlated subquery, you can achieve Top-N extraction even without the ROW_NUMBER() window function.

SELECT p.*
FROM   products p
WHERE  (
  SELECT COUNT(*)
  FROM   products p2
  WHERE  p2.category = p.category   -- within the same category
    AND  p2.price    >  p.price      -- products priced higher than me
) < 2;                               -- fewer than 2 → I am in the top 2

How it works: "within the same category, 0 rows priced higher than me → 1st place, 1 row → 2nd place, 2 or more → 3rd place or below." To take the top 2, the condition is "fewer than 2 rows priced higher than me (COUNT(*) < 2)." When multiple rows share the same price, they are all returned as "tied."

Problem ▼

From the products table, retrieve the top 2 products per category by price descending. Return product_id, name, category, price, ordered by category ascending, and within the same category by price descending.
Implement it with subqueries only; do not use window functions.

Tables used

▸ products

product_id	name	category	price
1	Plan A	service	9800
2	Plan B	service	4900
3	Plan C	service	2500
4	Template X	content	5500
5	Template Y	content	3800
6	Template Z	content	1200
7	API Add-on	option	3500
8	Support Extension	option	2200
9	Storage Add-on	option	1500

Expected Output ▼

Expected output (top 2 per category, category ascending, price descending):

product_id	name	category	price
4	Template X	content	5500
5	Template Y	content	3800
7	API Add-on	option	3500
8	Support Extension	option	2200
1	Plan A	service	9800
2	Plan B	service	4900

※ service: 3 rows → Plan A (9800) and Plan B (4900) are the top 2 / content: Template X, Y / option: API Add-on, Support Extension

Model Answer ▼

SELECT
  p.product_id,
  p.name,
  p.category,
  p.price
FROM   products p
WHERE  (
  SELECT COUNT(*)              -- count "how many rows are higher than me"
  FROM   products p2
  WHERE  p2.category = p.category  -- within the same category (correlation condition ①)
    AND  p2.price    >  p.price    -- rows priced higher than me (correlation condition ②)
) < 2                             -- fewer than 2 = I am within the top 2
ORDER BY p.category, p.price DESC;

/*
  Execution order (evaluated for each product row):
  1. FROM products p                → process 9 rows one by one
  2. Judge rank count with the correlated SQ → filter by number of higher rows in the same category
  3. ORDER BY category, price DESC  → category ascending, price descending
  */

Explanation (table transitions & key points) ▼

SELECT
  p.product_id, p.name, p.category, p.price
FROM   products p
WHERE  (
  SELECT COUNT(*)
  FROM   products p2
  WHERE  p2.category = p.category
    AND  p2.price    > p.price
) < 2
ORDER BY p.category, p.price DESC;

LEGEND ▼

Rows read / loaded

① FROM products p ▼

FROM products pProcess all 9 rows of the products table one by one. For each row, the correlated SQ that counts "the number of higher rows in the same category" is executed.

1 / 3

product_id	name	category	price
1	Plan A	service	9800
2	Plan B	service	4900
3	Plan C	service	2500
4	Template X	content	5500
5	Template Y	content	3800
6	Template Z	content	1200
7	API Add-on	option	3500
8	Support Extension	option	2200
9	Storage Add-on	option	1500

All 9 rows read

LEARNING POINTS

Express rank by "how many are above me": the COUNT(*) < N pattern means "fewer than N rows with a higher value = I am within the top N." This is an extremely important pattern for older DBs where ROW_NUMBER() is unavailable, and for deepening your understanding of correlated SQs.

Note that there are two correlation conditions: p2.category = p.category (limit to the same category) and p2.price > p.price (rows higher than me) combine to achieve "top N per category." If either is missing, you get unintended results.

Comparison with window functions: on PostgreSQL and MySQL 8.0+, using ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) is more efficient and more readable. However, the correlated-SQ pattern works on every RDBMS and is ideal for understanding the essence of the "top N" logic.

ANTI-PATTERNS

Multiple rows returned due to ties: when multiple rows have the same price, the COUNT is the same, so what you intend as the top 2 can return 3 or more rows. For example, if a category has two products at price=5000, both become "1st place." Confirm the specification for how ties should be handled.

Performance problems with large data: because the subquery runs for each row, if products has 100,000 rows, the inner query can run up to 100,000 times. In production, consider an implementation with ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) + a CTE.

Field note: comparing three implementation patterns for Top-N extraction

There are three patterns for implementing top-N per category: ① correlated SQ (COUNT < N), ② FROM-clause SQ + RANK(), and ③ CTE + ROW_NUMBER(). Old MySQL (5.x) can only use ①, but on PostgreSQL and MySQL 8.0+, ③ is the most efficient. When asked in an interview or code review to "fetch Top-N without a window function," the answer is ①. Choose the pattern according to the DB version and requirements.

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 9

Multi-level CTE — stack WITH clauses in three stages to implement cohort analysis

Multi-level CTEWITH clauseCohort analysisUser behavior analysis

Background ▼

A multi-level CTE that chains multiple CTEs (WITH clauses) is a standard practical technique for decomposing complex analytical queries step by step. Because each CTE can reference the previous CTE, you can intuitively write a pipeline of "narrow down the result of step 1 in step 2, then aggregate that result in step 3."

WITH
  step1 AS (                         -- stage 1: base aggregation
    SELECT ... FROM source_table
  ),
  step2 AS (                         -- stage 2: reference step1 and narrow down
    SELECT ... FROM step1 WHERE ...
  ),
  step3 AS (                         -- stage 3: further process step2
    SELECT ... FROM step2 JOIN ...
  )
SELECT * FROM step3;

The value of multi-level CTEs: decomposing complex nesting into "named stages" gives you: ① each step can be run and debugged on its own, ② intent is easier to convey to the team, and ③ when specs change, the affected scope is easier to identify.

Problem ▼

Implement the following three steps with a multi-level CTE:
① user_stats: aggregate per user the count of completed orders (order_cnt) and the total amount (total_spent)
② active_users: from ①, extract only users with order_cnt of 2 or more
③ Final SELECT: JOIN ② with the users table and return user_id, name, plan, order_cnt, total_spent in total_spent descending order.

Tables used

▸ users

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium
4	Jiro Yamada	standard
5	Saburo Ito	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	1	6500	completed

Expected Output ▼

Expected output (users with order_cnt ≥ 2, total_spent descending):

user_id	name	plan	order_cnt	total_spent
1	Taro Tanaka	premium	3	26500
3	Ichiro Suzuki	premium	2	13500

※ user1: 101(8000)+102(12000)+108(6500)=26500, cnt=3 / user3: 104(9500)+105(4000)=13500, cnt=2 / user5: cnt=1 → excluded / user2, 4: no completed → excluded

Model Answer ▼

WITH
  user_stats AS (               -- ① per-user completed aggregation
    SELECT
      user_id,
      COUNT(*)    AS order_cnt,   -- completed order count
      SUM(amount) AS total_spent  -- completed total amount
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  active_users AS (            -- ② reference user_stats and narrow down
    SELECT *
    FROM   user_stats             -- reference the previous CTE as an ordinary table
    WHERE  order_cnt >= 2        -- only buyers with 2 or more orders
  )
SELECT                           -- ③ JOIN active_users with users and output
  u.user_id,
  u.name,
  u.plan,
  a.order_cnt,
  a.total_spent
FROM   users u
INNER JOIN active_users a        -- a CTE can be JOINed just like an ordinary table
  ON  u.user_id = a.user_id
ORDER BY a.total_spent DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate CTE user_stats (WHERE  → GROUP BY
  2. Evaluate CTE active_users (references user_stats)
  3. FROM users u INNER JOIN active_users a
  4. SELECT the 5 columns
  5. ORDER BY total_spent DESC
  */

Explanation (table transitions & key points) ▼

WITH
  user_stats AS (
    SELECT
      user_id,
      COUNT(*) AS order_cnt,
      SUM(amount) AS total_spent
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  active_users AS (
    SELECT *
    FROM   user_stats
    WHERE  order_cnt >= 2
  )
SELECT
  u.user_id, u.name, u.plan,
  a.order_cnt, a.total_spent
FROM   users u
INNER JOIN active_users a
  ON  u.user_id = a.user_id
ORDER BY a.total_spent DESC;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① CTE: user_stats (aggregate all users) ▼

WITH user_stats AS (SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE status='completed' GROUP BY user_id)Aggregate completed orders per user_id with GROUP BY to build the CTE "user_stats." At this stage, no filtering by order_cnt is done yet.

1 / 4

user_id	completed orders	order_cnt	total_spent
1	101+102+108	3	26500
3	104+105	2	13500
5	107	1	4500

→ CTE user_stats: 3 rows (only users with completed)

LEARNING POINTS

Read a multi-level CTE as a "step-by-step pipeline": a later CTE can reference an earlier one, as in WITH a AS (...), b AS (...). Giving each CTE a meaningful name (user_stats, active_users) makes the SQL function as documentation. The design intent — "what to aggregate first, what to filter by next, and which table to JOIN with at the end" — reads directly.

CTEs are easy to debug: with a multi-level CTE, you can run just an intermediate CTE and check the intermediate result, as in "WITH user_stats AS (...)　SELECT * FROM user_stats." With nested subqueries, extracting just the inner part is cumbersome, but with CTEs you can verify each step independently.

A CTE is also a JOIN target: you can write a CTE name like active_users in the FROM/JOIN clause as an ordinary table name. This is the biggest difference between a CTE and a derived table, and being able to reference the same CTE from multiple places is also a strength of CTEs.

ANTI-PATTERNS

ORDER BY inside a CTE: writing ORDER BY inside a CTE is ignored by many DBs (only the final output's ORDER BY takes effect). A CTE is merely a "named intermediate table," and row order is not guaranteed. Always sort in the final SELECT's ORDER BY.

Over-decomposing into CTEs: decomposing even simple conditions into CTEs can, on the contrary, reduce readability. Using "one CTE has one clear role" as a guide, keeping it within 3 to 5 stages is the practical rule of thumb. If it is more complex than that, consider a view or a stored procedure.

Field note: writing BI dashboard queries with multi-level CTEs

On data warehouses such as BigQuery and Redshift, multi-level CTEs are the mainstream for reporting queries. For example, writing a 4-stage CTE like "① daily sales aggregation → ② month-over-month calculation → ③ flag against KPI thresholds → ④ final report output" with comments makes the SQL function as a spec sheet. "Once you exceed 3 stages, use a CTE" is the guideline for migrating from subqueries to CTEs.

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 10

CTE scoring — a user-evaluation query that aggregates multiple metrics independently and sums the scores

CTE scoringCASE+CTEComposite applicationRetention analysis

Background ▼

In practice, there are many cases where a customer rank is decided by a score that sums multiple metrics such as total amount, order count, and activity frequency. Aggregating each metric in an independent CTE and summing the scores in a final CTE is a design excellent for maintainability and readability.

Design points: when you want to change the score calculation logic (e.g., change the weight of the total amount), you only need to modify the relevant CTE. Compared with embedding all the logic in one huge SQL statement, the place to change is clear, and team review is easier.

Problem ▼

Using the steps below, compute each user's total score and return a ranking in score descending order, with ties broken by user_id ascending:
① spend_score: based on each user's completed total amount (total_spent), 10,000 or more → 3 points / 5,000 or more → 2 points / otherwise → 1 point (no completed → 0 points)
② freq_score: based on each user's completed order count (order_cnt), 3 or more → 3 points / 2 or more → 2 points / otherwise → 1 point (no completed → 0 points)
③ Final SELECT: for all users, return user_id, name, spend_score, freq_score, total_score (the sum).

Tables used

▸ users

user_id	name	plan
1	Taro Tanaka	premium
2	Hanako Sato	free
3	Ichiro Suzuki	premium
4	Jiro Yamada	standard
5	Saburo Ito	free

▸ orders

order_id	user_id	amount	status
101	1	8000	completed
102	1	12000	completed
103	2	3500	pending
104	3	9500	completed
105	3	4000	completed
106	4	2000	cancelled
107	5	4500	completed
108	1	6500	completed

Aggregation: user1 = 3 completed, 26500 yen / user3 = 2 completed, 13500 yen / user5 = 1 completed, 4500 yen / user2 = no completed / user4 = no completed

Expected Output ▼

Expected output (total_score descending, ties broken by user_id ascending):

user_id	name	spend_score	freq_score	total_score
1	Taro Tanaka	3	3	6
3	Ichiro Suzuki	3	2	5
5	Saburo Ito	1	1	2
2	Hanako Sato	0	0	0
4	Jiro Yamada	0	0	0

※ user1: 26500≥10000→spend=3, cnt=3≥3→freq=3, total=6 | user3: 13500≥10000→spend=3, cnt=2≥2→freq=2, total=5 | user5: 4500<5000→spend=1, cnt=1→freq=1, total=2 | user2, 4: no completed → 0+0=0

Model Answer ▼

WITH
  user_completed AS (           -- shared base: per-user completed aggregation
    SELECT
      user_id,
      COUNT(*)    AS order_cnt,
      SUM(amount) AS total_spent
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  spend_score AS (               -- ① total-amount score (all users)
    SELECT
      u.user_id,
      CASE
        WHEN c.total_spent >= 10000 THEN 3  -- high amount: 3 points
        WHEN c.total_spent >=  5000 THEN 2  -- mid amount: 2 points
        WHEN c.total_spent IS NOT NULL THEN 1 -- low amount: 1 point
        ELSE 0                            -- no completed: 0 points
      END AS spend_score
    FROM   users u
    LEFT JOIN user_completed c    -- keep users with no completed as NULL too
      ON  u.user_id = c.user_id
  ),
  freq_score AS (                -- ② order-count score (all users)
    SELECT
      u.user_id,
      CASE
        WHEN c.order_cnt >= 3 THEN 3       -- high frequency: 3 points
        WHEN c.order_cnt >= 2 THEN 2       -- mid frequency: 2 points
        WHEN c.order_cnt IS NOT NULL THEN 1 -- low frequency: 1 point
        ELSE 0                             -- no completed: 0 points
      END AS freq_score
    FROM   users u
    LEFT JOIN user_completed c
      ON  u.user_id = c.user_id
  )
SELECT                            -- ③ JOIN all scores and sum
  u.user_id,
  u.name,
  s.spend_score,
  f.freq_score,
  s.spend_score + f.freq_score AS total_score  -- sum the scores
FROM   users u
JOIN   spend_score s ON u.user_id = s.user_id  -- reference the spend_score CTE
JOIN   freq_score  f ON u.user_id = f.user_id  -- reference the freq_score CTE
ORDER BY total_score DESC, u.user_id;          -- score descending, ties by ID ascending

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate CTE user_completed                  → aggregate completed per user
  2. Evaluate CTE spend_score                     → assign a score by spend (LEFT JOIN)
  3. Evaluate CTE freq_score                      → assign a score by frequency (LEFT JOIN)
  4. FROM users JOIN spend_score JOIN freq_score  → join
  5. SELECT spend+freq                            → compute the total score
  6. ORDER BY total_score DESC, user_id           → descending
  */

Explanation (table transitions & key points) ▼

WITH
  user_completed AS (
    SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_spent
    FROM   orders  WHERE  status = 'completed'  GROUP BY user_id
  ),
  spend_score AS (
    SELECT u.user_id,
      CASE
        WHEN c.total_spent >= 10000 THEN 3
        WHEN c.total_spent >= 5000  THEN 2
        WHEN c.total_spent IS NOT NULL THEN 1
        ELSE 0
      END AS spend_score
    FROM   users u  LEFT JOIN user_completed c  ON u.user_id = c.user_id
  ),
  freq_score AS ( ... similar CASE on order_cnt ... )
SELECT
  u.user_id, u.name, s.spend_score, f.freq_score,
  s.spend_score + f.freq_score AS total_score
FROM   users u
JOIN   spend_score s ON u.user_id = s.user_id
JOIN   freq_score  f ON u.user_id = f.user_id
ORDER BY total_score DESC, u.user_id;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① CTE: user_completed (base aggregation) ▼

WITH user_completed AS (...)A base table that aggregates only completed orders. Both subsequent CTEs reference it. user2 and user4, which have no completed, are not included here.

1 / 4

user_id	order_cnt	total_spent
1	3	26500
3	2	13500
5	1	4500

→ CTE user_completed: 3 rows

LEARNING POINTS

The "aggregate independently → assign scores → sum" design pattern: aggregating each metric in an independent CTE and summing them in the final SELECT is a classic practical design called a scoring engine. When you want to change the spend_score logic, you only modify the relevant CTE, so maintainability is high.

Cover all users with LEFT JOIN + ELSE 0: to treat user2 and user4, who have no completed, as "0 points" rather than excluding them, we use LEFT JOIN instead of INNER JOIN. When the join fails (no completed), NULL is returned, so the CASE's ELSE 0 fires. When you want to include everyone in the ranking, this pattern is essential.

Reusing a shared base CTE: the CTE user_completed is referenced by both spend_score and freq_score. You avoid writing the same aggregation twice, and data consistency is preserved. "Referencing one CTE from multiple CTEs" is a CTE-specific strength that subqueries cannot match.

ANTI-PATTERNS

Beware of summing NULL scores: in the spend_score + freq_score calculation, if either is NULL, the sum is also NULL (NULL + number = NULL). It is important to replace NULL with 0, as in COALESCE(spend_score, 0) + COALESCE(freq_score, 0), or to guarantee 0 with the CASE ELSE as done here.

A CTE is not a temporary table (in many DBs): on PostgreSQL and MySQL 8.0+, a CTE is by default inlined (equivalent to a subquery). Beware of the misconception that "writing it in a CTE caches it." If you really want to cache, use a temporary table (CREATE TEMP TABLE) or a materialized CTE (PostgreSQL's MATERIALIZED hint).

Field note: applying this to RFM scoring

This pattern applies directly to marketing's RFM analysis (Recency, Frequency, Monetary). ① Recency CTE: score by days since the last order ② Frequency CTE: score by order count ③ Monetary CTE: score by total amount → sum R+F+M in the final SELECT to decide the user rank. This SQL structure is also a widely used pattern in data-analysis platforms on BigQuery and Redshift.

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.