Average Fulfillment Lag

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

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Airbnb

Problem

Find each user's average purchase age in days, counting from each date in transactions up to today, oldest averages first. We only want real sales in that number, so rows with a zero or negative amount stay out.

Ordered, then... waiting.

Tables

transactions

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

Expected output

Worked solution and explanation

This is a per-user average of date differences, dressed up as a customer recency metric. Everyone writes the GROUP BY user_id and the AVG. What separates candidates is deciding which rows are purchases at all, and what 'today' means, before typing. The seed hides a voided sale (total_amount of 0.0 for user 585) and a refund (-45.0 for user 973). Leave them in and those two users' averages absorb dates that were never purchases, so the list is wrong exactly for the customers with returns.

Build it in three decisions

Step 1: Keep only real sales

A refund or a void is a row in transactions but not a purchase. WHERE total_amount > 0 drops both before anything is averaged. Once the bad rows are inside a user's mean, no clause after the aggregate can take them back out.

Step 2: Turn each date into a day count

julianday(date('now')) - julianday(transaction_date) is the age of one purchase in days. date('now') is today with no time of day, and transaction_date is a plain date, so every per-row gap is a whole number of days.

Step 3: Average per user, oldest first

GROUP BY user_id with AVG(...) gives one row per buyer, keeping the fractional average. ORDER BY avg_days DESC puts the buyers with the oldest purchase history at the top, which is how the list is read.

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

Every row averaged

No WHERE: user 973's -45.0 refund and user 585's voided 0.0 sale each contribute a date, so their avg_days drift toward those dates.

Real sales only

WHERE total_amount > 0: each average is built only from purchases. The other thirteen users come out identical either way, which is why the bug survives a quick eyeball check.

Follow-up questions

Common follow-up questions

• How would you make this reproducible across reruns? (Probes that `'now'` is evaluated at query time and should become a bound snapshot date.)
• Only users with at least 5 real sales. Where does that go? (Checks `HAVING COUNT(*) >= 5` rather than a row-level `WHERE`.)
• Same metric, but bucketed by month of `transaction_date`. How? (Probes adding `strftime('%Y-%m', transaction_date)` to the `GROUP BY` and partition pruning awareness.)
• The team wants whole days in the report. What changes? (Tests rounding the averaged value with `ROUND` rather than truncating each row before `AVG`.)