Trend Spotter

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

L4

Problem

Every row of transactions is one purchase, stamped with the user who made it and its transaction_date. Alongside each purchase's user, amount and date, show what the same user spent on the purchase just before it in time, left empty when there is none.

What did they spend last time? Context changes everything.

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

This is a per-user look-back dressed up as a feature request. Everyone hears 'previous amount' and reaches for a look-back. What separates candidates is the two scopes on it: the look-back must stay inside one user, and 'previous' must mean previous in transaction_date, not in transaction_id. Miss the first and each buyer's first purchase quietly inherits the last amount of whoever sorts before them instead of staying empty. Miss the second and you pair purchases with the wrong neighbors, because on this table ids and dates do not move together.

Getting there

Step 1: Scope the look-back to one user

Every row stays (200 in, 200 out), so this is not a collapse into one row per user. It is a per-row lookup into the same user's history. PARTITION BY user_id is the fence: without it, the row before a buyer's first purchase belongs to somebody else.

Step 2: Order each history by transaction_date

It is tempting to treat transaction_id as time. Here it is not. User 100's original purchases have ids 2005, 3010 and 4015, but their dates run July, April, January. The backfilled rows carry higher ids still, yet land in earlier years. Only ORDER BY transaction_date inside the window yields the purchase that really came just before.

Step 3: Leave the first purchase empty

LAG returns NULL when nothing precedes the row in its partition, which is exactly the empty first row the preview shows. Resist wrapping it in COALESCE(..., 0). A zero reads as a real purchase, and this table already holds a voided sale recorded as 0.0, so the model could no longer tell 'no history' from 'spent nothing'.

SELECT
    user_id,
    total_amount,
    transaction_date,
    LAG(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY transaction_date
    ) AS prev_amount
FROM transactions

SELECT
    user_id,
    total_amount,
    transaction_date,
    LAG(total_amount) OVER (
        ORDER BY user_id, transaction_date
    ) AS prev_amount
FROM transactions

Common follow-up questions

• How would you compute the change in amount from one purchase to the next? (Subtract the look-back, `total_amount - LAG(total_amount) OVER (...)`, and decide what a first purchase's change should be.)
• What if a user makes two purchases on the same `transaction_date`? (Without a tiebreaker the order inside that day is arbitrary; add `transaction_id` to the window's `ORDER BY`.)
• How would you look back two purchases instead of one? (`LAG(total_amount, 2)` shifts by two rows within the same user.)
• How would you add the days since the user's previous purchase? (Take `LAG(transaction_date)` over the same window, then compute a date difference.)