Skip to content
Quantoros

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

Related questions