SQL · hard · Asked at Google

Find users who logged in 3 or more consecutive days.

Asked in Data Engineer interviews, in the SQL round.

Short answer

Use ROW_NUMBER and date - row_number as a group key, then count per group.

How to answer it

The gaps-and-islands trick. Number each user's distinct login days in order; subtract that number from the date. Consecutive days produce the same difference, so the difference is a group key for each run.

WITH days AS (
  SELECT DISTINCT user_id, login_date
  FROM logins
),
numbered AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_days
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Walk one example aloud: logins on the 1st, 2nd, 3rd, 7th get row numbers 1, 2, 3, 4 and differences of day 0, 0, 0, 3. The first three collapse into one group of three; the 7th is its own group of one.

DISTINCT first, because two logins on the same day would get two row numbers and break the arithmetic. In dialects where date minus integer is not allowed, use DATE_SUB or cast to a day number.

• What they are checking: whether you know the technique, and whether you can explain why the subtraction works rather than reciting it.
• Common mistake: a self-join on date + 1 and date + 2, which finds exactly-three-day runs and misses longer ones unless you add more joins.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

Practice this for real

• Check your resume against a Data Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card