Common Table Expression Problems

Problem 1: Most Frequently Ordered Product(s) for Each Customer

Problem Statement

A store keeps its clients, their purchases, and its item catalog in three tables. The company wants to know, for every customer, which item(s) they buy most often.

Business context Knowing each customer's favorite item lets a store recommend that item again, plan stock, or send a personal offer. If a customer likes several items equally, all of them are useful to know.

Goal For each customer, find the item(s) they ordered most frequently. If several items are tied for the highest frequency, return all of them.

Input Three tables: Clients, Purchases, and Items.

Output client_id, item_id, and item_name for each customer's most frequently ordered item(s), ordered by client_id ascending, and then item_id ascending.

Table Structure

Table: Clients

Table: Purchases

Table: Items

Column Meaning

• client_id (Clients) — the unique ID of a client, and the primary key
• name — the client's name
• purchase_id — the unique ID of a purchase, and the primary key of Purchases
• purchase_date — the date of the purchase
• client_id (Purchases) — the client who made the purchase
• item_id (Purchases) — the item that was purchased
• item_id (Items) — the unique ID of an item, and the primary key of Items
• item_name — the item's name
• price — the item's price

Important Notes

• The Clients table is given for context, but the answer does not need it. Everything needed is in Purchases and Items.
• Each row in Purchases is one purchase. "How often a client ordered an item" means how many Purchases rows they have for that item.
• Ties must be kept. If a client's top count is shared by several items, all of those items are returned.
• A client with no purchases (Eva, client 5) has no row in Purchases, so they do not appear in the result.
• Item price and purchase date are not needed for this question.

Example

Input: Clients

Input: Purchases

Input: Items

Expected Output

CREATE TABLE Clients (client_id INTEGER, name TEXT);
INSERT INTO Clients VALUES
  (1, 'Andrew'),
  (2, 'Bella'),
  (3, 'Chris'),
  (4, 'David'),
  (5, 'Eva');
CREATE TABLE Purchases (purchase_id INTEGER, purchase_date TEXT, client_id INTEGER, item_id INTEGER);
INSERT INTO Purchases VALUES
  (1, '2021-07-31', 1, 1),
  (2, '2021-07-30', 2, 2),
  (3, '2021-08-29', 3, 3),
  (4, '2021-07-29', 4, 1),
  (5, '2021-06-10', 1, 2),
  (6, '2021-08-01', 2, 1),
  (7, '2021-08-01', 3, 3),
  (8, '2021-08-03', 1, 2),
  (9, '2021-08-07', 2, 3),
  (10, '2021-07-15', 1, 2);
CREATE TABLE Items (item_id INTEGER, item_name TEXT, price INTEGER);
INSERT INTO Items VALUES
  (1, 'Laptop', 1200),
  (2, 'Phone', 800),
  (3, 'Tablet', 600),
  (4, 'Headphones', 150);

Explanation

• Andrew (client 1) ordered the Phone 3 times and the Laptop once. His most frequent item is the Phone.
• Bella (client 2) ordered the Laptop, the Phone, and the Tablet once each. They are all tied, so all three are included.
• Chris (client 3) ordered the Tablet twice, so the Tablet is his most frequent item.
• David (client 4) ordered the Laptop once, so the Laptop is his most frequent item.
• Eva (client 5) has no orders, so she is not in the result.

Extra Example (a tie with different counts)

Using the same Items table, suppose Purchases is:

Output:

Client 10 bought the Laptop 2 times, the Phone 2 times, and the Tablet once. The top count is 2, shared by the Laptop and the Phone, so both are returned. Client 11 bought only Headphones, once.

Intuition

The question has two layers:

1. Count how many times each client bought each item.
2. Pick the item(s) with the highest count for each client.

The second layer needs the results of the first layer twice: once to find each client's highest count, and once to get the items that match it. That is a perfect job for a CTE. Write the counting once, give it a name, and use it twice.

Mental Model

Think of a scoreboard for every client. The scoreboard has one line per item, showing how many times the client bought it. To find the favorite item, you look at the highest number on each client's scoreboard, and circle every line that has that number.

 Client 1:  Laptop 1   Phone 3 <-- highest   -> favorite: Phone
 Client 2:  Laptop 1 <-- tie   Phone 1 <-- tie   Tablet 1 <-- tie   -> all three
 Client 3:  Tablet 2 <-- highest              -> favorite: Tablet
 Client 4:  Laptop 1 <-- highest              -> favorite: Laptop

Thought Process

1. What is one "line" on the scoreboard? A client-and-item pair, with a count. So GROUP BY client_id, item_id with COUNT(*).
2. Where does the item name come from? The Items table, through item_id. Join Purchases and Items.
3. How do I find each client's highest count? Group the scoreboard by client_id and take MAX(order_count).
4. How do I keep the matching lines? Compare each scoreboard line's (client_id, order_count) pair with each client's (client_id, max_count) pair. Using a pair (not only the count) keeps one client's maximum from being applied to another client.
5. Do ties survive? Yes, because every line equal to the maximum is kept.
6. How do I sort? ORDER BY client_id, item_id.

Approach

Step 1: Join Purchases and Items Get the item name for each purchase, using item_id.

Step 2: Compute frequency in a CTE Name it ProductFrequency. Count purchases for each (client_id, item_id, item_name).

Step 3: Find the maximum frequency per client A subquery reads ProductFrequency, groups by client_id, and takes MAX(order_count).

Step 4: Keep only the top lines Keep the CTE rows whose (client_id, order_count) pair appears in that list of maximums.

Step 5: Sort ORDER BY client_id, item_id.

SQL Query

WITH ProductFrequency AS (
  -- How many times did each client order each item?
  SELECT p.client_id,
         p.item_id,
         i.item_name,
         COUNT(*) AS order_count
  FROM Purchases p
  JOIN Items i ON p.item_id = i.item_id
  GROUP BY p.client_id, p.item_id, i.item_name
)
SELECT pf.client_id, pf.item_id, pf.item_name
FROM ProductFrequency pf
-- Keep only rows whose (client, count) matches that client's highest count
WHERE (pf.client_id, pf.order_count) IN (
  SELECT client_id, MAX(order_count)     -- each client's highest count
  FROM ProductFrequency                  -- the CTE is used a second time here
  GROUP BY client_id
)
ORDER BY pf.client_id, pf.item_id;

Query Explanation

• WITH ProductFrequency AS (...) — the CTE. It holds one row per client and item, with the number of times that client ordered that item.
• JOIN Items i ON p.item_id = i.item_id — attaches the item name to each purchase.
• GROUP BY p.client_id, p.item_id, i.item_name — one group per client-item pair. item_name is included so the query is valid under strict SQL modes, since it is selected but not aggregated.
• COUNT(*) AS order_count — counts the purchase rows in each group.
• The subquery SELECT client_id, MAX(order_count) ... GROUP BY client_id — reads the CTE again and returns one row per client: the client and their highest count. This subquery does not use any column from the outer query, so it is a non-correlated subquery: it runs once, on its own.
• (pf.client_id, pf.order_count) IN (...) — a pair on the left side needs a two-column result on the right side. A CTE row is kept only if its pair matches one of the (client, max count) pairs.
• ORDER BY pf.client_id, pf.item_id — required order.

Dry Run

Original tables

Purchases (10 rows) and Items (4 rows), as in the example.

↓

Step 1: Count purchases per client and item (this is the CTE ProductFrequency)

(Eva, client 5, has no purchases, so she has no row here.)

↓

Step 2: The subquery, the highest count for each client

↓

Step 3: Keep CTE rows whose (client_id, order_count) pair is in that list

↓

Final output (after ORDER BY client_id, item_id)

SQL Execution Flow

Purchases p  JOIN  Items i
        |
GROUP BY client_id, item_id, item_name
        |
COUNT(*)  --> CTE: ProductFrequency
        |
        +--> used by the subquery:
        |       GROUP BY client_id, MAX(order_count)
        |
Outer query reads ProductFrequency again
        |
WHERE (client_id, order_count) IN (subquery result)
        |
SELECT client_id, item_id, item_name
        |
ORDER BY client_id, item_id

Time Complexity

Time Complexity: O(n log n), where n is the number of rows in Purchases The join and first grouping are the main cost. The second grouping (for the maximum) works on the much smaller CTE. The final sort is on the small result.

Space Complexity: O(k), where k is the number of distinct client-item pairs The CTE holds one row per pair.

Why: The CTE means the counting is written once (and can be reused), instead of repeating the big GROUP BY inside the subquery.

Common Mistakes to Avoid

Mistake 1: Comparing with the overall maximum instead of each client's maximum

Why it happens: "Maximum" sounds like a single number.

Wrong:

WHERE pf.order_count = (SELECT MAX(order_count) FROM ProductFrequency)

The overall highest count is 3 (Andrew's Phone), so only client 1 is returned. Everyone else is lost.

Correct: Take the maximum per client, with GROUP BY client_id.

Mistake 2: Matching only the count, not the pair

Why it happens: The pair syntax looks unusual.

Wrong:

WHERE pf.order_count IN (SELECT MAX(order_count) FROM ProductFrequency GROUP BY client_id)

The list of maximums is (3, 1, 2, 1). Andrew's Laptop has a count of 1, and 1 is in the list (it is Bella's maximum), so Andrew's Laptop is wrongly kept.

Correct: Compare the pair (client_id, order_count), so each client is only matched with their own maximum.

Mistake 3: Grouping by client_id only

Why it happens: "Per customer" suggests grouping by customer.

Wrong:

SELECT client_id, COUNT(*) FROM Purchases GROUP BY client_id

This counts all purchases of the client, not per item.

Correct: Group by client_id and item_id (and item_name).

Mistake 4: Leaving item_name out of GROUP BY

Why it happens: item_id already identifies the item.

Why it matters: Under MySQL's default strict mode (ONLY_FULL_GROUP_BY), a selected column that is neither grouped nor aggregated raises an error.

Correct: Add i.item_name to the GROUP BY.

Mistake 5: Using a LEFT JOIN from Clients

Why it happens: Trying to include every client.

Wrong idea:

FROM Clients c LEFT JOIN Purchases p ON p.client_id = c.client_id

Eva would appear with NULLs, but the problem says clients with no orders must not be in the result.

Correct: Start from Purchases, so only clients who bought something appear.

Mistake 6: Picking a single row per client and losing ties

Why it happens: LIMIT 1 or ROW_NUMBER() feels like the obvious way to get "the top".

Why it hurts: Bella has three tied items, and only one would be returned.

Correct: Keep every row that equals the client's maximum count.

Mistake 7: Forgetting ORDER BY

Why it happens: The rows often look sorted by accident.

Correct: End with ORDER BY pf.client_id, pf.item_id.

Interview Follow-up Questions

• "Solve this with a second CTE instead of a subquery." (See the alternative below.)
• "How would you solve this with a window function like RANK()?" (Rank items inside each client by count, and keep rank 1. This is covered in a later topic.)
• "Why does the IN compare a pair and not just the count?"
• "Why is the subquery non-correlated here?"
• "How would you also show the count next to each favorite item?"

Alternative: a two-step CTE pipeline

WITH ProductFrequency AS (
  SELECT p.client_id, p.item_id, i.item_name, COUNT(*) AS order_count
  FROM Purchases p
  JOIN Items i ON p.item_id = i.item_id
  GROUP BY p.client_id, p.item_id, i.item_name
),
MaxFrequency AS (
  SELECT client_id, MAX(order_count) AS max_count
  FROM ProductFrequency
  GROUP BY client_id
)
SELECT pf.client_id, pf.item_id, pf.item_name
FROM ProductFrequency pf
JOIN MaxFrequency m
  ON m.client_id = pf.client_id
 AND m.max_count = pf.order_count
ORDER BY pf.client_id, pf.item_id;

This gives the same result. The subquery is replaced by a named CTE and a JOIN, which many people find easier to read, and it follows the same pipeline pattern as the "top salesperson" example in the concept notes.

Similar Problems

• Top-performing salesperson (the multi-step pipeline in the concept notes)
• Customers Who Bought All Products and other "compare a group with its own maximum or total" problems
• The "order with the maximum amount for each user" example in the Subqueries notes (Day 30)

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• "Most frequent," "top," or "highest" item per group with ties allowed → count in a CTE, then keep rows equal to the per-group maximum
• The same aggregated result is needed twice → put it in a CTE and name it
• Need to compare a row with its own group's maximum → match on the pair (group, value), not on the value alone
• Only one row per group is wanted even with ties → that is a different problem (use a tie-breaker rule, or a window function)

Problem 2: Find Missing Subtasks for Each Task

Problem Statement

A project tool stores how many subtasks each task has, and which subtasks have already been executed. The team wants a list of the subtasks that have not been executed yet.

Business context Each task is split into numbered subtasks (1, 2, 3, ...). Knowing exactly which ones are still missing tells the team what work is left, and lets them spot tasks that are stuck.

Goal For each task, find the subtasks that have not been executed.

Input Two tables: Tasks (how many subtasks each task has) and Executed (which subtasks were executed).

Output task_id and subtask_id for each missing subtask, ordered by task_id and then subtask_id.

Table Structure

Table: Tasks

Table: Executed

Column Meaning

• task_id (Tasks) — the unique ID of a task, and the primary key
• subtasks_count — the number of subtasks the task has. Its values range from 2 to 20. A task with count 3 has subtasks numbered 1, 2, and 3.
• task_id (Executed) — the task the executed subtask belongs to
• subtask_id — the number of the subtask that was executed

Important Notes

• The pair (task_id, subtask_id) in Executed is unique, so a subtask is listed at most once per task.
• The Tasks table only stores a count, not the individual subtask numbers. So the list "1, 2, ..., subtasks_count" for each task has to be generated. MySQL has no built-in number generator, so a recursive CTE is used.
• A task whose subtasks are all executed (Task 3 in the example) gives no rows.
• A task with no executed subtask (Task 2 in the example) gives all of its subtasks.
• The recursion is short: at most 20 rounds, far below MySQL's default limit of 1000.
• The matching uses both task_id and subtask_id. Subtask 1 of Task 3 being executed does not mean that subtask 1 of Task 1 was executed.
• This solution assumes the two columns in Executed are never NULL (see Mistake 6).

Example

Input: Tasks

Input: Executed

Expected Output

CREATE TABLE Tasks (task_id INTEGER, subtasks_count INTEGER);
INSERT INTO Tasks VALUES
  (1, 3),
  (2, 2),
  (3, 4);
CREATE TABLE Executed (task_id INTEGER, subtask_id INTEGER);
INSERT INTO Executed VALUES
  (1, 2),
  (3, 1),
  (3, 2),
  (3, 3),
  (3, 4);

Explanation

• Task 1 has 3 subtasks (1, 2, 3). Subtask 2 was executed, so subtasks 1 and 3 are missing.
• Task 2 has 2 subtasks (1, 2). None was executed, so both are missing.
• Task 3 has 4 subtasks (1, 2, 3, 4). All were executed, so nothing is missing.

Extra Example

Tasks:

Executed:

Output:

Task 5 has subtasks 1 to 5, and 1, 3, 5 were executed, so 2 and 4 are missing. Task 6 has everything executed, so it gives no rows.

Intuition

Finding what is missing means comparing "everything that should exist" with "what actually happened". The Executed table already holds what happened. But "everything that should exist" is not stored anywhere as a list. It exists only as a count. So the plan has two steps:

1. Build the full list of subtasks (1 up to subtasks_count) for every task.
2. Remove the ones that appear in Executed. What is left is the missing set.

Mental Model

Think of a checklist with numbered boxes. The Tasks table says how many boxes each checklist has (3 boxes, 2 boxes, 4 boxes). First you draw all the boxes. Then you tick the boxes listed in Executed. The empty boxes are your answer.

 Task 1 (3 boxes):   [ ] 1    [x] 2    [ ] 3     -> missing: 1, 3
 Task 2 (2 boxes):   [ ] 1    [ ] 2               -> missing: 1, 2
 Task 3 (4 boxes):   [x] 1    [x] 2    [x] 3   [x] 4   -> missing: none

The recursive CTE "draws the boxes" by counting down from subtasks_count to 1.

Thought Process

1. Can I list the subtask numbers with a normal query? No. They are not stored. I need to generate them.
2. How do I generate 1..N for each task? With a recursive CTE. Start at subtasks_count (the anchor), then keep subtracting 1 (the recursive step) while the number is bigger than 1 (the stop condition).
3. Why count down instead of up? Because the starting number is already in the table (subtasks_count). Counting down from it needs no extra information.
4. How do I remove the executed ones? Compare the pair (task_id, number) against the pairs in Executed, and keep the ones that are not there (NOT IN, or the safer NOT EXISTS).
5. How do I sort? ORDER BY task_id, subtask_id.

Approach

Step 1: Generate a series for each task

• Anchor: for each task, start with subtasks_count.
• Recursive step: subtract 1 from the previous number.
• Stop when the number reaches 1 (WHERE subtasks_count > 1).

Step 2: Filter out executed subtasks Keep only the generated pairs (task_id, number) that are not in Executed.

Step 3: Return the result Show task_id and the number as subtask_id, in order.

SQL Query

WITH RECURSIVE cte AS (
  -- Anchor: one starting row per task, at its highest subtask number
  SELECT task_id, subtasks_count
  FROM Tasks

  UNION ALL

  -- Recursive step: count down by 1 until we reach subtask 1
  SELECT task_id, subtasks_count - 1
  FROM cte
  WHERE subtasks_count > 1          -- stop condition: do not go below 1
)
SELECT
  task_id,
  subtasks_count AS subtask_id       -- the counter column is the subtask number
FROM cte
-- Keep only the (task, subtask) pairs that were NOT executed
WHERE (task_id, subtasks_count) NOT IN (
  SELECT task_id, subtask_id
  FROM Executed
)
ORDER BY task_id, subtask_id;

Query Explanation

• WITH RECURSIVE cte AS (...) — defines a recursive CTE called cte.
• Anchor SELECT task_id, subtasks_count FROM Tasks — one row per task. Its subtasks_count is used as the starting subtask number (the largest one).
• UNION ALL — stacks the rows produced in each round on top of the earlier ones.
• Recursive SELECT task_id, subtasks_count - 1 FROM cte WHERE subtasks_count > 1 — for every row from the previous round, creates a row for the task with the number one smaller. The condition > 1 stops the countdown at 1. (If it were > 0, a bogus subtask 0 would be created.)
• subtasks_count AS subtask_id — in the final query, the counter column is the subtask number, so it is renamed.
• WHERE (task_id, subtasks_count) NOT IN (SELECT task_id, subtask_id FROM Executed) — removes pairs that appear in Executed. Both the task and the subtask number must match.
• ORDER BY task_id, subtask_id — required order.

Dry Run

Original tables

Tasks: (1, 3), (2, 2), (3, 4). Executed: (1, 2), (3, 1), (3, 2), (3, 3), (3, 4).

↓

Step 1: The recursive CTE builds the full list, round by round

↓

Step 2: All rows in the CTE, sorted by task and subtask

↓

Step 3: Check each pair against Executed

↓

Final output

SQL Execution Flow

Anchor:     SELECT task_id, subtasks_count FROM Tasks
               |
Recursive:  SELECT task_id, subtasks_count - 1 FROM cte WHERE subtasks_count > 1
               |   (repeats until no new rows)
               v
cte = every (task_id, 1..subtasks_count) pair
               |
WHERE (task_id, subtasks_count) NOT IN (SELECT task_id, subtask_id FROM Executed)
               |
SELECT task_id, subtasks_count AS subtask_id
               |
ORDER BY task_id, subtask_id

Time Complexity

Time Complexity: O(S + E), where S is the total number of generated subtasks (the sum of all subtasks_count values) and E is the number of rows in Executed Generating the list costs one row per subtask. Checking each generated pair against Executed is fast when Executed has an index on (task_id, subtask_id).

Space Complexity: O(S) The generated list is held while it is filtered.

Why: Since subtasks_count is at most 20, S is at most 20 times the number of tasks, so the work stays small and predictable.

Common Mistakes to Avoid

Mistake 1: Writing WITH instead of WITH RECURSIVE

Why it happens: The CTE keyword seems enough.

Why it fails: Without RECURSIVE, the CTE cannot refer to itself, and MySQL reports an error.

Correct: WITH RECURSIVE cte AS (...).

Mistake 2: An off-by-one stop condition

Why it happens: > 0 and > 1 look similar.

Wrong:

WHERE subtasks_count > 0

The recursive step would also produce a row with subtasks_count = 0. A subtask 0 does not exist, but it is not in Executed, so it would appear as a fake "missing" subtask.

Correct: WHERE subtasks_count > 1, so the last row produced is 1.

Mistake 3: No stop condition at all

Why it happens: The recursive part looks complete.

Result: the countdown never ends (0, -1, -2, ...), and MySQL stops it with an error when it reaches the recursion limit.

Correct: Always include a condition that becomes false.

Mistake 4: Matching only subtask_id, not the task too

Why it happens: It looks simpler.

Wrong:

WHERE subtasks_count NOT IN (SELECT subtask_id FROM Executed)

Subtask 1 of Task 3 was executed, so the number 1 is in the list, and every task's subtask 1 is wrongly removed. The row (1, 1) would disappear from the answer.

Correct: Compare the pair (task_id, subtasks_count) with (task_id, subtask_id).

Mistake 5: Forgetting ORDER BY

Why it happens: The output of a recursive CTE often looks ordered by accident.

Correct: Add ORDER BY task_id, subtask_id. The generated rows come out in countdown order (high to low), so without ORDER BY the output would not be in the required order.

Mistake 6: Using NOT IN when Executed could contain NULL

Why it happens: NOT IN reads naturally.

Why it hurts: As covered in the Subqueries notes, a NULL in the list can make NOT IN return no rows at all. This solution is safe only if task_id and subtask_id in Executed are never NULL.

Correct: Use NOT EXISTS, which is safe even with NULLs (see the alternative below).

Mistake 7: Leaving the output column named subtasks_count

Why it happens: The counter column keeps its old name.

Correct: Write subtasks_count AS subtask_id, as the required output column is subtask_id.

Interview Follow-up Questions

• "Rewrite the filter using NOT EXISTS or a LEFT JOIN." (See the alternative below.)
• "How would you find subtasks in Executed that are invalid, meaning the number is bigger than the task's subtasks_count?"
• "How would you count the missing subtasks for each task, instead of listing them?"
• "What is MySQL's default recursion limit, and what happens if a task had 2000 subtasks?" (The default cte_max_recursion_depth is 1000, so the query would stop with an error unless the limit is raised.)
• "How would you generate the numbers 1 to N without a recursive CTE?" (A numbers table, for example.)

Alternative: cleaner names and a LEFT JOIN

WITH RECURSIVE all_subtasks (task_id, subtask_id) AS (
  SELECT task_id, subtasks_count
  FROM Tasks
  UNION ALL
  SELECT task_id, subtask_id - 1
  FROM all_subtasks
  WHERE subtask_id > 1
)
SELECT a.task_id, a.subtask_id
FROM all_subtasks a
LEFT JOIN Executed e
  ON e.task_id = a.task_id
 AND e.subtask_id = a.subtask_id
WHERE e.task_id IS NULL                  -- no matching executed row = missing
ORDER BY a.task_id, a.subtask_id;

The column list (task_id, subtask_id) on the CTE gives the counter its real name from the start, so no renaming is needed at the end. The LEFT JOIN plus IS NULL is the classic "find rows with no match" pattern from the Joins notes, and it is safe with NULLs.

Similar Problems

• The Recursive CTE examples in the concept notes (numbers 1 to 5, and the category tree)
• "Users who have never placed an order" (NOT EXISTS, from the Subqueries notes), which uses the same "find what has no match" idea
• Any "find the gaps" problem, like missing dates in a date range or missing invoice numbers

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to find what is missing compared with an expected full list → generate the full list, then remove what exists
• The full list is only described by a count or a range (1 to N) → recursive CTE to generate it
• Need to match a pair of values (task and subtask) → compare both columns together, never one alone
• Writing a recursive CTE → write the stop condition first, and check the boundary (> 1, not > 0)