Find orders from bottom-quartile revenue restaurants

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).

Find orders from bottom-quartile revenue restaurants

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.

Table restaurants

column	type	constraints
restaurant_id	INT	PRIMARY KEY, NOT NULL
restaurant_name	VARCHAR(100)	NOT NULL

Sample data:

restaurant_id	restaurant_name
1	Sushi Zen
2	Taco Town
3	Pasta Palace
4	Burger Barn
5	Salad Stop
6	Curry Corner
7	Pizza Plaza
8	BBQ House

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
restaurant_id	INT	NOT NULL
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL
total_amount	DECIMAL(10,2)	NOT NULL

Sample data:

customer_id	order_date	order_id	restaurant_id	status	total_amount
1001	2025-05-02	101	1	DELIVERED	200
1002	2025-05-10	102	1	DELIVERED	150
1003	2025-05-28	103	1	DELIVERED	150
1004	2025-04-20	104	1	DELIVERED	80
1101	2025-05-05	201	2	DELIVERED	60
1102	2025-05-18	202	2	DELIVERED	60
1103	2025-05-25	203	2	CANCELED	40
1104	2025-04-15	204	2	DELIVERED	100
1201	2025-05-03	301	3	DELIVERED	300
1202	2025-05-11	302	3	DELIVERED	300
1203	2025-05-29	303	3	DELIVERED	300
1301	2025-05-07	401	4	DELIVERED	100
1302	2025-05-19	402	4	DELIVERED	200
1303	2025-04-09	403	4	DELIVERED	50
1401	2025-05-03	501	5	DELIVERED	20
1402	2025-05-20	502	5	DELIVERED	30
1501	2025-05-04	601	6	DELIVERED	200
1502	2025-05-12	602	6	DELIVERED	250
1503	2025-05-26	603	6	DELIVERED	250
1601	2025-05-09	701	7	DELIVERED	120
1602	2025-05-21	702	7	DELIVERED	130
1701	2025-05-06	801	8	DELIVERED	400
1702	2025-05-16	802	8	DELIVERED	300
1703	2025-05-30	803	8	DELIVERED	300

Expected output

Returns all delivered May 2025 orders that come from restaurants in the bottom revenue quartile (using NTILE(4) over restaurant May revenue ascending). Columns: order_id, restaurant_id, restaurant_name, order_date, total_amount, restaurant_may_revenue.

order_date	order_id	restaurant_id	restaurant_may_revenue	restaurant_name	total_amount
2025-05-03	501	5	50	Salad Stop	20
2025-05-05	201	2	120	Taco Town	60
2025-05-18	202	2	120	Taco Town	60
2025-05-20	502	5	50	Salad Stop	30

Hints

1. First aggregate delivered order revenue per restaurant within 2025-05-01 to 2025-05-31.
2. Use NTILE(4) over the restaurant revenue ordered ascending to identify the bottom quartile, then join back to orders.