Optimizing Slow SQL Queries | Dataford Interview Questions

How do you approach optimizing slow-running SQL queries and stored procedures in large-scale relational databases?

Using the provided PostgreSQL tables, write an optimized query that identifies active accounts with at least 100 billable usage units during 2025. The query should avoid unnecessary row multiplication and preserve accounts even when plan details are missing.

Output

One row per qualifying active account with account_id, account_name, plan_name, and total_billable_units
Include only accounts with at least 100 billable units during 2025
Sort by total_billable_units descending, then account_id ascending

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account display name
status	VARCHAR(20)	Current account status
plan_id	INT	Referenced subscription plan
created_at	DATE	Account creation date

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
plan_name	VARCHAR(80)	Subscription plan name
monthly_fee	DECIMAL(10,2)	Monthly plan fee
is_active	BOOLEAN	Whether the plan is currently offered

usage_events

Column	Type	Description
event_idPK	INT	Unique usage event identifier
account_id	INT	Account associated with the event
occurred_at	DATE	Date when usage occurred
event_type	VARCHAR(20)	Usage classification
units	INT	Number of usage units

Your question is Optimizing Slow SQL Queries. Start with the requirements and the three 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