For each account with at least 2 transactions, return accountid and the amount of its SECOND-largest transaction.…
SQLLevel: IntermediateBonus drills (mixed difficulty)
For each account with at least 2 transactions, return account_id and the amount of its SECOND-largest transaction. Break amount ties by lower transaction_id first.
Table definitions (from the source exercise):
CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
Hints
- Write an interleaving of two concurrent operations.
- Which observations does the isolation level permit, and what happens on a mid-operation failure?
Answer
Approach: Same ROW_NUMBER pattern as top-1, but keep rn = 2. Accounts with fewer than 2 transactions drop out naturally.
SELECT account_id, amount AS second_largest
FROM (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY account_id
ORDER BY amount DESC, transaction_id) AS rn
FROM transactions t
) x
WHERE rn = 2;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT