Using a month index YEAR12+MONTH over 2026 orders: for each month, what percent of that months customers ALSO ordered…
SQLLevel: IntermediateWindow functions
Using a month index YEAR*12+MONTH over 2026 orders: for each month, what percent of that months customers ALSO ordered the previous month? Return month_index and pct rounded to 1 decimal, in order.
Table definitions (from the source exercise):
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
CREATE TABLE orders(order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, amount REAL, rep_id INTEGER, discount REAL);
Hints
- Start with distinct customer-month pairs so multiple orders do not inflate the customer count.
- Match each month to its predecessor; check the numerator, denominator and year boundary separately.
Answer
Approach: Distinct customer-month CTE, LEFT self-join on customer and index-1, conditional count.
WITH cm AS (
SELECT DISTINCT customer_id,
YEAR(order_date) * 12 + MONTH(order_date) AS mi
FROM orders
WHERE order_date >= '2026-01-01'
)
SELECT a.mi AS month_index,
ROUND(100.0 * COUNT(b.customer_id) / COUNT(*), 1) AS pct_repeat
FROM cm a
LEFT JOIN cm b ON b.customer_id = a.customer_id AND b.mi = a.mi - 1
GROUP BY a.mi
ORDER BY a.mi;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT