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