Return each merchantcategory whose largest single transaction is more than 3 times its average transaction, with both…
SQLLevel: IntermediateBonus drills (mixed difficulty)
Return each merchant_category whose largest single transaction is more than 3 times its average transaction, with both numbers (max and average, average rounded to 2 decimals).
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: Two aggregates in one HAVING: MAX(amount) > 3 * AVG(amount).
SELECT merchant_category, MAX(amount) AS max_amt,
ROUND(AVG(amount), 2) AS avg_amt
FROM transactions
GROUP BY merchant_category
HAVING MAX(amount) > 3 * AVG(amount);
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