# First Arrivals

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

Canonical URL: <https://datadriven.io/problems/first_arrivals>

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

For each calendar date, how many customers placed their very first order on that day? A customer's first-order date is the earliest order they have in the table.

## 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

**MIN date per user, then count per day**

```sql
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
```

> **The whole trick is the grain**
>
> Do the customer-level reduction first. Once each customer is a single row carrying their first date, counting per date is trivial. Try to do it in one pass over the raw table and you are counting orders, not customers.

> **Interviewers watch for**
>
> A strong candidate names the two grains out loud before writing anything, and asks whether a customer who ordered twice on day one counts once (it does). Jumping straight to GROUP BY transaction_date on the raw table is the junior tell.

> **Common pitfall**
>
> Grouping the raw `transactions` table by `transaction_date` counts orders per day, not new customers per day. Repeat buyers land on every day they purchased, so the acquisition curve balloons. The `MIN` per customer is the only thing that makes it a 'first' order.

> **Cost analysis**
>
> At 80,000,000 rows the inner aggregation is the cost: one grouping pass over `user_id` to compute each `MIN(transaction_date)`. That intermediate is at most one row per customer (roughly 4,000,000 rows), so the outer count over it is cheap. In production you would persist first-order dates in a customer dimension so the daily count reads a small table instead of rescanning the fact table.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/first_arrivals)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.