# Spending Velocity > Fraud hides in the pace, not the price. Watch each customer's last seven purchases roll forward. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## 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. Walk each customer's transactions from earliest to latest and return user_id, transaction_date, total_amount, and that trailing total. ## 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 each row without letting one customer's spending leak into another's, and can you bound that window to the last seven transactions instead of the entire history? Anyone can type SUM(total_amount) OVER (). Two things separate the candidates. Forget to partition by user_id and every customer collapses into one shared total. Omit the frame clause and you get an unbounded running total that never forgets, not the trailing window the prompt asks for. Both mistakes return numbers that look reasonable and are quietly wrong. > **Trick to solving** > > Three pieces have to line up: PARTITION BY user_id keeps each customer independent, ORDER BY transaction_date walks the window forward in time, and ROWS BETWEEN 6 PRECEDING AND CURRENT ROW caps it at seven transactions (the current row plus the six before it). --- ### 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 sit months apart, that frame would sum almost nothing. Read the unit before you pick the frame. **Unbounded running total (wrong)** SUM(...) 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 prior spending for that customer. 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 **Trailing seven-transaction total per customer** ```sql SELECT user_id, transaction_date, total_amount, SUM(total_amount) OVER (PARTITION BY user_id ORDER BY transaction_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_sum FROM transactions ORDER BY user_id, transaction_date ``` #### Step 1: Anchor the window to the customer PARTITION BY user_id computes the aggregate independently per customer. Without it, SQL treats every row as one group and one customer's spending contaminates the next. #### Step 2: Bound and order the frame ORDER BY transaction_date inside the OVER clause defines what recent means, and ROWS BETWEEN 6 PRECEDING AND CURRENT ROW limits each sum to seven transactions. Drop the frame and it silently becomes a lifetime running total. #### Step 3: Order the final output The outer ORDER BY user_id, transaction_date groups each customer's rows together and walks them chronologically, matching the expected layout where every user_id block appears contiguously. The window's internal ordering and the output ordering are two separate clauses. Both need stating. > **Interviewers watch for** > > Whether you name the frame out loud. A candidate who writes ROWS BETWEEN ... and can explain why the default RANGE frame would be wrong here signals real understanding of window frames rather than pattern-matching a template. > **Common pitfall** > > Leaning on the default frame. SUM() OVER (ORDER BY ...) with no ROWS or RANGE clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, an unbounded running total. It runs, it returns plausible numbers, and it answers a different question. > **Cost analysis** > > At 120M rows the whole table feeds the window operator; there is no filter to prune it. The cost driver is the sort: the engine must order rows by (user_id, transaction_date) before it can slide the frame. A composite index on (user_id, transaction_date) lets the planner stream rows already in window order and skip the explicit sort, which is the single biggest win at this scale. --- ## 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.)_ - Two of a customer's transactions fall on the same date. How does that affect which rows land in the window, and how would you make the result deterministic? _(Tests understanding that ORDER BY ties make frame membership nondeterministic and that a tiebreaker column is needed.)_ - 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/spending_velocity) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.