Apple: Apple Pay Rolling Seven-Day Fraud Rate

Hard
622.

Apple: Apple Pay Rolling Seven-Day Fraud Rate

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

ColumnType
txn_idint
txn_datedate
amount_usdint
flagged_fraudbool

Primary key: txn_id.

One row per Apple Pay transaction; flagged_fraud is 1 when the transaction was later confirmed fraudulent.

Example

The rows must come back in the order shown.

PayTxn

txn_idtxn_dateamount_usdflagged_fraud
12025-03-01400
22025-03-019001
32025-03-02250
42025-03-04600
52025-03-04150
62025-03-083001
72025-03-17750

Output

daytxns_7dfraud_pct_7d
2025-03-01250
2025-03-02333.33
2025-03-03333.33
2025-03-04520
2025-03-05520
2025-03-06520
2025-03-07520
2025-03-08425
2025-03-09333.33
2025-03-10333.33
7 more rows
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
txn_idint
txn_datedate
amount_usdint
flagged_fraudbool

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_idtxn_dateamount_usdflagged_fraud
12025-03-01400
22025-03-019001
32025-03-02250
42025-03-04600
52025-03-04150
62025-03-083001
72025-03-17750

Output

daytxns_7dfraud_pct_7d
2025-03-01250
2025-03-02333.33
2025-03-03333.33
2025-03-04520
2025-03-05520
2025-03-06520
2025-03-07520
2025-03-08425
2025-03-09333.33
2025-03-10333.33
2025-03-111100
2025-03-121100
2025-03-131100
2025-03-141100
2025-03-150NULL
2025-03-160NULL
2025-03-1710

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 day

Another 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 →