The Ghost Ledger

4pts

Fintech, Stripe, Adyen

Problem Statement

You are auditing a legacy system where the transaction_id was lost during a database migration. All you have is the user, the amount, and the time.

The Challenge

Calculate a running total of purchase amounts for each user, ordered by time.

The Trap

Because there is no primary key, multiple transactions from the same user might have the exact same timestamp. Your query must treat these as separate entries and update the running total for each row individually, rather than jumping the total all at once.

Rules:

Result columns: user_id, amount, transaction_time, running_total.
Even if timestamps are identical, the running_total must increase row-by-row.

Tests your understanding of

Window Functions, ROWS vs RANGE and Finance

Input Tables

purchases

user_id(INTEGER)	amount(DECIMAL)	transaction_time(TIMESTAMP)
1	100	2026-01-01 10:00:00
1	50	2026-01-01 11:00:00
1	25	2026-01-01 11:00:00
2	200	2026-01-01 10:00:00

Expected Output

user_id(INTEGER)	amount(DECIMAL)	transaction_time(TIMESTAMP)	running_total(DECIMAL)
1	100	2026-01-01 10:00:00	100
1	50	2026-01-01 11:00:00	150
1	25	2026-01-01 11:00:00	175
2	200	2026-01-01 10:00:00	200