SQL Practice Problems with Solutions

Roughly 10 patterns account for nearly every SQL problem a data engineering interview asks, and they aren't equally common. Aggregation with GROUP BY and HAVING and joins that keep the row count right appear in almost every round, usually as the warm-up. Window functions are the tier that decides the round. That means numbering rows to keep the latest record and ranking within a group, and it also means running totals and LAG for period over period change. When the first 20 minutes went well, an interviewer reaches for gaps and islands or sessionization, and sometimes a pivot.

Spend your time where the patterns stack. Deduplication, top N per group and sessionization are each 1 window function plus 1 filter, so once ROW_NUMBER with a tiebreaker is a reflex, 3 problem families fall at once. Gaps and islands are a running sum over a flag, the same move sessionization makes. Recursive CTEs stand alone and come up less often, but a candidate who can write one in 5 minutes has a visible edge in senior rounds.

A useful order: problems 1 to 4 until they take under 5 minutes each, then 5 to 12 until you can name the window function a statement needs before you start typing, then 13 to 20 at interview pace. Across the DataDriven catalogue, the concept tags on the SQL problems rank the same way. Joins and aggregation are tagged on most problems, and window functions come close behind.

Problem 1: from an orders table with a status and an amount per order, return every customer with at least 2 completed orders, with how many they completed and how much they spent, biggest spender first. Cancelled orders carry a NULL amount.

The filter on status belongs in WHERE, because it removes rows before they are grouped; HAVING filters the groups after COUNT has run. The solution:

Customer 104 has 2 orders but 1 of them was cancelled, so it drops out at the WHERE and never reaches HAVING. Move the status test into HAVING with a conditional count and the query still works, but it has to read every row first. The trap in this family is COUNT(amount) against COUNT(*): COUNT of a column skips NULLs, so without the WHERE clause customer 103 counts 3 orders with COUNT(*) and 2 with COUNT(amount), and the 2 answers look equally plausible until someone checks a cancelled order.

Open the editor under the solution, delete the WHERE line and run again: customer 104 appears with 2 orders, 1 of them cancelled. Then swap COUNT(*) for COUNT(amount) in both places and customer 103's count falls from 3 to 2 while customer 104 drops back out.

Problem 2: return the customers whose average completed order is above the average completed order across all customers. The comparison value is a scalar subquery, and it has to apply the same status filter as the outer query or the 2 averages are taken over different rows:

The overall average is 85.75, so customer 102 at 310 and customer 104 at 99 qualify. AVG ignores NULLs, which is what you want here: a cancelled order with no amount should not pull an average down. Had the cancelled rows carried 0 instead of NULL, the WHERE clause would be doing all the work, and dropping it would change every number. 1 more thing to say out loud in an interview: on an integer column, some engines return an integer average, so cast to a decimal when the column isn't already one.

Problem 5: a device_readings table receives temperature readings, sometimes twice. Return each device's latest reading. 2 readings for device 7 share the same timestamp, and device 8 received the same reading twice.

Number each device's readings newest first and keep row 1. The tiebreaker on reading_id is what makes the answer stable: without it, the engine may pick either of device 7's 2 readings at 09:00, and a query that returns a different row on each run is a bug waiting for a reconciliation report.

Device 8's 2 identical rows also collapse to 1, because ROW_NUMBER numbers them 1 and 2 even though nothing distinguishes them. That's the difference from RANK, which gives both rows 1 and returns device 8 twice. The GROUP BY device_id with MAX(reading_ts) version looks shorter, but the temperature then comes from no particular row, and a join back to the table on the max timestamp returns both of device 7's rows again. Whenever a statement asks for the latest or first row per key, reach for ROW_NUMBER with a complete ORDER BY.

Problem 6: a contacts table has the same email entered more than once with different letter case. Return each duplicated email, how many copies it has and the id of the row to keep, taking the oldest.

Normalise before you group: LOWER(email) treats ada@example.com and ADA@example.com as 1 email, which is what the business means by a duplicate. MIN(contact_id) stands in for the oldest row only when ids follow insertion order; when they might not, number the rows by created_at instead. The delete that follows keeps 1 row per email by excluding every id in that keep list, and NOT IN is safe there because MIN never returns NULL for a group that has rows.

The same move on a live problem: every service deploys versions over time, and the statement asks for the latest version of each. Write it here:

Problem 7: from a table of revenue per product, return the top 2 products in each category by revenue, keeping ties, so a category with 2 products tied for second returns 3 rows.

The 3 ranking functions differ only on ties, and the statement tells you which one it wants. Keeping ties and filling the next position without a gap is DENSE_RANK:

The audio category has speaker and earbuds tied at 700. ROW_NUMBER would number them 2 and 3 and drop one of them arbitrarily, which breaks the statement. RANK gives both 2 and the microphone 4, which happens to give the same top 2 here, but ask for the top 3 and RANK returns nothing at position 3 while DENSE_RANK returns the microphone. Say the tie rule back to the interviewer before you pick a function; it's the whole question.

The 3 functions on the same rows show the whole difference in 1 result. The functions agree until the tie at 700 in audio: ROW_NUMBER splits it 2 and 3 in an order the engine chooses, RANK gives both rows 2 and jumps to 4, and DENSE_RANK gives both rows 2 and continues at 3. In the editor, wrap the query in a CTE and filter each column with <= 3 in turn to see which function returns the microphone at position 3:

Problem 8: return the second highest distinct salary in each department, and nothing for a department that has only 1 distinct salary. DENSE_RANK on the values again, with a DISTINCT in the final select because 2 employees can share the second highest salary:

The data department has 2 people on 150000, so ROW_NUMBER() = 2 would return 150000, which is the highest salary and not the second. Ops has 2 people on the same salary and returns no row, which is correct for this statement; if the interviewer wants a NULL for such a department, start from a list of departments and LEFT JOIN the ranked rows to it. The single table version of this problem, the second highest salary overall, is a LIMIT 1 OFFSET 1 over the distinct values, and it fails the same way when the top salary is shared.

A live problem with the same shape, ranked within time periods instead of categories: keep the top 3 items in each month.

Problem 9: a daily_sales table has 1 row per store per day, except that on 1 day the north store's 2 registers reported separately, and on another day it was closed. Return each store's running total by day.

A running total is SUM over a window ordered by date. The detail that fails first attempts is the default frame. When a window has an ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in a RANGE frame every row with the same ORDER BY value is a peer of the current row, so both of north's rows on the 2nd get the same running total of 200 instead of 150 and then 200. The SQLite window function reference states that default. PostgreSQL shares it, as do Snowflake and BigQuery. Aggregate to 1 row per day first and declare a ROWS frame:

Collapsing to 1 row per day also answers the question the business asked, since a running total by day has 1 value per day. Keep the explicit ROWS frame anyway: it costs nothing, it documents the intent, and on engines where RANGE over a date needs an interval it avoids an error.

The default frame and an explicit ROWS frame on the raw rows, in 1 query: on north's 2 rows for the 2nd, default_frame reads 200 on both while rows_frame reads 150 and then 200. In the editor, add a third SUM with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to watch a 3 row window skip the closed day on the 4th:

Problem 10: return each store's 3 day moving average, counting a day the store was closed as 0 sales rather than skipping it. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the 3 most recent rows, and a missing day isn't a row, so the window silently stretches to the 3 most recent days that had sales. The fix is a date spine: every store crossed with every calendar day, then the sales joined on:

North was closed on the 4th. With the spine, the 5th averages 80 and 120 with a 0 for the closed day, giving 66.7; without it, the window reaches back to the 2nd and puts that day's 100 where the 0 should be, giving 100, which hides the closed day entirely. Warehouses keep a date dimension for exactly this; when you have none, generate the days with a recursive CTE or the engine's series function. The SQL window functions interview questions go further into frames, and the SQL window functions practice page drills them.

Problem 11: a monthly_revenue table has 1 row per region per month. Return each month's change from the previous month in absolute terms and as a percentage. 1 region has no row for March, and another recorded 0 in February.

LAG reads the previous row in the window's order, which is the previous month only when no month is missing. Carry the previous month's date along with its revenue and test whether it really is 1 month back, and guard the division with NULLIF so a 0 month returns NULL instead of an error:

For apac, April's LAG row is February, so the change of 110 is over 2 months and the row says so. Interviewers ask what the percentage should be when the previous month was 0, and there's no right number: a NULL with a note beats a fake 100%. The first row of every region has no previous month and keeps a NULL change; do not COALESCE it to 0, which would read as flat growth.

Problem 12: from an orders table, return the number of days between each order and the customer's previous order, with NULL on the first. A tiebreaker on order_id keeps 2 orders on the same day in a fixed order so the second shows 0 days:

The date difference function is the 1 line of this query that changes by engine. Snowflake and SQL Server use DATEDIFF with the unit first. BigQuery and Trino both spell it DATE_DIFF, with different argument orders, and PostgreSQL just subtracts 2 dates. Know your target's form before the interview and say it when you write it.

A live version with a window on the gap: the retention team wants the customers who bought again within a set number of days.

Problem 13: a job_runs table records 1 row per run with a status. For each job, return its longest streak of consecutive days with a successful run. 1 day has 2 successful runs, and 1 day has none.

Gaps and islands is the pattern behind every streak question. Number the successful days per job in date order and subtract the row number from the date: inside an unbroken run both rise by 1 a day, so the difference stays constant, and after a gap the date jumps while the number does not. That difference is the island's anchor, and grouping by it gives 1 row per streak:

The DISTINCT in the first step is the trap. The nightly load ran twice on the 6th, and without DISTINCT those 2 rows get 2 row numbers for 1 date. The difference shifts, so the 3 day streak splits in 2. Ties are kept on purpose: the hourly sync has 2 streaks of 1 day and both come back. When runs happen on weekdays only, consecutive means consecutive rows rather than consecutive dates, and the anchor becomes the difference between 2 row numbers, 1 over all days and 1 over successful days.

Problem 14: a subscriptions table has start and end dates, and 1 customer's subscriptions overlap or touch, with one sitting inside another. Merge them into continuous periods of coverage. This is gaps and islands over ranges rather than points: a new period starts when a row begins after the latest end date seen so far, and a running sum of those starts numbers the periods:

The running MAX of the end date is what makes it correct when a short subscription sits inside a longer one. Customer 2's third subscription starts on the 9th, after the inner subscription ended on the 7th but while the first still runs to the 10th; compared with only the previous row's end it would open a new period, and compared with the running MAX it merges. Customer 1's third subscription starts the day after the second ends, and the test treats touching ranges as 1 period; change the DATE_ADD to a plain comparison if the business counts a 1 day gap as a break.

In the editor, replace the running MAX with LAG(end_date) over the same partition and order, then run again: customer 2 splits into 2 periods, because its third subscription is compared with only the row before it. Change the DATE_ADD day count from 1 to 0 and touching ranges stop merging, so customer 1 gains a third period.

Problem 17: a tickets table records the hours each ticket took to close, along with its team and priority. Return 1 row per team with the ticket counts per priority level, high through low, in 3 columns, and the share of tickets that were high priority.

A pivot in portable SQL is a CASE inside an aggregate, 1 per output column. The share is an AVG over a CASE that returns 1 or 0, and the ELSE 0 is the whole problem:

Leave the ELSE out and the CASE returns NULL for every ticket that is not high priority. AVG ignores NULLs, so the platform team's share reads 100% instead of 50%, and the number looks fine until someone compares it with the counts beside it. The same omission in a SUM is harmless, which is why people carry the habit from SUM to AVG. Snowflake has a PIVOT keyword that does the same work with fixed column names, and so do BigQuery and SQL Server; the CASE form is what you can write in any interview without checking the docs.

Problem 18: return each team's total hours to close and that total as a percentage of all hours. A window SUM with an empty OVER clause reads the grand total on every row, so the percentage needs no self join:

Multiply by 100.0 before you divide. On an integer column in PostgreSQL or SQL Server, 52 / 130 is 0, which makes every percentage in the result 0 too. The decimal literal promotes the arithmetic. BigQuery and Snowflake divide integers into decimals and hide the problem until the query is ported, so write the literal in every engine.

Problem 19: an employees table has a manager_id that points at another employee, and 1 employee reports to nobody. Return every employee with their level in the hierarchy and the chain of names from the top.

A recursive CTE has an anchor query, the rows with no manager, and a recursive query that joins the CTE's previous result to the table to find the next level, repeated until a level adds no rows. Each level appends its name to the path and adds 1 to the level:

A cycle in the data, 2 managers who report to each other through a data entry mistake, makes the recursion run forever on engines that don't detect it. The WHERE c.level < 10 guard caps the depth, so an outage becomes a wrong row you can find. String concatenation is the other line that changes by engine: PostgreSQL and SQLite use ||, as does Snowflake, while BigQuery and MySQL use CONCAT.

In the editor, change the depth guard from 10 to 2 and the 2 deepest levels disappear. Replace the anchor's IS NULL test with employee_id = 2 and the chain starts at Bo, which is how you answer the follow-up on 1 manager's whole team.

Problem 20: for every employee, return how many people report to them directly and how many in total, including reports of reports. Build the transitive closure, every ancestor paired with every descendant and the depth between them, then count it per ancestor. A LEFT JOIN from the employees keeps the ones with no reports at 0: