Measure Customers Ordering from Bottom-Quartile Restaurants
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
# Measure Customers Ordering from Bottom-Quartile Restaurants
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
delivery_orders
| column | type |
|---|---|
| delivery_id | integer |
| order_place_time | timestamp |
| restaurant_id | integer |
| customer_id | integer |
order_value
| column | type |
|---|---|
| delivery_id | integer |
| order_total | real |
## Data Guarantees
- `delivery_orders.delivery_id` is unique, and every 2021 delivery has exactly one matching row in `order_value`.
- `order_place_time`, `restaurant_id`, `customer_id`, and `order_total` are non-NULL for those matched deliveries.
- Every calendar month in 2021 has at least one matched delivery.
## Task
Use deliveries placed on or after `2021-01-01` and before `2022-01-01`. For each calendar month:
1. Cast each matched `order_total` to `numeric` before aggregation, then sum those numeric values by restaurant to calculate each restaurant's monthly sales. Use these numeric sums when assigning sales buckets.
2. Assign the restaurants that had at least one matched delivery that month to four sales buckets with `NTILE(4)`, ordered by monthly sales ascending and then by `restaurant_id` ascending for deterministic ties.
3. Count all distinct customers with a matched delivery that month.
4. Count the distinct customers who placed at least one order from a restaurant assigned to bucket 1 that month.
## Required Output
Return exactly these columns:
| column | meaning |
|---|---|
| `order_month` | The first date of the calendar month, as a date |
| `bottom_quartile_customer_pct` | `100 *` the number of distinct customers with at least one bucket-1 order that month, divided by all distinct customers with a matched delivery that month, rounded to two decimal places and returned as `numeric(5,2)` |
Return exactly 12 rows, one for each month of 2021, sorted by `order_month` ascending.
## Constraints
- A customer who orders from several bucket-1 restaurants counts once in the numerator.
- A customer who orders from both bucket-1 and other restaurants still counts once in the numerator and once in the denominator.
- Use the restaurant-ID tie-break when assigning buckets; do not rank restaurants by individual order values.
Overview: A PostgreSQL interview problem about measuring customers who order from bottom-quartile restaurants. Candidates must define the restaurant population and quartile grain, handle ties and sparse activity, then calculate a distinct-customer share with the correct denominator.
Using PostgreSQL, analyze matched delivery_orders and order_value rows for deliveries placed on or after 2021-01-01 and before 2022-01-01. For each calendar month, cast each REAL order_total to numeric before summing by restaurant, assign active restaurants to NTILE(4) buckets ordered by monthly numeric sales ascending and restaurant_id ascending, and return the percentage of distinct monthly customers who ordered from at least one bucket-1 restaurant among all distinct matched-delivery customers that month. Return order_month as the first date of the month and the percentage rounded to two decimals as numeric(5,2), with exactly 12 rows sorted chronologically.
Tables
delivery_orders(delivery_id INTEGER, order_place_time TIMESTAMP, restaurant_id INTEGER, customer_id INTEGER)
order_value(delivery_id INTEGER, order_total REAL)
Hints
- Keep the restaurant-month sales calculation separate from customer-level counting.
- Apply the stated date interval exactly and preserve distinct-customer semantics in both counts.
Community answers
Answer by tmm86nn
-- Write your SQL query here
with filtered as
(
SELECT DATE_TRUNC('month',order_place_time)::date as order_month,
restaurant_id,
customer_id,
order_total::NUMERIC(4,2) as order_total
FROM delivery_orders a join order_value b on a.delivery_id = b.delivery_id
where order_place_time between '2021-01-01' and '2022-01-01'
)
,
agg as
(SELECT order_month,
restaurant_id,
sum(order_total) as monthly_sales
from filtered
group by 1,2
)
,
ntiles as
(SELECT order_month,restaurant_id,
ntile(4) over (partition by order_month order by monthly_sales,restaurant_id) as quartile
from agg
)
SELECT
n.order_month,
round(count(distinct case when quartile = 1 then customer_id end)*100::numeric/
nullif(count(distinct customer_id) ,0),2)::numeric(5,2) AS bottom_quartile_customer_pct
from ntiles n join filtered f on n.order_month = f.order_month and n.restaurant_id = f.restaurant_id
group by 1
order by order_month