Expert: design, speed and engines · Interview questions and practice projects · topic 59 of 62 · 40 min
Classic interview queries, part 2
Explanation
Running totals
A running total adds each row to the sum of the rows before it. A window SUM with ORDER BY does exactly that. Here each order's value is added to the total so far (cancelled orders left out):
SELECT o.id, o.order_date, SUM(oi.qty * oi.price) AS total,
SUM(SUM(oi.qty * oi.price)) OVER (ORDER BY o.order_date) AS running_total
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'Cancelled'
GROUP BY o.id
ORDER BY o.order_date;
-- 101 | 2025-01-05 | 1780 | 1780
-- 102 | 2025-01-09 | 1999 | 3779
-- ...
-- 108 | 2025-04-02 | 650 | 13844Consecutive days: gaps and islands
An island is a run of consecutive days; a gap is where the run breaks. The trick: number each user's login days 1, 2, 3... and subtract that number of days from the date. Within a run, date and number both go up by one, so the result stays the same. That constant is a group key for the streak.
| login_date (user 1) | row number | date − row number |
|---|---|---|
| 2025-03-01 | 1 | 2025-02-28 |
| 2025-03-02 | 2 | 2025-02-28 |
| 2025-03-03 | 3 | 2025-02-28 |
| 2025-03-05 | 4 | 2025-03-01 |
| 2025-03-06 | 5 | 2025-03-01 |
WITH days AS (SELECT DISTINCT user_id, login_date FROM logins),
grp AS (
SELECT user_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS anchor
FROM days)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM grp
GROUP BY user_id, anchor;Month-over-month growth
LAG(x) reads x from the previous row in the window. Growth is (this month − last month) / last month × 100. Multiply by 100.0, not 100, so SQLite does not do integer division.
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.qty * oi.price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'Cancelled'
GROUP BY month)
SELECT month, revenue,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
/ LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly ORDER BY month;
-- 2025-01 | 3779 | NULL
-- 2025-02 | 2248 | -40.5
-- 2025-03 | 7167 | 218.8
-- 2025-04 | 650 | -90.9Bought A but not B; bought in every month
"Customers who bought the Cotton Kurta but never the Bluetooth Earbuds" is a set difference: the buyers of A minus the buyers of B. Write it with IN and NOT IN (safe here because customer_id has no NULLs), NOT EXISTS, or EXCEPT. "Customers who bought in every month" compares counts: a customer qualifies when their number of distinct order months equals the number of months in the data. In SHOP nobody qualifies: Asha ordered in January, February and April, but not in March.
SELECT c.name
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT strftime('%Y-%m', o.order_date))
= (SELECT COUNT(DISTINCT strftime('%Y-%m', order_date)) FROM orders);
-- (no rows)Median with window functions
The median is the middle value after sorting. SQLite and MySQL have no MEDIAN function; PostgreSQL has PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary). A portable way numbers the rows and averages the one or two middle rows:
WITH s AS (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS n
FROM employees)
SELECT AVG(salary) AS median
FROM s
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);
-- 95000.0 (11 salaries: the 6th one)Pivot with CASE
A pivot turns values in rows into columns, like a spreadsheet pivot table. Put a CASE inside SUM for each column you want:
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders GROUP BY month ORDER BY month;
-- 2025-01 | 2 | 2 | 0
-- 2025-02 | 2 | 1 | 1
-- 2025-03 | 3 | 1 | 0
-- 2025-04 | 1 | 0 | 0Common mistakes
- Forgetting DISTINCT before a streak query, so two logins on one day split the streak.
- Integer division in growth: (a − b) * 100 / b drops decimals in SQLite and PostgreSQL. Use 100.0.
- Dividing by a LAG value of 0. Wrap it with NULLIF(LAG(...), 0).
- Using NOT IN when the subquery can return NULL. Prefer NOT EXISTS.
- Running totals without ORDER BY in the window: then every row shows the grand total.
Need help?
Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.
Try:
Watch the lesson
Classic interview queries, part 2 · 1/7
Part two of the interview classics: sequences, comparisons and reshaping data.
Prefer a video by a teacher? Find free videos on “sql gaps and islands consecutive days lag month over month growth interview” on YouTube ↗
In real life: Daily streaks in a learning app
Language and coding apps reward "7-day streaks". Behind the badge is a gaps-and-islands query over a logins or activity table, run per user. The same pattern finds consecutive months a customer paid an EMI, days a server stayed healthy, or runs of failed UPI payments that should raise an alert.
WITH days AS (SELECT DISTINCT user_id, login_date FROM logins),
grp AS (SELECT user_id, login_date,
julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS g
FROM days),
streaks AS (SELECT user_id, COUNT(*) AS days FROM grp GROUP BY user_id, g)
SELECT user_id, MAX(days) AS longest_streak FROM streaks GROUP BY user_id;Practice (0/4 done)
Find login streaks. The logins table may have several logins per user on the same day. Return user_id, streak_start, streak_end and days (the number of consecutive days) for every streak of 2 or more days, ordered by user_id and then streak_start.
Month-over-month growth on SHOP. Revenue is SUM(qty * price) over orders that are not 'Cancelled', per month (strftime('%Y-%m', order_date)). Return month, revenue, prev_revenue (last month's revenue, NULL for the first month) and growth_pct = (revenue − prev_revenue) × 100.0 / prev_revenue rounded to 1 decimal, ordered by month.
Which customers bought the Cotton Kurta (product 5) but never bought the Bluetooth Earbuds (product 7)? Count any order status. Return id and name.
Pivot order statuses by month. From orders, return month (strftime('%Y-%m', order_date)), orders (all orders that month), delivered (how many have status 'Delivered') and cancelled (how many 'Cancelled'), ordered by month.
Quick quiz
Read more (free)
Related help
Before this, learn
This topic builds on these. If something here feels new, open them first.
Related topics
- Theory questions you will be askedInterview questions and practice projects · same module
- Project: a railway booking database (IRCTC-like)Interview questions and practice projects · same module
- Grouping, joining and reshapingData Science, Machine Learning and AI · Cleaning, exploring and visualising data
- Merge sort and quick sortData Structures and Algorithms (DSA) for interviews · Sorting and searching
- Running totals, moving averages and framesWindow functions
- OVER and PARTITION BYWindow functions
Questions people ask about this
Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.
In the gaps-and-islands trick, what stays constant inside a streak of consecutive dates?
Answer: The date minus the row number
Both the date and the row number rise by one per row, so their difference stays the same.
Which window function reads a value from the previous row?
Answer: LAG
LAG looks back; LEAD looks forward.
Revenue went from 2248 to 7167. In SQLite, what does (7167 - 2248) * 100 / 2248 return?
Answer: 218
All integers, so the fraction is dropped. Use 100.0 to get 218.8...
How do you write a running total of amount by date?
Answer: SUM(amount) OVER (ORDER BY date)
With ORDER BY, the window grows row by row; without it, every row gets the grand total.
Which SQL turns statuses in rows into delivered and cancelled columns?
Answer: SUM(CASE WHEN status = ... THEN 1 ELSE 0 END) per column
Conditional aggregation with CASE is the portable pivot.
You compute days between orders with LAG(order_date) OVER (ORDER BY order_date), without PARTITION BY. What goes wrong?Related
Answer: Each customer's first order gets compared with another customer's order
Partition by customer_id so the previous row is always the same customer.
Which tool makes an Excel-style grid with cities as rows and months as columns?Related
Answer: pivot_table
pivot_table reshapes long data into a grid of summaries.
What does a median-of-three pivot do?Related
Answer: Takes the middle value of the first, middle and last items, avoiding the worst case on sorted input
On sorted input the median of three is the true middle, so the split is balanced.
Why group months by strftime('%Y-%m', order_date) rather than by month names like "Mar 2025"?Related
Answer: 'YYYY-MM' text sorts in time order; month names sort alphabetically
Format month names only for display, after sorting.
Pass the quiz (60% or more) or mark this topic as done when you are ready.
Your progress is saved on this device only.
Next steps
- 3 more topics in “Interview questions and practice projects” before its exam: Theory questions you will be asked, Project: a railway booking database (IRCTC-like), Project: e-commerce analytics, and your next steps.
- Module 12 exam: interview questions and projects · 13 questions · 25 min, without help. 5 topics of this module still to finish first.
- 62 topics left in the course before the final exam.