Outlier Transactions by Amount | Dataford Interview Questions

Write a SQL query to identify outlier transactions by amount.

Treat an outlier as a transaction below Q1 minus 1.5 times the interquartile range or above Q3 plus 1.5 times the interquartile range. Calculate thresholds from non-null transaction amounts.

Output

One row per outlier transaction with transaction_id, amount, lower_bound, and upper_bound.
Sort by amount descending, then transaction_id ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
merchant_name	VARCHAR(100)	Merchant associated with the transaction
transaction_type	VARCHAR(30)	Transaction classification
amount	DECIMAL(12,2)	Transaction amount, including possible credits or reversals

Your question is Outlier Transactions by Amount. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem