Compute age-band spend and YoY in Georgia
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Using ANSI SQL only, answer the three parts below on the following schema and samples. Assume dates are YYYY-MM-DD, paid_amt can be negative (adjustments) and should be included; exclude rows with NULL paid_amt. Treat Georgia as state='GA'.
Schema
- mem(mem_id INT PRIMARY KEY, age_band VARCHAR(10), state CHAR(2))
- claim(claim_id INT PRIMARY KEY, mem_id INT, claim_date DATE, paid_amt DECIMAL(10,2))
- geo(state CHAR(2) PRIMARY KEY, state_name VARCHAR(50))
Sample rows (minimal toy data)
mem
+--------+----------+-------+
| mem_id | age_band | state |
+--------+----------+-------+
| 1 | 18-43 | GA |
| 2 | 44-65 | GA |
| 3 | 65+ | GA |
| 4 | 44-65 | FL |
+--------+----------+-------+
claim
+----------+--------+------------+----------+
| claim_id | mem_id | claim_date | paid_amt |
+----------+--------+------------+----------+
| 10 | 1 | 2019-12-15 | 120.00 |
| 11 | 1 | 2020-05-10 | 80.00 |
| 12 | 2 | 2020-02-01 | 200.00 |
| 13 | 2 | 2019-03-15 | 100.00 |
| 14 | 3 | 2020-07-20 | 300.00 |
| 15 | 3 | 2019-07-20 | 250.00 |
| 16 | 4 | 2020-11-01 | 90.00 |
+----------+--------+------------+----------+
geo
+-------+-----------+
| state | state_name|
+-------+-----------+
| GA | Georgia |
| FL | Florida |
+-------+-----------+
Tasks
1) Return the 2020 sum of paid_amt by age_band across all states, including age bands with zero 2020 spend (show 0.00). Columns: age_band, paid_amt_sum_2020. Hint: decide whether to filter claims before/after joining to avoid dropping age bands with no 2020 claims.
2) From (1), compute each age_band's percentage share of the 2020 total across all bands. Columns: age_band, pct_of_total_2020 (0–100 with two decimals). Ensure division-by-zero safety.
3) For Georgia only (state='GA'), compute 2019 vs 2020 totals and year-over-year change for age bands IN ('44-65','65+'). Output: age_band, paid_amt_2019, paid_amt_2020, yoy_abs_change (2020-2019), yoy_pct_change ((2020-2019)/NULLIF(2019,0)). Exclude members not residing in GA, even if they have claims elsewhere. Clearly state your join types and any assumptions about members without claims.
Quick Answer: This question evaluates SQL data-manipulation competency including aggregations, percent-share calculations, year-over-year comparisons, join strategies, and handling NULLs and zero-value groups.