SQL With Complex Joins and Aggregations

HardSQL · PostgreSQL

Problem

OptumRx needs a member-level view of paid prescription utilization for the first quarter of 2025. Write a PostgreSQL query that combines member, prescription, claim, and pharmacy data.

Requirements

Include only active members and paid claims dated from 2025-01-01 through 2025-03-31.
Return each qualifying member's total paid amount, paid claim count, distinct medication count, latest paid claim date, and largest gap in days between consecutive paid claims.
Identify the pharmacy with the highest paid claim spend for each member, using a window function to rank pharmacies.
Keep members with at least two paid claims and total paid amount of at least 100. Treat missing pharmacy matches as Unknown and missing monetary values as zero during aggregation.
Sort results by total paid amount descending and member ID ascending.

Schema

members

Column	Type	Description
member_idPK	INT	OptumRx member identifier
member_name	VARCHAR(100)	Member display name
is_active	BOOLEAN	Whether the member is currently active

prescriptions

Column	Type	Description
prescription_idPK	INT	Prescription identifier
member_id	INT	References members.member_id
drug_name	VARCHAR(100)	Medication name

claims

Column	Type	Description
claim_idPK	INT	Prescription claim identifier
prescription_id	INT	References prescriptions.prescription_id
pharmacy_id	INT	References pharmacies.pharmacy_id when available
claim_date	DATE	Date the claim was processed
claim_status	VARCHAR(20)	Claim adjudication status
paid_amount	NUMERIC(10,2)	Amount paid for the claim

pharmacies

Column	Type	Description
pharmacy_idPK	INT	Pharmacy identifier
pharmacy_name	VARCHAR(120)	Pharmacy or dispensing channel name

Tablesmembersprescriptionsclaimspharmacies