Skip to content
Quantoros

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

Related questions