SQL interview questions

The problem

Fernhill Software, a fictional company, runs a 45 minute SQL screen for every analyst candidate. Tomas, the analytics manager, uses the same six questions each time because each one has a wrong answer that looks right on a small table. You have an interview with him on Thursday. You get his four practice tables and write each answer as a named query, then check it against a test that knows the traps.

The files in data/ are synthetic, generated by make_data.py with a fixed seed: 36 employees, 154 signups, 1,000 login rows and 285 orders. All names and emails are invented.

What you will build from scratch

• second_highest_salary: the second highest distinct value.
• duplicate_emails: rows that are the same person after cleaning.
• three_day_streaks: users active on 3 or more consecutive days (gaps and islands).
• top_two_per_department: top N per group with ties.
• running_total: a cumulative sum with a window function.
• month_over_month: change from the previous month, including a month with no data.

The idea

Each question is one pattern. Learn the pattern and the trap, not the query.

1. Second highest. Remove the maximum, then take the maximum of what is left: MAX(salary) WHERE salary < (SELECT MAX(salary) ...). The trap is ORDER BY salary DESC LIMIT 1 OFFSET 1, which returns the second row, and the second row is the top salary again when two people tie.

2. Duplicates. GROUP BY key HAVING COUNT(*) > 1. The trap is the key. Ana@Example.com and ana@example.com are one person, so group by LOWER(TRIM(email)).

3. Consecutive days. Number each user's distinct days in order. Inside a run of consecutive days, the date goes up by 1 and the row number goes up by 1, so date - row_number is the same for the whole run:

    day        row_number  julianday - row_number
    Oct 3      1           constant A
    Oct 4      2           constant A
    Oct 5      3           constant A
    Oct 9      4           constant B (a new run)
   

   Group by that constant and count. The trap is duplicate days: two logins on Oct 4 add a row number without adding a day, which shifts the constant. Take DISTINCT user_id, login_date first.

4. Top N per group. Rank inside each group with a window function, then filter: DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 2. ROW_NUMBER gives tied people different numbers and drops one at random. RANK skips numbers after a tie (1, 1, 3), so <= 2 returns no second level. DENSE_RANK gives 1, 1, 2. Say out loud which one the question means.

5. Running total. Aggregate to one row per day, then SUM(revenue) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The trap is running the window over order rows: you get one row per order, not per day.

6. Month over month. LAG(revenue) OVER (ORDER BY month) gives the previous row. The trap is that the previous row is not always the previous month. Fernhill had no orders in April 2026, so GROUP BY month has no April row and May is compared to March. Build the full list of months with a recursive CTE, LEFT JOIN the revenue to it, and fill missing months with 0. Divide by NULLIF(prev, 0) so a zero month gives NULL, not an error.

Walk through

Each block below is one query you can run against the four tables. The test file has a load_db() helper that builds them in memory:

python3 -c "import test_lesson as t; con = t.load_db(); print(con.execute('SELECT COUNT(*) FROM orders').fetchall())"

Step 1. The tie that breaks OFFSET.

SELECT salary FROM employees ORDER BY salary DESC LIMIT 3;

It returns 182,000, 182,000 and 175,000. LIMIT 1 OFFSET 1 gives 182,000. The answer is 175,000.

Step 2. Duplicates before and after cleaning.

SELECT
  (SELECT COUNT(*) FROM (SELECT email FROM signups GROUP BY email HAVING COUNT(*) > 1)) AS raw_dups,
  (SELECT COUNT(*) FROM (SELECT LOWER(TRIM(email)) FROM signups GROUP BY 1 HAVING COUNT(*) > 1)) AS clean_dups;

Grouping by the raw email finds 2 duplicated people. Grouping by the cleaned email finds 14. There are 154 signup rows from 140 people.

Step 3. Islands, with and without DISTINCT.

WITH raw AS (
  SELECT user_id, julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM logins
),
dedup AS (
  SELECT user_id, julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM (SELECT DISTINCT user_id, login_date FROM logins)
)
SELECT
  (SELECT COUNT(DISTINCT user_id) FROM (SELECT user_id FROM raw GROUP BY user_id, grp HAVING COUNT(*) >= 3)) AS raw_users,
  (SELECT COUNT(DISTINCT user_id) FROM (SELECT user_id FROM dedup GROUP BY user_id, grp HAVING COUNT(*) >= 3)) AS distinct_users;

Without DISTINCT the islands are wrong in both directions. A repeated day adds a row number without a day, so it can split a real run, or fill a one day gap and join two short runs into a fake one (days 5, 6, 8 and 8 give the same grp to 5, 6 and the second 8). Here 34 users pass the raw version. The right answer is 29.

A second wrong answer counts logins in a 3 day window. User 41 logged in twice on 5 October and twice on 6 October, so the window holds 4 logins and the user looks like a 3 day streak, but it is only 2 days:

SELECT user_id, login_date,
       COUNT(*) OVER (PARTITION BY user_id ORDER BY julianday(login_date)
                      RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS logins_in_3_days
FROM logins
WHERE user_id = 41
ORDER BY login_date;

Step 4. Ranking functions side by side, in the Marketing department where two people share the top salary.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
WHERE department = 'Marketing'
ORDER BY salary DESC
LIMIT 4;

Two people earn 182,000 and Quinn Hart earns 163,000. ROW_NUMBER <= 2 returns the two top earners and no one else. RANK <= 2 does the same, because Quinn's rank is 3. DENSE_RANK <= 2 returns all three. In Finance two people share second place at 162,000, and DENSE_RANK keeps both.

Step 5. The month over month trap.

WITH m AS (SELECT substr(order_date, 1, 7) AS month, ROUND(SUM(amount), 2) AS revenue FROM orders GROUP BY 1)
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM m
WHERE month BETWEEN '2026-03' AND '2026-06';

There is no April row. May (7,801.40) is compared to March (8,973.21) and shows a 13.1% fall. With April filled in as 0, April shows a 100% fall and May has no percentage, because growth from zero is undefined. Both are more honest than a fake month over month number.

The full answers are in solution/solution.sql. Try them yourself first.

Your turn

Open exercise.sql and write the six queries under their markers. Then run, from this folder:

python3 test_lesson.py

To see the reference solution pass: CHECK_SOLUTION=1 python3 test_lesson.py.

For more practice, try the SQL case study.

Check yourself

1. Your second highest salary query must return NULL when everyone earns the same. Does the MAX ... WHERE salary < version do that?
2. When would you use ROW_NUMBER instead of DENSE_RANK for top N per group?
3. Why does julianday(login_date) - ROW_NUMBER() stay constant inside a streak?
4. An interviewer asks for a 7 day rolling sum of revenue. What changes from the running total?

In real job postings

In the Real Analyst Jobs live feed on 25 October 2026, 72% of the 2,102 US analyst postings that list tools ask for SQL, and so do 70% of the 914 India postings that list tools. SQL is the most requested tool in both. Worked answers to more questions are on the data analyst interview questions page.

Next

03: Portfolio project end to end