Interview Question Bank · EXTRACT(HOUR) · time buckets · conditional share
6. Lunch vs dinner order share by city
MediumFood deliveryQuestion #234
Concept: EXTRACT(HOUR) · time buckets · conditional shareshow
EXTRACT(HOUR FROM ts) returns the hour as a number from 0 to 23, which makes time-of-day buckets easy. Define a window by whole hours so its edges are unambiguous: 11:00 to 14:59 is hours 11 through 14. BETWEEN includes both ends, so BETWEEN 11 AND 15 also catches everything from 15:00 to 15:59.
Example (different data):
SELECT store, COUNT(*) AS sales, ROUND(100.0 * COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM sold_at) BETWEEN 6 AND 9) / COUNT(*), 1) AS breakfast_pct FROM sales GROUP BY store;
Your task
Brightbowl's city leads are planning courier shifts and want to know what share of each city's demand lands in the lunch peak and what share in the dinner peak.
Count every order in fd_orders, cancelled ones included (this is about demand). Lunch is placed_at from 11:00 to 14:59, dinner from 17:00 to 21:59. Return city (the restaurant's city), orders, lunch_pct and dinner_pct (each as a % of the city's orders, ROUND(..., 1)). Sort by city.
Starting Postgres in your browser…
Checking your access…