First Arrivals

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

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

JPMorgan Chase

Problem

The transactions table holds one row per order, and a customer who keeps buying shows up on many dates while only their earliest order marks the day they arrived. Count how many customers placed their very first order on each date, oldest date first.

Every customer has a first day. Find when the crowds showed up.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, transaction_date TEXT, product_id INTEGER, quantity INTEGER, total_amount REAL

Expected output

Worked solution and explanation

What this really is

Beneath the acquisition-metrics costume, this is a per-customer earliest-date reduction feeding a per-date count: two aggregations at two different grains stacked back to back. Anyone can write a COUNT and a GROUP BY. The tell is whether you keep the grains straight: collapse each customer down to their first date first, THEN count those dates. Skip the first stage, group the raw table by date, and you count orders instead of first-time customers. Every repeat buyer then inflates the day they came back, and your 'new customer' curve is quietly a total-orders curve.

Walking it

Step 1: Find each customer's first transaction date

Reduce the raw table to one row per customer: MIN(transaction_date) grouped by user_id gives each customer's earliest order date. A customer who ordered five times still becomes a single row here, which is exactly what stops the later count from double-counting repeat buyers.

Step 2: Count new customers per day

Now the grain is one row per customer, stamped with their first date. Group THAT by first_date with COUNT(*) to tally how many customers debuted each day. The two GROUP BYs sit at different grains: customer, then date. Keep them in separate stages and the logic stays honest.

The solution

WITH first_purchase AS (
    SELECT user_id, MIN(transaction_date) AS first_date
    FROM transactions
    GROUP BY user_id
)
SELECT first_date, COUNT(*) AS new_customers
FROM first_purchase
GROUP BY first_date
ORDER BY first_date

Common follow-up questions

• How would you also surface calendar days where zero customers placed a first order? (Tests date-spine thinking: a GROUP BY only emits dates that actually occur.)
• If a customer's earliest order is later refunded, should that date still count as their first-order date? (Tests whether the candidate questions the business definition of 'first order'.)
• At 80M rows growing daily, how would you avoid rescanning the whole fact table for this metric every day? (Tests incremental or materialized maintenance of first-order dates instead of a full rescan.)