Find orders from bottom-quartile revenue restaurants
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
## SQL Question
You want to identify **orders coming from restaurants whose total revenue is in the bottom 25th percentile**.
Assume the following tables:
### `restaurants`
- `restaurant_id` (BIGINT, PK)
- `name` (VARCHAR)
- `market_id` (BIGINT)
### `orders`
- `order_id` (BIGINT, PK)
- `restaurant_id` (BIGINT, FK → restaurants.restaurant_id)
- `customer_id` (BIGINT)
- `order_total` (DECIMAL(10,2)) — revenue for this order (exclude tips)
- `created_at` (TIMESTAMP)
- `status` (VARCHAR) — e.g., 'completed', 'canceled'
## Task
Write a SQL query to return orders from restaurants whose **total completed-order revenue** is in the **bottom 25%** among all restaurants over a specified analysis window.
### Requirements
- Use an analysis window of the **last 30 days** relative to `CURRENT_DATE`.
- Consider only `status = 'completed'` orders.
- Define restaurant revenue as `SUM(order_total)` over the window.
- Compute the **25th percentile** of restaurant revenue across restaurants with ≥1 completed order in the window.
### Output columns
- `order_id`
- `restaurant_id`
- `order_total`
- `created_at`
- `restaurant_revenue_30d`
State any assumptions (e.g., percentile function availability in your SQL dialect).
Overview: This question evaluates proficiency in SQL data manipulation, including time-windowed aggregation, joins, filtering by status and date, and percentile-based ranking of restaurant revenue using completed orders.
You are given DoorDash-style order data. Define a restaurant's revenue as the sum of total_amount from DELIVERED orders during 2025-05-01 to 2025-05-31 (inclusive).
Task:
1) Compute each restaurant's revenue for 2025-05.
2) Rank restaurants into revenue quartiles using NTILE(4) ordered by revenue ascending (lowest revenue = quartile 1).
3) Return all DELIVERED orders in 2025-05 that belong to restaurants in the bottom revenue quartile (quartile 1).
Output columns: order_id, restaurant_id, restaurant_name, order_date, total_amount, restaurant_may_revenue.
Note: Only DELIVERED orders count toward revenue and should appear in the output.
Tables
restaurants(restaurant_id INT, restaurant_name VARCHAR(100))
orders(order_id INT, restaurant_id INT, customer_id INT, order_date DATE, status VARCHAR(20), total_amount DECIMAL(10,2))
Hints
- First aggregate delivered order revenue per restaurant within 2025-05-01 to 2025-05-31.
- Use NTILE(4) over the restaurant revenue ordered ascending to identify the bottom quartile, then join back to orders.