Write monthly customer and sales SQL queries

You are analyzing a food-delivery marketplace.

Tables

Assume the following schema (you may add minor helper CTEs as needed):

orders

• order_id (BIGINT, PK)
• customer_id (BIGINT)
• restaurant_id (BIGINT)
• order_ts (TIMESTAMP) — time the order was placed
• order_amount (NUMERIC) — total GMV for the order

customers

• customer_id (BIGINT, PK)

restaurants

• restaurant_id (BIGINT, PK)

Conventions / Definitions

• “Month” means calendar month based on order_ts (assume UTC unless otherwise stated).
• “Monthly order count” for a customer is the number of orders they placed in that month.
• A “high-frequency customer” in a given month is a customer with monthly order count > 30 .

Q1) Percentage of high-frequency customers each month

For each month, compute the percentage of distinct customers who are high-frequency customers.

Output:

• month (DATE or TIMESTAMP truncated to month)
• pct_high_frequency_customers

Q2) Most-ordering customer each month excluding high-frequency customers

For each month, find the customer(s) with the highest monthly order count among customers with monthly order count ≤ 30.

Output:

• month
• customer_id
• monthly_order_count

Follow-up: Identify the single customer (or customers, if tied) with the most total orders across all months (no exclusion).

Q3) Month-over-month sales change for a restaurant in 2021

Given a specific restaurant_id, compute monthly total sales in 2021 and the month-over-month (MoM) change, excluding the first month in the series (i.e., only months where a prior month exists).

Output:

• year_month
• restaurant_id
• monthly_sales
• mom_sales_change (current month sales − previous month sales)

Follow-up: How would you change the query to return the MoM change for all restaurants?

Q4) Percentage of customers ordering from bottom-quartile restaurants

For each month, rank restaurants by that month’s total sales and label restaurants into quartiles (4 buckets) within the month.

Define “bottom quartile restaurants” as the lowest 25% by monthly sales for that month.

Compute, for each month, the percentage of distinct customers who placed at least one order from a bottom-quartile restaurant.

Output:

• month
• pct_customers_ordered_bottom_quartile

Notes:

• Clarify and handle ties consistently (e.g., using NTILE(4) over monthly restaurant sales).
• A customer should be counted at most once per month in numerator/denominator.