One Year to the Next

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Airbnb

Problem

The growth dashboard keeps celebrating signup jumps, but the growth team suspects the gains are piling onto a few busy days instead of spreading across the calendar. From users, give each signup year the number of separate days that saw at least one signup, next to the prior year's figure and the change from it as a whole-number percent.

This year versus last year. Growing or shrinking?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a count-the-days-not-the-rows problem hiding behind a growth chart, with an integer-division trap stacked on top. The phrase 'signup activity' pulls you toward counting rows, but the metric is how many separate calendar days saw a signup, and many users land on the same day. Count rows and you report signups; you want the number of days those signups fall on, and a day stays one day even when a row happens to carry a time on the end. Then the second trap: a difference of two integer counts divided by an integer count stays an integer, so a real 133 percent jump collapses to a blunt 100 and every smaller swing flattens to 0. Miss either and the numbers look plausible but are wrong.

Break down the requirements

Step 1: Count days, not rows or strings

Take the year with strftime('%Y', signup_date), then count each calendar day once with COUNT(DISTINCT strftime('%Y-%m-%d', signup_date)). A busy day with many signups is still one day, and so is '2025-08-08 16:34:00': as a raw string it differs from the plain '2025-08-08' another user signed up on, and 2025 would read 43 days instead of 42.

Step 2: Pull the prior year inline

Compute LAG(active_signup_days) OVER (ORDER BY signup_year) once, in with_prior_year, on the yearly aggregate, and reuse the named prev_year_active_days. The first year has no predecessor, so it gets NULL, and that NULL propagates through the division to give a null growth for the earliest row.

Step 3: Make the division real, once

Cast the year-over-year difference to REAL once, then divide by last year's count, multiply by 100 and round to a whole percent. One real operand is all the division needs; casting the denominator too adds nothing.

The solution

WITH yearly_signups AS (
  SELECT strftime('%Y', signup_date) AS signup_year,
         COUNT(DISTINCT strftime('%Y-%m-%d', signup_date)) AS active_signup_days
  FROM users
  GROUP BY signup_year
),
with_prior_year AS (
  SELECT signup_year,
         active_signup_days,
         LAG(active_signup_days) OVER (ORDER BY signup_year) AS prev_year_active_days
  FROM yearly_signups
)
SELECT signup_year,
       active_signup_days,
       prev_year_active_days,
       ROUND(
         CAST(active_signup_days - prev_year_active_days AS REAL)
         / prev_year_active_days
         * 100
       ) AS yoy_growth_pct
FROM with_prior_year

Common follow-up questions

Common follow-up questions

• How would you filter to only years with negative growth? (Filter on `yoy_growth_pct` in an outer `SELECT`; `LAG` cannot live in `WHERE`.)
• What if growth is versus a rolling 3-year average instead? (Swap `LAG` for `AVG(active_signup_days) OVER (ORDER BY signup_year ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)`.)
• How does the answer shift with soft-deletes via `account_status`? (Decide if a deleted account counts. If not, filter inside the CTE before aggregating.)