0Pricing
SQL Interview Prep · Lesson

Full Mock Interview Problem Set

Timed end-to-end problems that combine joins, windows, and CTEs under interview conditions.

Full Mock Interview Problem Set is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

How a SQL Interview Round Flows

This capstone runs you through full mock problems that combine joins, windows, and CTEs under interview conditions. First, the meta-skill: how to behave in the room.

  • Restate the problem and confirm the schema.
  • Clarify edge cases (NULLs, ties, duplicates) before coding.
  • Narrate your approach, then write the query.
  • Test against a tiny sample in your head.

Interviewers grade your process as much as your final query.

The Shared Schema

All problems below use this small e-commerce schema. Read it once so each query makes sense.

  • customers(id, name, country)
  • orders(id, customer_id, order_date, status, amount)
  • order_items(order_id, product_id, quantity)
  • products(id, name, category, price)

Keep this in mind; the rest of the lesson references these tables.

-- orders.status is one of: 'paid','pending','cancelled'
-- amount is the order total in the customer's currency

Problem 1: Top Customers by Spend

"Return the top 3 customers by total paid spend, with their name and total."

Approach: filter to paid orders, aggregate per customer, order, and limit. State that you exclude cancelled and pending orders, an edge case interviewers plant.

SELECT c.name,
       SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY total_spend DESC
LIMIT 3;

Problem 2: Customers Who Never Ordered

"List customers who have never placed an order." This is the anti-join pattern. Two clean solutions: LEFT JOIN with IS NULL, or NOT EXISTS.

Prefer NOT EXISTS because it is NULL-safe (unlike NOT IN). Mention that distinction; it is exactly what the interviewer is fishing for.

-- NULL-safe anti-join
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

Problem 3: Second Highest Order Amount

"Find the second highest distinct order amount." The cleanest, tie-proof solution uses DENSE_RANK so duplicate amounts share a rank.

Edge case to call out: if there is no second distinct value, this returns no rows, which may be acceptable or may need a COALESCE wrapper depending on requirements.

SELECT amount
FROM (
  SELECT amount,
         DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
  FROM orders
) ranked
WHERE rnk = 2;

Problem 4: Latest Order Per Customer

"Return each customer's most recent order." This is the keep-the-latest-row-per-key pattern, solved with ROW_NUMBER partitioned by customer and ordered by date descending.

Add a tiebreaker (order id) so the result is deterministic when two orders share a date, a detail strong candidates include.

SELECT customer_id, id AS order_id, order_date, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, id DESC
         ) AS rn
  FROM orders o
) t
WHERE rn = 1;

Problem 5: Month-over-Month Growth

"Compute monthly paid revenue and its percent change versus the prior month." This combines aggregation in a CTE with LAG.

Step one aggregates by month; step two compares each month to the previous using LAG. Guard the division so the first month (no prior) does not error.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth,
         SUM(amount) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT mth,
       revenue,
       LAG(revenue) OVER (ORDER BY mth) AS prev_revenue,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
         / NULLIF(LAG(revenue) OVER (ORDER BY mth), 0), 2
       ) AS pct_change
FROM monthly
ORDER BY mth;

Problem 6: Top Product Per Category

"For each category, return the best-selling product by total quantity." The top-N-per-group pattern: aggregate, rank within partition, filter to rank 1.

If ties matter, swap ROW_NUMBER for RANK so all co-leaders appear. Naming that choice shows you understand the difference.

WITH sales AS (
  SELECT p.category,
         p.name AS product,
         SUM(oi.quantity) AS qty
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category, p.name
)
SELECT category, product, qty
FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (
           PARTITION BY category ORDER BY qty DESC
         ) AS rn
  FROM sales s
) r
WHERE rn = 1;

Problem 7: Running Total of Revenue

"Show a running (cumulative) total of paid revenue by day." A window SUM with an ordered frame produces the running total without a self-join.

Mention ROWS framing for a true row-by-row cumulative; the default RANGE frame can behave unexpectedly with tied dates.

SELECT order_date,
       SUM(daily) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM (
  SELECT order_date, SUM(amount) AS daily
  FROM orders
  WHERE status = 'paid'
  GROUP BY order_date
) d
ORDER BY order_date;

Problem 8: Consecutive Active Days

"Find users with at least 3 consecutive days containing a paid order." This is a gaps-and-islands twist using the row-number difference trick.

Subtracting a per-user row number from the date yields a constant within a consecutive run, so you group by that constant and count. This is a senior-level signal.

WITH days AS (
  SELECT DISTINCT customer_id, order_date
  FROM orders WHERE status = 'paid'
),
grp AS (
  SELECT customer_id, order_date,
         order_date - (ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY order_date
         ) * INTERVAL '1 day') AS island
  FROM days
)
SELECT customer_id, COUNT(*) AS streak_len
FROM grp
GROUP BY customer_id, island
HAVING COUNT(*) >= 3;

Performance and Common Pitfalls

After a correct query, interviewers ask "how would you make it faster?" and watch for classic traps. Keep a checklist ready:

  • Index the join and filter columns (e.g. orders(customer_id, status)); avoid functions on indexed columns in WHERE.
  • Prefer EXISTS over IN for large anti-joins; NOT IN with a NULL silently returns nothing.
  • Filtering an outer-joined column in WHERE quietly becomes an inner join.
  • Always add a tiebreaker so top-N results are deterministic.
  • Check the EXPLAIN plan for sequential scans on big tables.

Quick Check

You need each customer's single most recent order, and two orders can share the same date.

Recap: Full Mock Interview Set

You worked through the highest-frequency interview problems end to end:

  • Aggregation + LIMIT for top-N spend.
  • Anti-joins with NOT EXISTS (NULL-safe).
  • DENSE_RANK for Nth highest, ROW_NUMBER for latest-per-key and top-per-group.
  • LAG for month-over-month, SUM OVER for running totals.
  • The gaps-and-islands row-number trick for streaks.
  • Close every answer by discussing indexes, EXPLAIN, and common pitfalls.

Frequently asked questions

Is the “Full Mock Interview Problem Set” lesson free?

Yes — the full text of “Full Mock Interview Problem Set” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Full Mock Interview Problem Set”?

Timed end-to-end problems that combine joins, windows, and CTEs under interview conditions. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Full Mock Interview Problem Set” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Normalization Through 3NF
  2. ER Modeling and Relationship Cardinality
  3. Star Schema and Data Warehouse Design
  4. Full Mock Interview Problem Set
← Back to SQL Interview Prep