SQL for Throughput Declines | Dataford Interview Questions

How would you use SQL to identify the top 5 branches with the largest month-over-month decline in throughput?

Use the latest month present in the data as the comparison month. Include only branches with throughput recorded for both the latest month and its immediately preceding calendar month.

Output

One row per qualifying branch, with branch_id, branch_name, current_month, previous_total, current_total, and decline
Return the five largest positive declines, ordered by decline descending and branch_id ascending for ties

Schema

branches

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

throughput_records

Column	Type	Description
record_idPK	INT	Unique throughput record identifier
branch_id	INT	Branch associated with the throughput record
recorded_at	TIMESTAMPTZ	Timestamp when throughput was recorded
throughput_units	INT	Throughput units recorded during the event

Your question is SQL for Throughput Declines. Start with the requirements and the two tables 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