Spending Velocity

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Goldman Sachs

Problem

A fraud model watches each customer's spending pace, so for every transaction we need a trailing total across that customer's seven most recent purchases up to and including it. Return user_id, transaction_date, total_amount, and that trailing total, laid out customer by customer from the lowest user id up, each customer's purchases running from earliest to latest, with the smaller amount first whenever two land on the same day.

Fraud hides in the pace, not the price. Watch each customer's last seven purchases roll forward.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this really is

Strip the fraud costume and this is a per-customer trailing-window sum. The skill being probed: can you attach a moving aggregate to every row without letting one customer's spending leak into another's, and can you cap that window at the last seven purchases instead of the whole history? Anyone can type SUM(total_amount) OVER (). Two things separate the candidates. Forget to scope by user_id and fifteen customers collapse into one shared total. Leave out the frame and you get a lifetime running total that never forgets; with 12 to 14 purchases per customer, every row from the eighth purchase on is wrong. Both mistakes return numbers that look reasonable and are quietly wrong.

ROWS, not RANGE

The prompt says seven most recent purchases, and the word purchases is the tell: you want the last seven rows, not the last seven calendar days. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts positions in the ordered partition. Had the requirement been seven days of history, you would reach for RANGE with a date interval instead, and in this data, where a customer's purchases usually sit weeks or months apart, that frame would mostly hold a single purchase. Read the unit before you pick the frame.

Lifetime running total (wrong)

SUM(total_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) with no frame. The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so every row sums all earlier spending for that customer, and same-day purchases are summed together as peers. The total only ever grows.

Trailing seven (right)

Add ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Now the window slides: once a customer has an eighth purchase, the oldest drops out and the sum reflects only recent pace, which is what a fraud signal needs.

The solution

SELECT
    user_id,
    transaction_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY transaction_date, total_amount
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM transactions
ORDER BY user_id, transaction_date, total_amount

Step 1: Anchor the window to the customer

PARTITION BY user_id restarts the sum for every customer. Without it the window runs across the whole table and one customer's spending contaminates the next customer's first rows.

Step 2: Order the frame, then bound it

ORDER BY transaction_date, total_amount inside OVER defines what earlier means. One customer in the seed has two purchases on the same day; ordered by the date alone, the engine picks which one enters the frame first, and their two trailing totals can swap. Adding total_amount makes the smaller one earlier, exactly as the statement lays them out. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW then limits each sum to seven purchases.

Step 3: Order the final output

The outer ORDER BY user_id, transaction_date, total_amount lays the result out customer by customer, earliest first. The window's ordering and the output's ordering are separate clauses: the first decides the numbers, the second decides how they are listed, and both need stating.

Common follow-up questions

• The requirement changes to a true rolling seven calendar days of spending. How does your frame clause change, and what breaks if you leave it as `ROWS`? (Tests the `ROWS` versus `RANGE` distinction and awareness that `RANGE` with a date interval is the calendar-day tool.)
• The seed's one same-day pair is settled by `total_amount`. If two same-day purchases could also share an amount, what would you add so the result stays deterministic? (Tests that a window ordering must end on a unique key such as `transaction_id` before frame membership is fixed.)
• Scaled to billions of rows, which operation dominates the cost, and how would partitioning or indexing help? (Tests ability to locate the sort as the bottleneck and reason about physical layout.)