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.
Overview: 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.
Sum 2020 paid amounts by age band across all states
Using only ANSI SQL, return the 2020 sum of paid_amt by age_band across all states. Include age bands with zero 2020 spend and show them as 0.00. Exclude rows where paid_amt is NULL, but include negative paid_amt values (adjustments). Use the schema and sample data below.
Output columns:
- age_band
- paid_amt_sum_2020
Hint: Think carefully about whether to filter claims before or after joining so that age bands with no 2020 claims are not dropped.
Tables
mem(mem_id INT, age_band VARCHAR(10), state CHAR(2))
claim(claim_id INT, mem_id INT, claim_date DATE, paid_amt DECIMAL(10,2))
geo(state CHAR(2), state_name VARCHAR(50))
Hints
- Start from mem to get all age_band values, then LEFT JOIN to claim.
- Filter the 2020 date range and NULL paid_amt in the JOIN condition so that age bands without 2020 claims are not filtered out.
Compute 2020 age-band percentage share of total spend
Using only ANSI SQL and building on the logic from part (1), compute each age_band's percentage share of the 2020 total paid_amt across all bands and states. Exclude rows where paid_amt is NULL, but include negative paid_amt values (adjustments). Use the same schema and sample data.
Output columns:
- age_band
- pct_of_total_2020 (0–100 with two decimal places)
Ensure that your solution is safe if the 2020 total is zero (avoid division-by-zero). You may reuse part (1) via a CTE or subquery.
Tables
mem(mem_id INT, age_band VARCHAR(10), state CHAR(2))
claim(claim_id INT, mem_id INT, claim_date DATE, paid_amt DECIMAL(10,2))
geo(state CHAR(2), state_name VARCHAR(50))
Hints
- Reuse the age-band 2020 sums (from part 1) in a CTE or subquery, then divide by the grand total.
- Use NULLIF or an explicit CASE around the denominator to avoid division by zero when the total is 0.
Georgia age-band 2019 vs 2020 spend and YoY change
Using only ANSI SQL, and treating Georgia as state = 'GA', compute 2019 vs 2020 paid_amt totals and year-over-year change for Georgia members in age bands IN ('44-65','65+'). Exclude members not residing in GA, even if they have claims elsewhere. Exclude claims where paid_amt is NULL, but include negative paid_amt values (adjustments).
Output columns:
- age_band
- paid_amt_2019 (sum for 2019-01-01 to 2019-12-31)
- paid_amt_2020 (sum for 2020-01-01 to 2020-12-31)
- yoy_abs_change (paid_amt_2020 - paid_amt_2019)
- yoy_pct_change ((paid_amt_2020 - paid_amt_2019) / NULLIF(paid_amt_2019, 0))
Clearly choose your join types and assume that Georgia members in the specified age bands should appear even if they have no claims in 2019 or 2020 (in that case, show 0.00 for the sums and NULL for the percentage change). Use the schema and sample data below.
Tables
mem(mem_id INT, age_band VARCHAR(10), state CHAR(2))
claim(claim_id INT, mem_id INT, claim_date DATE, paid_amt DECIMAL(10,2))
geo(state CHAR(2), state_name VARCHAR(50))
Hints
- Filter to Georgia members and the required age bands in the mem table, then LEFT JOIN to claim so members without claims are retained.
- Use conditional aggregation (CASE inside SUM) to separate 2019 and 2020 totals, then compute the YoY metrics in an outer query so you can reference the aggregated year totals.