10 SQL Interview Questions for Experienced Candidates: Checked PostgreSQL Examples

SQL interview questions for experienced candidates become interesting at the edges: duplicate salaries, tied timestamps, missing dates, empty sets, and a plan that changes with data volume. Begin each answer by defining the output grain and the rules for ties, NULLs, and time. Then demonstrate those rules on data small enough to recount.

These ten questions use original synthetic fixtures rather than reproducing solutions to the linked interview prompts. Each SQL block is a complete PostgreSQL query with its own VALUES data. The eleven queries and their stated results were checked on PostgreSQL 17.11 in an isolated local database. These tiny fixtures demonstrate semantics only; they make no production performance claims. Other engines require dialect-specific changes.

1. How do you return the Nth highest distinct salary?

First decide whether N counts distinct salary levels or individual employees. Here it counts distinct non-null values, and a missing level produces one row containing NULL.

WITH employees(salary) AS (
    VALUES (120), (120), (100), (80), (NULL)
), ranked AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT MAX(salary) AS second_distinct_salary
FROM ranked
WHERE salary_rank = 2;

The result is 100. Changing the final rank to 4 produces NULL. The outer aggregate deliberately collapses tied rows and still returns a row when no rank matches. A plain filtered ranking query would return duplicate salary rows or no row, which is a different contract.

ROW_NUMBER answers a row-selection question; it does not automatically express distinct salary levels. Practice the public prompt on ROW_NUMBER, RANK, and DENSE_RANK for top-K results. For a parameterized version, also define the behavior for a non-positive or missing N.

2. Why do running totals change when timestamps tie?

A frame controls which rows contribute to an aggregate for the current row. This fixture contrasts a default ordered frame with an explicit row frame and a deterministic tiebreaker:

WITH sales(id, sale_date, amount) AS (
    VALUES (1, DATE '2026-01-01', 10),
           (2, DATE '2026-01-01', 20),
           (3, DATE '2026-01-03', 30)
)
SELECT id,
       SUM(amount) OVER (
           ORDER BY sale_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS row_total,
       SUM(amount) OVER (ORDER BY sale_date) AS peer_total,
       AVG(amount) OVER (
           ORDER BY sale_date, id
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS two_row_average
FROM sales
ORDER BY sale_date, id;

The tied January 1 rows are peers in the second aggregate. The two-row average at id 3 includes January 1 and January 3, so it is not a two-calendar-day average. Likewise, seven observed rows are not necessarily seven days. A calendar metric needs an explicit date range or date spine, plus a rule for missing days.

The PostgreSQL window tutorial documents frame behavior and filtering window results in an outer query. Continue with the internal SQL window-functions guide and running-balance exercises.

3. How do you find consecutive-day islands without counting duplicates?

Choose one row per user per calendar date before numbering. For consecutive dates, subtracting the row number gives a constant grouping key:

WITH logins(user_id, login_date) AS (
    VALUES (1, DATE '2026-01-01'),
           (1, DATE '2026-01-01'),
           (1, DATE '2026-01-02'),
           (1, DATE '2026-01-04')
), daily AS (
    SELECT DISTINCT user_id, login_date FROM logins
), numbered AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY login_date
           )::integer AS rn
    FROM daily
)
SELECT user_id, MIN(login_date) AS first_day,
       MAX(login_date) AS last_day, COUNT(*) AS days
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, first_day;

User 1 has January 1–2 with length 2, then January 4 with length 1. The duplicate January 1 login does not lengthen or split a streak.

This query uses PostgreSQL date-minus-integer arithmetic. For timestamp inputs, first agree on the calendar timezone and day boundary. Missing observations do not establish that a service was healthy or unhealthy; an uptime question needs a separate rule for unknown intervals.

4. How do you make a recursive query terminate on a cycle?

Define what the result represents. This query returns reachable node IDs, not every path or a depth for each visit:

WITH RECURSIVE edges(parent_id, child_id) AS (
    VALUES (1, 2), (2, 3), (3, 1), (1, 4)
), reachable(id) AS (
    VALUES (1)
    UNION
    SELECT e.child_id
    FROM edges e
    JOIN reachable r ON e.parent_id = r.id
)
SELECT id
FROM reachable
ORDER BY id;

The result is 1, 2, 3, 4, and the cycle back to 1 terminates. Here the recursive result has only one column, so deduplicating complete rows also deduplicates visited nodes. Adding a changing depth or path column would change row identity; UNION alone would then no longer establish the same termination argument.

For path enumeration, define explicit cycle detection and the meaning of a depth limit. A limit can truncate a valid hierarchy; it does not prove that all descendants were visited. PostgreSQL's recursive-query documentation explains recursive UNION evaluation and cycle-handling options. Do not assume deduplication always uses a sort or that every recursive query should use UNION ALL.

5. How do you sessionize events at an exact inactivity boundary?

For this example, a new session starts only when inactivity is greater than 30 minutes. Exactly 30 minutes remains in the same session. Event IDs break timestamp ties.

WITH events(user_id, event_id, event_time) AS (
    VALUES (1, 1, TIMESTAMP '2026-01-01 10:00'),
           (1, 2, TIMESTAMP '2026-01-01 10:30'),
           (1, 3, TIMESTAMP '2026-01-01 11:01'),
           (1, 4, TIMESTAMP '2026-01-01 11:01'),
           (2, 5, TIMESTAMP '2026-01-01 10:05')
), previous AS (
    SELECT *, LAG(event_time) OVER (
        PARTITION BY user_id ORDER BY event_time, event_id
    ) AS previous_time
    FROM events
), starts AS (
    SELECT *, CASE
        WHEN previous_time IS NULL
          OR event_time - previous_time > INTERVAL '30 minutes'
        THEN 1 ELSE 0 END AS new_session
    FROM previous
)
SELECT user_id, event_id,
       SUM(new_session) OVER (
           PARTITION BY user_id ORDER BY event_time, event_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM starts
ORDER BY user_id, event_time, event_id;

User 1's event IDs 1–4 receive session IDs 1, 1, 2, 2. User 2's event 5 receives session 1. These are per-user identifiers; the composite of user and session identifies a session in the output.

The fixture already has unique event IDs and a single time basis. In an ingestion pipeline, decide how retries are deduplicated and how late events change previously assigned sessions. A late event can bridge a gap, so recomputing a fixed partition without surrounding context can disagree with a complete-history calculation. The timeout is a product rule, not an inherent property of SQL.

6. Which median do you mean for an even-sized group?

A continuous percentile can interpolate; a discrete percentile chooses an observed value. Make that choice explicit:

WITH salaries(value) AS (
    VALUES (10::numeric), (20), (30), (40), (NULL)
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (
           ORDER BY value
       ) AS continuous_median,
       PERCENTILE_DISC(0.5) WITHIN GROUP (
           ORDER BY value
       ) AS discrete_median
FROM salaries;

The results are 25 and 20. Adding 50 gives 30 for both. These ordered-set aggregates ignore null input values; replacing missing values with zero would change the population being measured.

Consult PostgreSQL's aggregate-function reference for supported input types and semantics. A function with the same name in another database may require a different query shape. Use the median and percentile interview exercises to compare even counts, ties, and dialect differences.

7. How do you keep a cohort-retention denominator fixed?

This fixture measures activity in the calendar month after signup, with February and March assumed completely observed. It is a month-offset metric, not activity exactly 30 days after signup.

WITH users(user_id, signup_date) AS (
    VALUES (1, DATE '2026-01-15'),
           (2, DATE '2026-01-20'),
           (3, DATE '2026-02-05')
), activity(user_id, activity_date) AS (
    VALUES (1, DATE '2026-02-01'),
           (1, DATE '2026-02-02'),
           (1, DATE '2026-03-01'),
           (3, DATE '2026-03-05')
), cohorts AS (
    SELECT user_id,
           DATE_TRUNC('month', signup_date)::date AS cohort_month
    FROM users
), sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohorts GROUP BY cohort_month
), active_months AS (
    SELECT DISTINCT user_id,
           DATE_TRUNC('month', activity_date)::date AS activity_month
    FROM activity
)
SELECT s.cohort_month, s.cohort_size,
       COUNT(DISTINCT c.user_id) FILTER (
           WHERE a.activity_month = s.cohort_month + INTERVAL '1 month'
       ) AS retained_users,
       ROUND(100.0 * COUNT(DISTINCT c.user_id) FILTER (
           WHERE a.activity_month = s.cohort_month + INTERVAL '1 month'
       ) / s.cohort_size, 1) AS retention_pct
FROM sizes s
JOIN cohorts c USING (cohort_month)
LEFT JOIN active_months a USING (user_id)
GROUP BY s.cohort_month, s.cohort_size
ORDER BY s.cohort_month;

January has 2 signups, 1 retained user, and 50.0% month-one retention. February has 1 signup, 1 retained user, and 100.0%. User 1's two February activity dates do not create two retained users. Users without activity remain in the denominator.

In real data, specify the qualifying event and observation cutoff, and distinguish an unobserved future period from observed zero activity. Normalize timestamps to the agreed calendar before bucketing them. Compare this monthly definition with the public D7-retention window-function prompt; do not assume the two metrics use the same denominator or time interval.

8. How do you return up to N rows per group with stable ties?

Here the contract is up to two products per country, ignoring null revenue. Product IDs are unique within a country and resolve equal revenues deterministically.

WITH sales(country, product_id, revenue) AS (
    VALUES ('US', 'A', 100), ('US', 'B', 100),
           ('US', 'C', 90), ('US', 'D', 80),
           ('US', 'E', NULL),
           ('GB', 'X', 50), ('GB', 'Y', 40),
           ('CA', 'Z', 70)
), ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY country ORDER BY revenue DESC, product_id
    ) AS rn
    FROM sales
    WHERE revenue IS NOT NULL
)
SELECT country, product_id, revenue
FROM ranked WHERE rn <= 2
ORDER BY country, rn;

The output contains CA: Z; GB: X and Y; US: A and B. A group with one eligible product returns one row, not two. To return all products in the top two distinct revenue levels instead, use DENSE_RANK over revenue alone; a product-ID tiebreaker inside that ranking would change the intended ties.

Follow up with ranking top products per category. An indexed per-group lookup and a global window can perform differently depending on selectivity and group counts. Neither shape wins for every dataset.

9. What does buying every required product mean when the set is empty?

Choose the customer universe and the empty-set rule first. This query means there is no required product that a customer lacks:

WITH customers(customer_id) AS (
    VALUES (1), (2), (3), (4)
), products(product_id, category) AS (
    VALUES ('A', 'bundle'), ('B', 'bundle'), ('X', 'other')
), purchases(customer_id, product_id) AS (
    VALUES (1, 'A'), (1, 'A'), (1, 'B'),
           (2, 'A'), (3, 'X')
), required AS (
    SELECT product_id FROM products WHERE category = 'bundle'
)
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM required r
    WHERE NOT EXISTS (
        SELECT 1 FROM purchases p
        WHERE p.customer_id = c.customer_id
          AND p.product_id = r.product_id
    )
)
ORDER BY c.customer_id;

Only customer 1 qualifies. Its repeated purchase of A cannot substitute for buying B. If the required category is changed to an absent category, all four customers qualify under this stated rule, including customer 4 with no purchases.

A count-based alternative needs the same universe, required-product filtering, duplicate handling, and empty-set policy to be equivalent. Starting only from customers who appear in purchases can silently lose eligible customers in the empty-set case. If the product requirement is instead that an empty bundle qualifies nobody, encode that condition explicitly.

10. How do you compare a slow-query rewrite without changing its result?

Begin with semantics. This original query includes West-region customers even when they have no orders:

WITH customers(customer_id, region) AS (
    VALUES (1, 'West'), (2, 'West'), (3, 'East')
), orders(order_id, customer_id) AS (
    VALUES (101, 1), (102, 1), (103, 3)
)
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c
WHERE c.region = 'West'
ORDER BY c.customer_id;

A grouped rewrite is:

WITH customers(customer_id, region) AS (
    VALUES (1, 'West'), (2, 'West'), (3, 'East')
), orders(order_id, customer_id) AS (
    VALUES (101, 1), (102, 1), (103, 3)
), counts AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(o.order_count, 0) AS order_count
FROM customers c
LEFT JOIN counts o USING (customer_id)
WHERE c.region = 'West'
ORDER BY c.customer_id;

Both return (1, 2) and (2, 0). The left join and zero substitution preserve the customer with no orders. Compare result multiplicities as well as values; ordinary set equality can hide duplicate-row differences.

On an appropriate test dataset, prefix either complete query with EXPLAIN (ANALYZE, BUFFERS) to inspect actual execution. This command executes the query; it is not just a plan estimate. Look at estimated versus actual rows, loops, filtering, buffers, and total work. Read PostgreSQL's EXPLAIN guide when interpreting those fields.

The tiny fixtures do not establish which query is faster at scale. A selective customer filter may favor indexed lookups, while grouping every order may do unnecessary work. Test representative cardinalities and distributions, relevant indexes, and current statistics. A sequential scan is not automatically a problem, and a visually shorter plan is not evidence of improvement. The PostgreSQL interview guide develops the transaction, index, and plan context.

Build a practice loop around counterexamples

For each query, write the output grain, tie rule, NULL rule, and time rule before running it. Then change one assumption: remove a salary level, repeat an activity event, introduce a cycle, or empty the required set. Explain why the result changes, rather than memorizing a window-function name.

Choose a senior SQL and data practice question and establish its own contract before applying a pattern from this article. The linked questions are separate exercises with their own requirements.

For other engines, Microsoft's execution-plan documentation and intelligent query processing reference provide SQL Server-specific context; PostgreSQL syntax and operator behavior should not be assumed portable. Background career references include the 2025 Stack Overflow survey, its press summary, and the BLS computer and IT skills page. Survey participation and occupational projections do not establish that any particular interview asks these questions.

Keep practicing

• Compare ranking functions for top-K results
• Write window functions for D7 retention
• SQL window functions and frame clauses
• PostgreSQL: indexes, plans, and transactions