# Average Fulfillment Lag

> Ordered, then... waiting.

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

Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Airbnb

## Problem

The operations team is measuring fulfillment lag across the customer base. For each user, compute the average number of days between their transaction dates and today, only considering transactions with a positive amount. Present users from the longest average lag to the shortest.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 15 rows:

| user_id | avg_days |
| --- | --- |
| 197 | 862.2142857142857 |
| 1167 | 820.3333333333334 |
| 682 | 809.6428571428571 |
| 294 | 650.2142857142857 |
| 779 | 647.7142857142857 |

## Worked solution and explanation

### Why this problem exists in real interviews

Fulfillment lag is the canonical date-arithmetic prompt. The interviewer wants to see you treat `julianday('now') - julianday(transaction_date)` as a real expression with a real type (float days), wrap it in `CAST` deliberately, and aggregate per user. They are also checking whether you pin down the reference date and the cohort filter before touching the keyboard.

---

### Break down the requirements

#### Step 1: Pin the cohort

Filter transactions to those with a positive `total_amount`.

#### Step 2: Compute days, one row at a time

For each row compute the day gap as julianday(today) - julianday(`transaction_date`).

#### Step 3: Aggregate and rank

Group by `user_id` and average the per-transaction day gaps.

---

### The solution

**AVG DAYS SINCE TRANSACTION PER USER**

```sql
SELECT user_id, AVG(julianday(date('now')) - julianday(transaction_date)) AS avg_days FROM transactions WHERE total_amount > 0 GROUP BY user_id ORDER BY avg_days DESC
```

> **Cost Analysis**
>
> `transactions` is 30M rows partitioned by `transaction_date`, but there is no date predicate here, so every partition gets read. Expect a full scan into a hash aggregate keyed on `user_id` (high cardinality, likely millions of groups). If ops only cares about recent activity, push a `transaction_date >= ?` predicate to prune partitions.

> **Interviewers Watch For**
>
> Ask two things out loud before writing: **as of what date?** (`'now'` resolves at query time, so cached results drift) and fractional or whole days? (the `CAST` to INTEGER truncates each row before `AVG`, which is coarser than averaging the raw float). State your answers, then write.

> **Common Pitfall**
>
> Writing `AVG(julianday('now')) - AVG(julianday(transaction_date))` instead of averaging the per-row difference. It happens to give the same number for `AVG`, but the moment you swap to `MIN`, `MAX`, or add a `CAST`, the two diverge. Keep the subtraction inside the aggregate.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you make this reproducible across reruns? _(Probes that you understand `'now'` is evaluated at query time and should be replaced with a bound parameter or snapshot date.)_
- Finance wants fractional days. What changes? _(Tests whether you can explain why the inner `CAST AS INTEGER` is the lossy step and drop it cleanly.)_
- Only users with at least 5 qualifying transactions. Where does that go? _(Checks `HAVING COUNT(*) >= 5` vs `WHERE`, and that you know the filter is on the aggregated group.)_
- Same metric, but bucketed by month of `transaction_date`. How? _(Probes `GROUP BY user_id, strftime('%Y-%m', transaction_date)` and partition pruning awareness.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/average_fulfillment_lag)
- [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.