Skip to content
QueryLab

SQL interview questions

These are the shapes interviewers actually ask for: a top row per group, a duplicate, a retention definition, a running total, and a rank that respects history. Each one links to a dataset you can query.

How to answer out loud

State the grain first. 'One row per country, the customer with the highest spend.' Then name the filter that defines spend, then the window or aggregate. Interviewers are listening for cancelled orders and historical salaries as much as for the function name.

What QueryLab will not do for you

The reference query is hidden until you reveal it. Write your own, check the result, and use the explanation to see which predicate carried the business rule.

Example

WITH ranked AS (
  SELECT country, name, spend,
         RANK() OVER (PARTITION BY country ORDER BY spend DESC) AS rnk
  FROM customer_spend
)
SELECT country, name, spend
FROM ranked
WHERE rnk = 1;

Partition restarts the rank. The outer query keeps the winners.

Practice

Questions

  • MediumWindow Functions

    Return the top customer by spend in each country.

    Aggregate spend at the customer grain, then RANK() OVER (PARTITION BY country ORDER BY spend DESC) and keep rank 1. A single ORDER BY LIMIT cannot do this for every country at once.

    Practice this interactively
  • MediumGROUP BY

    Find email addresses captured more than once.

    Group by the column that should be unique and keep groups with HAVING COUNT(*) > 1. That is the whole pattern. The interesting part is agreeing what a duplicate is: the email, not the lead id.

    Practice this interactively
  • HardDate functions

    Which users were active in their signup month and the next month?

    Build the signup month and the following month, then require an event in each. EXISTS is enough. Do not count events, or a user with two events in month one looks retained.

    Practice this interactively
  • HardWindow Functions

    Show a running total of cash collected.

    Filter to succeeded payments, then SUM(amount) OVER (ORDER BY paid_at, id). The extra id makes the order stable when two payments share a timestamp.

    Practice this interactively
  • HardWindow Functions

    Rank employees by current salary.

    Reduce salary history to the latest effective date first. Rank that set. Ranking first will score obsolete contracts.

    Practice this interactively
  • MediumSubqueries

    Who is paid above the average current salary?

    The benchmark has to use the same definition of current as the rows you compare. Average the latest salary per person, then keep people strictly above it.

    Practice this interactively

Common mistakes

  • Using LIMIT 1 after a global sort and calling it top-N per group.
  • Counting a cancelled order as revenue.
  • Defining retention as 'any later event' when the prompt said the next calendar month.
  • Ranking a person twice because old salaries were left in the set.

FAQ

Are these the exact questions a company will ask?
They are the standard shapes. Interviewers change the table names. If you can explain the grain and the filter, you can retarget the query.
Should I memorize the solution SQL?
Memorize the pattern and the mistakes. The solution on each exercise is a reference, and it stays hidden until you ask for it.

Related practice

Start solving SQL challenges today.

Open Top customer in each country and check the result in the browser. An account is optional.

Start practicing free