Analyze DoorDash Orders: High-Frequency Customers, Top Spenders, MoM Sales & Bottom-Percentile Reach
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
orders
+-------------+-------------+---------------+---------------------+
| delivery_id | customer_id | restaurant_id | order_place_time |
+-------------+-------------+---------------+---------------------+
| 1001 | 1 | 1 | 2024-01-01 09:00:00 |
| 1002 | 1 | 2 | 2024-01-02 09:01:00 |
| ... | ... | ... | ... |
+-------------+-------------+---------------+---------------------+
order_value
+-------------+--------------+
| delivery_id | order_amount |
+-------------+--------------+
| 1001 | 5.00 |
| 1002 | 5.00 |
| ... | ... |
+-------------+--------------+
##### Scenario
You are a data scientist on the DoorDash marketplace team. You have an `orders` table (one row per delivery) and an `order_value` table with each delivery's amount. Work through the four parts below.
##### Question
1. **High-frequency orders by month.** A high-frequency customer places more than 30 orders in a calendar month. For each month, find the percentage of that month's orders placed by high-frequency customers.
2. **Top customer per month.** Excluding the high-frequency customers above, find the top customer for each month by total spend. If there is a tie, return every tied customer.
3. **Restaurant month-over-month sales.** Using `order_value` joined to `orders` (`restaurant_id`), compute the month-over-month change in total sales for `restaurant_id = 5`. Follow-up: generalize to every restaurant.
4. **Bottom-30% restaurant reach (read & rewrite).** Given a query that returns the percentage of customers who order from bottom-30%-by-sales restaurants, explain what it does, then rewrite it to compute that percentage per month.
##### Hints
Think DATE_TRUNC for monthly buckets, COUNT per (month, customer) for the >30 filter, RANK() for ties, LAG() for month-over-month, and PERCENT_RANK() for the bottom-30% percentile.
Overview: A four-part DoorDash data-scientist SQL screen on an orders / order_value schema: the monthly share of high-frequency (>30 orders/month) orders, the top non-high-frequency customer per month (with ties), month-over-month sales change for a restaurant, and the per-month customer reach of bottom-30%-by-sales restaurants — with full solutions.
Monthly share of high-frequency orders
A **high-frequency customer** in a given month is a customer who places **more than 30 orders** in that calendar month.
For each month, return the **percentage of that month's orders** that were placed by high-frequency customers. Return one row per month with the order month, the total orders, the high-frequency orders, and the percentage (0-100, rounded to 2 decimals).
Tables
orders(delivery_id INTEGER, customer_id INTEGER, restaurant_id INTEGER, order_place_time TIMESTAMP)
order_value(delivery_id INTEGER, order_amount DECIMAL(10,2))
Hints
- Bucket orders by month with DATE_TRUNC('month', order_place_time).
- Count orders per (month, customer); a customer is high-frequency that month when the count > 30.
Top customer per month (excluding high-frequency customers)
**Excluding** the high-frequency customers from Q1 (customers with more than 30 orders in a month), find the **top customer for each month by total spend** (sum of `order_amount`). If two or more customers tie for the highest monthly spend, return **all** of them. Return the order month, customer id, and total spend.
Tables
orders(delivery_id INTEGER, customer_id INTEGER, restaurant_id INTEGER, order_place_time TIMESTAMP)
order_value(delivery_id INTEGER, order_amount DECIMAL(10,2))
Hints
- Join orders to order_value to get each order's amount.
- Exclude, per month, any customer with > 30 orders that month.
Month-over-month sales change for restaurant 5
Using `order_value(delivery_id, order_amount)` joined to `orders` (which has `restaurant_id`), compute the **month-over-month change in total sales for `restaurant_id = 5`**, where a month's sales is the sum of `order_amount` for that restaurant. Return each month's total sales, the previous month's sales, the absolute change, and the percent change.
**Follow-up:** generalize the query to report the month-over-month change for **every** restaurant (see the solution notes).
Tables
orders(delivery_id INTEGER, customer_id INTEGER, restaurant_id INTEGER, order_place_time TIMESTAMP)
order_value(delivery_id INTEGER, order_amount DECIMAL(10,2))
Hints
- Aggregate sales per month for restaurant 5, then use LAG(total_sales) OVER (ORDER BY month).
- Absolute change = current - previous; percent change = 100 * (current - previous) / previous.
Explain & rewrite: customer reach of bottom-30% restaurants
The interviewer hands you the query below and asks you to **explain what it computes** (you do not have to write it):
```sql
WITH restaurant_sales AS (
SELECT o.restaurant_id, SUM(ov.order_amount) AS total_sales
FROM orders o
JOIN order_value ov ON ov.delivery_id = o.delivery_id
GROUP BY o.restaurant_id
),
ranked AS (
SELECT restaurant_id, total_sales,
PERCENT_RANK() OVER (ORDER BY total_sales) AS pct_rank
FROM restaurant_sales
),
bottom_restaurants AS (
SELECT restaurant_id FROM ranked WHERE pct_rank < 0.30
)
SELECT
ROUND(
100.0 * COUNT(DISTINCT CASE
WHEN o.restaurant_id IN (SELECT restaurant_id FROM bottom_restaurants)
THEN o.customer_id END)
/ COUNT(DISTINCT o.customer_id), 2
) AS pct_customers_ordering_bottom30
FROM orders o;
```
It ranks restaurants by all-time total sales, takes the restaurants in the **bottom 30%** (`PERCENT_RANK < 0.30`), and returns the **percentage of all customers** who placed at least one order at any of those bottom-30% restaurants.
**Now rewrite it** so the percentage is computed **per month**: for each month, what share of that month's customers ordered from a restaurant that is in the bottom 30% by sales **within that month**? Return one row per month.
Tables
orders(delivery_id INTEGER, customer_id INTEGER, restaurant_id INTEGER, order_place_time TIMESTAMP)
order_value(delivery_id INTEGER, order_amount DECIMAL(10,2))
Hints
- Make the percentile per-month: PERCENT_RANK() OVER (PARTITION BY month ORDER BY total_sales).
- Recompute the bottom-30% restaurant set inside each month.