Measure Customers Ordering from Bottom-Quartile Restaurants
Quick 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.
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 | numeric |
## Task
For each calendar month in 2021, total sales by restaurant and assign restaurants to four sales buckets with NTILE(4), ordered from lowest monthly sales to highest and then by restaurant ID for deterministic ties. Calculate the percentage of distinct monthly customers who placed at least one order from a restaurant in bucket 1.
## Required Output
Return order_month, monthly_customers, bottom_quartile_customers, and bottom_quartile_customer_pct rounded to two decimals. Sort by month ascending.
## Constraints
- A customer who orders from several bottom-quartile restaurants counts once.
- The denominator is all distinct customers ordering that month.
- Use the specified restaurant-ID tie break when assigning buckets.
```hint Separate restaurant and customer grains
First compute restaurant-month sales and buckets, then join the bucket membership back to customer orders.
```
Quick Answer: 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.
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
numeric
Task
For each calendar month in 2021, total sales by restaurant and assign restaurants to four sales buckets with NTILE(4), ordered from lowest monthly sales to highest and then by restaurant ID for deterministic ties. Calculate the percentage of distinct monthly customers who placed at least one order from a restaurant in bucket 1.
Required Output
Return order_month, monthly_customers, bottom_quartile_customers, and bottom_quartile_customer_pct rounded to two decimals. Sort by month ascending.
Constraints
A customer who orders from several bottom-quartile restaurants counts once.
The denominator is all distinct customers ordering that month.
Use the specified restaurant-ID tie break when assigning buckets.