Optimizing SQL Query Performance | Dataford Interview Questions

How do you optimize a SQL query for performance in CVS data systems?

Using the supplied CVS pharmacy and prescription claim tables, write a PostgreSQL query that returns paid claims for active stores during the first quarter of 2025. Keep the result at the state and calendar-month grain.

Output

Columns: state, claim_month, paid_claim_count, total_reimbursement, and average_reimbursement
Include only paid claims from active stores within the specified date range
Sort by state ascending, then claim_month ascending

Schema

stores

Column	Type	Description
store_idPK	INT	Unique CVS store identifier
store_name	VARCHAR(100)	CVS store name
state	VARCHAR(2)	Two-letter state code
is_active	BOOLEAN	Whether the store is currently active

prescription_claims

Column	Type	Description
claim_idPK	INT	Unique prescription claim identifier
store_id	INT	CVS store associated with the claim
claim_date	DATE	Date the claim was submitted
claim_status	VARCHAR(20)	Claim processing status
reimbursement_amount	NUMERIC(10,2)	Reimbursement amount for the claim

Your question is Optimizing SQL Query Performance. 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