Tell me about your experience writing SQL queries for healthcare scenarios at Oscar Health, including how you handle joins, filters, and aggregations.
Using the supplied member, claim, and provider data, return a claims summary for active members and qualifying 2025 paid claims.
Output
- One row per active member, including members without qualifying claims.
- Return
member_id, member_name, state, paid_claim_count, total_paid_amount, distinct_provider_count, and spend_band.
- Order by
total_paid_amount descending, then member_id ascending.
Output details
- Restrict claims to service dates from
2025-01-01 inclusive through 2026-01-01 exclusive, with claim_status exactly PAID.
- Use
0 for total paid amount and counts when an active member has no qualifying claims.
- Count only providers whose
provider_id matches a qualifying claim's provider, distinctly.
- Classify spend as
High for totals at least 1000, Moderate for totals at least 500 but below 1000, and Low otherwise.
