Your question is SQL Pharmacy Spend Per Member. Start with the requirements and the two 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.
Lockton Companies needs a monthly view of pharmacy costs for members covered during each claim service date. Write a PostgreSQL query that joins pharmacy claims to enrollment periods and calculates total paid pharmacy spend per member per month.
claim_type = 'PHARMACY' and claim_status = 'PAID'.service_date falls within the member's enrollment period. Treat coverage_end as exclusive; a NULL end date means coverage is ongoing.0.00 when all matching paid amounts are NULL.member_id and month.| Column | Type | Description |
|---|---|---|
| claim_idPK | INT | Unique claim identifier |
| member_id | VARCHAR(10) | Member identifier |
| service_date | DATE | Date the prescription was filled |
| claim_type | VARCHAR(20) | Claim category |
| claim_status | VARCHAR(20) | Processing status |
| paid_amount | NUMERIC(10,2) | Amount paid for the claim |
| Column | Type | Description |
|---|---|---|
| enrollment_idPK | INT | Unique enrollment record |
| member_id | VARCHAR(10) | Enrolled member identifier |
| coverage_start | DATE | Inclusive coverage start date |
| coverage_end | DATE | Exclusive coverage end date, or null for ongoing coverage |