Apple: Apple Pay Rolling Seven-Day Fraud Rate — SQL Problem
The Apple Pay risk team watches a smoothed fraud rate rather than noisy single days.
- Difficulty: Hard
- Topics: Window Functions, Dates, Subqueries
- Dialect: MySQL
- Problem: #622
Problem statement
The Apple Pay risk team watches a smoothed fraud rate rather than noisy single days.
For every calendar day from the earliest to the latest txn_date in the table — including days with no transactions — return day, txns_7d (transactions in the seven days ending that day, i.e. from day − 6 to day inclusive) and fraud_pct_7d, the percentage of those transactions with flagged_fraud = 1, rounded to 2 decimal places. When the seven-day window holds no transactions, fraud_pct_7d is NULL. Order the rows by day.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: PayTxn
| Column | Type |
|---|---|
| txn_id | int |
| txn_date | date |
| amount_usd | int |
| flagged_fraud | bool |
Primary key: txn_id.
One row per Apple Pay transaction; flagged_fraud is 1 when the transaction was later confirmed fraudulent.
Examples
Example 1
PayTxn
| txn_id | txn_date | amount_usd | flagged_fraud |
|---|---|---|---|
| 1 | 2025-03-01 | 40 | 0 |
| 2 | 2025-03-01 | 900 | 1 |
| 3 | 2025-03-02 | 25 | 0 |
| 4 | 2025-03-04 | 60 | 0 |
| 5 | 2025-03-04 | 15 | 0 |
| 6 | 2025-03-08 | 300 | 1 |
| 7 | 2025-03-17 | 75 | 0 |
Output
| day | txns_7d | fraud_pct_7d |
|---|---|---|
| 2025-03-01 | 2 | 50 |
| 2025-03-02 | 3 | 33.33 |
| 2025-03-03 | 3 | 33.33 |
| 2025-03-04 | 5 | 20 |
| 2025-03-05 | 5 | 20 |
| 2025-03-06 | 5 | 20 |
| 2025-03-07 | 5 | 20 |
| 2025-03-08 | 4 | 25 |
| 2025-03-09 | 3 | 33.33 |
| 2025-03-10 | 3 | 33.33 |
| 2025-03-11 | 1 | 100 |
| 2025-03-12 | 1 | 100 |
| 2025-03-13 | 1 | 100 |
| 2025-03-14 | 1 | 100 |
| 2025-03-15 | 0 | NULL |
| 2025-03-16 | 0 | NULL |
| 2025-03-17 | 1 | 0 |
How to solve Apple: Apple Pay Rolling Seven-Day Fraud Rate
A rolling seven-day window is only "seven rows" if there is one row per calendar day. The transactions have gaps, so the first step is a date spine: a recursive CTE that starts at MIN(txn_date) and adds a day with DATE_ADD until it reaches MAX(txn_date). LEFT JOINing the transactions onto it and grouping per day gives every day its count and its fraud count — zero on quiet days.
Over that gap-free daily table, SUM(…) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) adds the current day and the six before it: exactly the window day − 6 … day. Near the start the frame is simply shorter, which matches the definition because no transactions exist before the first day. A window over the raw transactions would be wrong twice: rows are transactions, not days, and gaps would stretch the window beyond a week.
The rate is 100 × frauds / txns, rounded to two places; after a quiet week the count is 0, and the CASE (or NULLIF in the alternative) yields NULL instead of dividing by zero. The alternative skips the window and counts each day's range with correlated subqueries — clearer, but each day rescans the transactions.
Reference solution (MySQL)
WITH RECURSIVE calendar AS (
SELECT MIN(txn_date) AS day FROM PayTxn HAVING MIN(txn_date) IS NOT NULL
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM calendar
WHERE day < (SELECT MAX(txn_date) FROM PayTxn)
), daily AS (
SELECT c.day, COUNT(t.txn_id) AS txns, COALESCE(SUM(t.flagged_fraud), 0) AS frauds
FROM calendar c
LEFT JOIN PayTxn t ON t.txn_date = c.day
GROUP BY c.day
), rolled AS (
SELECT day,
SUM(txns) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS txns_7d,
SUM(frauds) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS frauds_7d
FROM daily
)
SELECT day, txns_7d,
CASE WHEN txns_7d = 0 THEN NULL ELSE ROUND(100 * frauds_7d / txns_7d, 2) END AS fraud_pct_7d
FROM rolled
ORDER BY dayAnother way
WITH RECURSIVE calendar AS (
SELECT MIN(txn_date) AS day FROM PayTxn HAVING COUNT(*) > 0
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM calendar WHERE day < (SELECT MAX(txn_date) FROM PayTxn)
)
SELECT c.day,
(SELECT COUNT(*) FROM PayTxn t WHERE t.txn_date BETWEEN DATE_SUB(c.day, INTERVAL 6 DAY) AND c.day) AS txns_7d,
ROUND(100 * (SELECT SUM(t.flagged_fraud) FROM PayTxn t WHERE t.txn_date BETWEEN DATE_SUB(c.day, INTERVAL 6 DAY) AND c.day)
/ NULLIF((SELECT COUNT(*) FROM PayTxn t WHERE t.txn_date BETWEEN DATE_SUB(c.day, INTERVAL 6 DAY) AND c.day), 0), 2) AS fraud_pct_7d
FROM calendar c
ORDER BY c.day← Apple: Launch-Day Pre-Orders Filled Strictly in Queue Order · Dream11: Paid Public Contests Still Under Half Full →