Skip to content
Quantoros

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

Related questions