Write monthly customer and sales SQL queries
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
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.
Overview: This question evaluates SQL data-manipulation and analytical skills, including temporal aggregation, grouping and counting, percent calculations, ranking/ntile-based quartile assignment, and windowed month-over-month comparisons applied to a marketplace orders dataset.
Read the full TikTok Data Scientist interview experience this question came from
Q3) Month-over-month sales change for a restaurant in 2021
Tables
orders(order_id BIGINT, customer_id BIGINT, restaurant_id BIGINT, order_ts TIMESTAMP, order_amount NUMERIC)
customers(customer_id BIGINT)
restaurants(restaurant_id BIGINT)
Hints
- Aggregate orders by DATE_TRUNC('month', order_ts) and filter to 2021 with a half-open date range [2021-01-01, 2022-01-01).
- Avoid QUALIFY; compute month-over-month via a self-join on the prior month or by using LAG in a subquery/CTE and filtering in the outer query.
Community answers
Answer by yoyo
(1) with customer_month as (
select
date_trunct('month', order_ts) as month,
o.customer_id,
count(*) as customer_order_per_month
from orders o
group by 1,2),
month_total as (
select
month,
count(*) as month_customer_num,
sum(when customer_order_per_month > 30 then 1 else 0 end) as high_freq_customers
from customer_month
group by month,
select
month,
coleasce( high_freq_customers::numeric/nullif(month_customer_num, 0), 0) as high_freq_customers_ratio
from month_total;
from month_total
Answer by yoyo
(2)
With month_order as (Select
date_trunc(‘month’, order_ts):: Date as month,
customer_id,
Count(*) as monthly_order_amt
From orders
Group by 1,2 ),
Eligibility as (
Select month, customer_id, month_order_cmt,
dense_rank() over(month_order_amt desc) as rank
From month_order ),
Select month, customer_id, month_order_amt
From eligibility where rank =1;
Follow-up :
Select
customer_id
From (
Select
customer_id,
dense_rank() over (order_cnt desc) as rnk
From (
Select customer_id,
count(distinct order_id) over (partition by customer_id) as order_cnt
From orders )
)
Where ink = 1;
Answer by yoyo
(3)
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_ts) AS year_month,
restaurant_id,
SUM(order_amount) AS monthly_sales
FROM orders
WHERE order_ts >= TIMESTAMP '2021-01-01'
AND order_ts < TIMESTAMP '2022-01-01'
AND restaurant_id = :restaurant_id
GROUP BY 1, 2
)
SELECT
year_month,
restaurant_id,
monthly_sales,
monthly_sales - LAG(monthly_sales) OVER (ORDER BY year_month) AS mom_sales_change
FROM monthly
QUALIFY LAG(monthly_sales) OVER (ORDER BY year_month) IS NOT NULL;
Answer by yoyo
With rr as (
Select
year_month,
restaurant_id,
monthly_sales,
NTILE(4) over (PARITION BY MONTH,
ORDER BY MONTHLY_SALES ASC, RESTAURNT_ID ASC) AS sales_percentile
From ( SELECT
DATE_TRUNC('month', order_ts) AS year_month,
restaurant_id,
SUM(order_amount) AS monthly_sales
FROM orders
)
),
Select year_month,
count(distinct case when rr.sales_percentile = 1 then o.customer_id end)::numeric/nullif(count(distinct customer_id),0) as pct_customers_order_low_percentile_res
From orders o
Join rr on o.restaurant_id = rr.resutant_id and
extract('month’, order_ts) = rr.year_month
Group by month