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.

Measure Customers Ordering from Bottom-Quartile Restaurants

Table delivery_orders

column	type	constraints
delivery_id	INTEGER	PRIMARY KEY
order_place_time	TIMESTAMP	NOT NULL
restaurant_id	INTEGER	NOT NULL
customer_id	INTEGER	NOT NULL

Sample data:

customer_id	delivery_id	order_place_time	restaurant_id
101	1001	2021-01-01 00:00:00	11
102	1002	2021-01-10 12:00:00	12
103	1003	2021-01-20 18:30:00	13
104	1004	2021-01-31 23:59:59	14
105	1005	2021-02-15 09:00:00	21
106	1006	2021-03-15 09:00:00	31
107	1007	2021-04-15 09:00:00	41
108	1008	2021-05-15 09:00:00	51
109	1009	2021-06-15 09:00:00	61
110	1010	2021-07-15 09:00:00	71
111	1011	2021-08-15 09:00:00	81
112	1012	2021-09-15 09:00:00	91
113	1013	2021-10-15 09:00:00	101
114	1014	2021-11-15 09:00:00	111
115	1015	2021-12-31 23:59:59	121

Table order_value

column	type	constraints
delivery_id	INTEGER	PRIMARY KEY
order_total	REAL	NOT NULL

Sample data:

delivery_id	order_total
1001	1.25
1002	2.5
1003	3.75
1004	5
1005	15
1006	16
1007	17
1008	18
1009	19
1010	20
1011	21
1012	22
1013	23
1014	24
1015	25

Expected output

order_month	bottom_quartile_customer_pct
2021-01-01	25
2021-02-01	100
2021-03-01	100
2021-04-01	100
2021-05-01	100
2021-06-01	100
2021-07-01	100
2021-08-01	100
2021-09-01	100
2021-10-01	100
2021-11-01	100
2021-12-01	100

Hints

1. Keep the restaurant-month sales calculation separate from customer-level counting.
2. Apply the stated date interval exactly and preserve distinct-customer semantics in both counts.
3. Use every specified ordering key when assigning monthly sales buckets.