Compute age-band spend and YoY in Georgia
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
- 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.
- 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.
- 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.