Drill 7 of 12 in Healthcare questions
0

Interview Question Bank · age() · CASE buckets · EXISTS

7. Flu vaccination coverage by age band

MediumHealthcareQuestion #139

Concept: age() · CASE buckets · EXISTSshow

age(as_of, birth_date) returns an interval that accounts for whether the birthday has happened yet, so EXTRACT(YEAR FROM age(...)) is the real age. Subtracting birth years is wrong for everyone whose birthday is later in the year. Bucket ages with CASE, and test 'has at least one matching row' with EXISTS, so people with no rows stay in the denominator and people with two rows aren't counted twice.

Example (different data):

SELECT CASE WHEN tenure < 1 THEN '<1y' WHEN tenure < 5 THEN '1-4y' ELSE '5y+' END AS band,
  COUNT(*) AS staff,
  COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM trainings t WHERE t.staff_id = s.id)) AS trained
FROM (SELECT id, EXTRACT(YEAR FROM age(DATE '2025-01-01', hired_on)) AS tenure FROM staff) s
GROUP BY 1 ORDER BY 1;

Your task

Larkspur Health's public-health team reports flu vaccine coverage for the 2024-25 season (shots given 2024-09-01 through 2025-03-31) by age band, using each patient's age on 2025-01-01.

Return age_band ('0-17', '18-49', '50-64' or '65+'), patients (all patients in the band), vaccinated (patients with at least one 'flu' shot in the season) and coverage_pct (vaccinated as a % of patients, rounded to 1 decimal). Sort by age_band.

Starting Postgres in your browser…

Checking your access…