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

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.

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.

Table mem

column	type	constraints
mem_id	INT	PRIMARY KEY, NOT NULL
age_band	VARCHAR(10)	NOT NULL
state	CHAR(2)	NOT NULL

Sample data:

age_band	mem_id	state
18-43	1	GA
44-65	2	GA
65+	3	GA
44-65	4	FL

Table claim

column	type	constraints
claim_id	INT	PRIMARY KEY, NOT NULL
mem_id	INT	NOT NULL
claim_date	DATE	NOT NULL
paid_amt	DECIMAL(10,2)

Sample data:

claim_date	claim_id	mem_id	paid_amt
2019-12-15	10	1	120
2020-05-10	11	1	80
2020-02-01	12	2	200
2019-03-15	13	2	100
2020-07-20	14	3	300
2019-07-20	15	3	250
2020-11-01	16	4	90

Table geo

column	type	constraints
state	CHAR(2)	PRIMARY KEY, NOT NULL
state_name	VARCHAR(50)	NOT NULL

Sample data:

state	state_name
GA	Georgia
FL	Florida

Expected output

One row per age_band with the total 2020 paid_amt summed across all members and states. Age bands with no 2020 claims should appear with 0.00. Based on the sample data, all age bands have 2020 spend.

age_band	paid_amt_sum_2020
18-43	80
44-65	290
65+	300

Hints

1. Start from mem to get all age_band values, then LEFT JOIN to claim.
2. 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.

Table mem

column	type	constraints
mem_id	INT	PRIMARY KEY, NOT NULL
age_band	VARCHAR(10)	NOT NULL
state	CHAR(2)	NOT NULL

Sample data:

age_band	mem_id	state
18-43	1	GA
44-65	2	GA
65+	3	GA
44-65	4	FL

Table claim

column	type	constraints
claim_id	INT	PRIMARY KEY, NOT NULL
mem_id	INT	NOT NULL
claim_date	DATE	NOT NULL
paid_amt	DECIMAL(10,2)

Sample data:

claim_date	claim_id	mem_id	paid_amt
2019-12-15	10	1	120
2020-05-10	11	1	80
2020-02-01	12	2	200
2019-03-15	13	2	100
2020-07-20	14	3	300
2019-07-20	15	3	250
2020-11-01	16	4	90

Table geo

column	type	constraints
state	CHAR(2)	PRIMARY KEY, NOT NULL
state_name	VARCHAR(50)	NOT NULL

Sample data:

state	state_name
GA	Georgia
FL	Florida

Expected output

One row per age_band with its percentage contribution to the total 2020 paid_amt across all age bands and states. Percentages are 0–100 with two decimals. Based on the sample data, the total 2020 spend is 670.00.

age_band	pct_of_total_2020
18-43	11.94
44-65	43.28
65+	44.78

Hints

1. Reuse the age-band 2020 sums (from part 1) in a CTE or subquery, then divide by the grand total.
2. 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.

Table mem

column	type	constraints
mem_id	INT	PRIMARY KEY, NOT NULL
age_band	VARCHAR(10)	NOT NULL
state	CHAR(2)	NOT NULL

Sample data:

age_band	mem_id	state
18-43	1	GA
44-65	2	GA
65+	3	GA
44-65	4	FL

Table claim

column	type	constraints
claim_id	INT	PRIMARY KEY, NOT NULL
mem_id	INT	NOT NULL
claim_date	DATE	NOT NULL
paid_amt	DECIMAL(10,2)

Sample data:

claim_date	claim_id	mem_id	paid_amt
2019-12-15	10	1	120
2020-05-10	11	1	80
2020-02-01	12	2	200
2019-03-15	13	2	100
2020-07-20	14	3	300
2019-07-20	15	3	250
2020-11-01	16	4	90

Table geo

column	type	constraints
state	CHAR(2)	PRIMARY KEY, NOT NULL
state_name	VARCHAR(50)	NOT NULL

Sample data:

state	state_name
GA	Georgia
FL	Florida

Expected output

One row per Georgia age_band in ('44-65','65+') with 2019 and 2020 totals, absolute change, and percentage change. Only Georgia residents are included; Florida member claims are excluded.

age_band	paid_amt_2019	paid_amt_2020	yoy_abs_change	yoy_pct_change
44-65	100	200	100	1
65+	250	300	50	0.2

Hints

1. Filter to Georgia members and the required age bands in the mem table, then LEFT JOIN to claim so members without claims are retained.
2. 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.