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 |