SQL Interview Prep: Solve Unfamiliar Questions

To solve an unfamiliar SQL interview question, define what one result row represents, write a tiny example with its expected answer, and build the query in stages you can check. This works even when a new schema or metric makes a memorized solution hard to recognize.

Use this sequence on your next practice problem:

1. Confirm the SQL dialect, available tables, and whether you can execute queries.
2. Define what one input row and one output row represent.
3. Write a tiny example with the expected answer before choosing functions.
4. Build intermediate results with a clear purpose and row count.
5. Check duplicates, missing values, date boundaries, and ties.

Find the requirement before the SQL pattern

Start with a sentence such as, “The output is one row per customer, containing their longest qualifying activity streak.” This establishes the output grain: the entity represented by each result row. Then name the grain of each source. An events table might contain many rows per customer per day; an accounts table might contain one row per customer.

Ask questions that change the answer. Does “active” mean any event or a particular event type? Does a streak include weekends? Which timezone defines a day? Should customers with no activity appear? If two streaks have equal length, should you return both or choose one?

When the interviewer leaves a detail open, state a provisional assumption and continue. “I will count calendar days with at least one event, use the supplied date column, and choose the earliest streak on a tie. Please stop me if that differs from the intended result.” An explicit assumption is easier to correct than a hidden one.

Choose operations by what they do to rows

You do not need to recognize a named puzzle immediately. Work out the transformation first:

• Reduce many rows to one per entity: group and aggregate.
• Keep rows and attach related columns: join, after checking how many matches each row can have.
• Check whether a related row exists: consider EXISTS when you do not need the related columns.
• Keep rows and compare their position or neighbors: use a window function with an explicit partition and order.
• Separate dependent calculations: use a common table expression or subquery so the next step consumes an understandable intermediate result.

A window function preserves individual rows while calculating across related rows. A grouped aggregate collapses rows. That distinction, explained in the PostgreSQL window-function tutorial, helps you decide whether the next step needs detail rows or a summary.

Worked example: the longest consecutive-day streak

Original practice exercise: Return each user's longest run of consecutive active calendar days. Multiple events on the same date count as one active day. Exclude events with a missing user or date. Omit users without a valid event. If a user has equally long runs, return the earliest start date.

The following complete PostgreSQL statement includes synthetic input. It reads only the values in the statement and requires no tables or data changes:

WITH events(user_id, activity_day) AS (
    VALUES
        (7, DATE '2026-09-28'),
        (7, DATE '2026-09-29'),
        (7, DATE '2026-09-29'),
        (7, DATE '2026-09-30'),
        (7, DATE '2026-10-02'),
        (7, DATE '2026-10-03'),
        (8, DATE '2026-09-29'),
        (8, DATE '2026-09-30'),
        (8, DATE '2026-10-02'),
        (8, DATE '2026-10-03'),
        (8, NULL::date),
        (NULL::integer, DATE '2026-09-30')
), valid_days AS (
    SELECT DISTINCT user_id, activity_day
    FROM events
    WHERE user_id IS NOT NULL AND activity_day IS NOT NULL
), neighbors AS (
    SELECT user_id, activity_day,
           LAG(activity_day) OVER (
               PARTITION BY user_id ORDER BY activity_day
           ) AS previous_day
    FROM valid_days
), boundaries AS (
    SELECT user_id, activity_day,
           CASE WHEN previous_day IS NULL
                     OR activity_day > previous_day + 1
                THEN 1 ELSE 0 END AS starts_run
    FROM neighbors
), numbered AS (
    SELECT user_id, activity_day,
           SUM(starts_run) OVER (
               PARTITION BY user_id ORDER BY activity_day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS run_id
    FROM boundaries
), runs AS (
    SELECT user_id, MIN(activity_day) AS start_day,
           MAX(activity_day) AS end_day, COUNT(*) AS active_days
    FROM numbered
    GROUP BY user_id, run_id
), ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY user_id ORDER BY active_days DESC, start_day ASC
    ) AS position
    FROM runs
)
SELECT user_id, start_day, end_day, active_days
FROM ranked
WHERE position = 1
ORDER BY user_id;

The expected result is:

user_id | start_day  | end_day    | active_days
7       | 2026-09-28 | 2026-09-30 | 3
8       | 2026-09-29 | 2026-09-30 | 2

Explain why the query works

valid_days changes the grain from events to distinct user-days. Without that step, two events on Tuesday could inflate the length of a run. It also makes the missing-value policy visible.

neighbors orders dates within each user and attaches the previous valid day. boundaries marks the first day and every date more than one day after its predecessor. PostgreSQL date arithmetic lets previous_day + 1 mean the next calendar date; other dialects may require a date-add function.

The cumulative sum in numbered gives every date in a continuous run the same run identifier. Trace user 7 after duplicate and missing-value filtering:

October 2 starts a new run because October 1 is missing. Grouping by user and run produces a three-day streak and a two-day streak; ranking selects the three-day result.

The explicit ROWS frame describes which ordered rows enter the cumulative sum. Deduplication makes each user's date order unique here. Finally, ROW_NUMBER chooses one streak using both length and the agreed tie rule. The outer ORDER BY controls result presentation; ordering inside a window does not establish the final output order.

This is a gaps-and-islands problem: gaps separate runs, and each run is an island. Understanding the boundary rule lets you adapt the query when the definition changes.

Use small counterexamples to test your assumptions

Check cases that could invalidate a stage:

• Empty input: no result rows.
• One valid date: a one-day run with identical start and end.
• Repeated date: the length remains one.
• Two users with interleaved events: their runs remain separate.
• Equal-length runs: the earlier start wins.
• A month, year, or leap-day boundary: adjacent dates remain consecutive.
• Only missing identifiers or dates: no result rows under this contract.

A business-day streak needs a business calendar and a different adjacency rule. A timestamp source needs conversion to the agreed timezone before extracting its date. A request to show every account, including inactive accounts, needs an account population joined to the result. Name these changes before modifying the query.

Check join multiplication before trusting a metric

The same habit applies to ordinary joins. Suppose a customer has two orders and three support tickets. Joining both detail tables directly to that customer can produce six rows. Summing order amounts after that join counts each order three times.

Original practice exercise: Return every customer, including those without orders or tickets, with total spending and ticket count. This complete PostgreSQL statement compares the incorrect detail join with summaries at the required customer grain. All input is synthetic:

WITH customers(customer_id) AS (
    VALUES (7), (8), (9)
), orders(order_id, customer_id, amount) AS (
    VALUES (101, 7, 40::numeric),
           (102, 7, 60::numeric),
           (103, 8, 25::numeric)
), tickets(ticket_id, customer_id) AS (
    VALUES (201, 7), (202, 7), (203, 7)
), joined_summary AS (
    -- Incorrect: joins multiply detail rows before aggregation.
    SELECT c.customer_id, SUM(o.amount) AS spending,
           COUNT(t.ticket_id) AS ticket_count
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    LEFT JOIN tickets t ON t.customer_id = c.customer_id
    GROUP BY c.customer_id
), order_totals AS (
    SELECT customer_id, SUM(amount) AS spending
    FROM orders
    GROUP BY customer_id
), ticket_totals AS (
    SELECT customer_id, COUNT(*) AS ticket_count
    FROM tickets
    GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(j.spending, 0) AS wrong_spending,
       j.ticket_count AS wrong_tickets,
       COALESCE(o.spending, 0) AS correct_spending,
       COALESCE(t.ticket_count, 0) AS correct_tickets
FROM customers c
LEFT JOIN joined_summary j ON j.customer_id = c.customer_id
LEFT JOIN order_totals o ON o.customer_id = c.customer_id
LEFT JOIN ticket_totals t ON t.customer_id = c.customer_id
ORDER BY c.customer_id;

For customer 7, the incorrect join repeats each order for three tickets and each ticket for two orders. The separate summaries each contain at most one row per customer, so their join preserves the required grain. LEFT JOIN keeps customers 8 and 9, and COALESCE supplies zero when a summary is missing.

Do not use SUM(DISTINCT amount) to repair this: two real orders for 50 each still total 100, while that expression returns 50. If you only need to know whether a ticket exists, use EXISTS instead of bringing ticket detail into a spending calculation.

During a live round, say what you expect after each stage: “This intermediate result must contain at most one row per customer. I will check that before joining.” The PostgreSQL table-expression documentation explains joins and grouping in more detail.

Both worked examples use inline values. Their outputs were checked with read-only queries, including duplicate dates, missing dates, tied streaks, equal-priced orders, and customers without activity. When you adapt them to a real schema, check the new assumptions and run your own examples again.

Practice transferring a solution to a changed requirement

Use one session to solve a problem aloud without seeing its category. Then change one requirement: return every longest streak, use business days, require a specific event type, or include inactive users. Explain exactly which stage changes and why. Repeat after a few days with new table and column names.

Use the SQL question collection to rehearse joins, common table expressions, window functions, and running totals. Choose a question, write a miniature dataset, and explain the output grain before reading the answer. Some questions offer free previews; full access follows the site's subscription rules. Judge your practice by whether you can defend the result and handle a changed requirement.

If the main difficulty is freezing while speaking, pair this exercise with the coding interview brain-freeze practice protocol. If execution is unavailable, trace each intermediate result and state that you have not run the query.

Public discussions behind this guide

An August 2026 data-science candidate described regular SQL practice that did not transfer to unfamiliar live questions. A September technical-interview account described difficulty choosing among joins, grouping, and subqueries. These individual accounts motivated the problem-solving approach here; they do not establish a universal interview format. The worked exercise is original and is not presented as either employer's question.