SQL Anomaly Detection with Aggregations | Dataford Interview Questions

Your question is SQL Anomaly Detection with Aggregations. Start with the requirements and the three tables on the right.

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

Problem

Huntington's Data Analytics team is reviewing posted debit card activity for unusual daily transaction totals by branch. Write a PostgreSQL query to identify branch dates where the daily transaction amount is unusually high or low compared with that branch's observed daily average.

Requirements

Join branches, accounts, and card_transactions using their keys, and include only posted transactions from January 2025.
Aggregate transactions by branch and transaction date, returning transaction count and total amount.
Calculate each branch's average observed daily amount using a CTE.
Flag a day as high_amount when its total exceeds 150% of the branch average, or low_amount when it is below 50%. Return only anomalous days, ordered by branch and date.

Schema

branches

Column	Type	Description
branch_idPK	INT	Huntington branch identifier
branch_name	VARCHAR(100)	Branch name

accounts

Column	Type	Description
account_idPK	INT	Account identifier
branch_id	INT	Branch where the account was opened

card_transactions

Column	Type	Description
transaction_idPK	INT	Transaction identifier
account_id	INT	Account associated with the transaction
transaction_date	DATE	Transaction date
amount	DECIMAL(10,2)	Transaction amount
transaction_status	VARCHAR(20)	Transaction processing status