avg_age.sql

-- Given the data at https://gist.github.com/chalu/da81ea79bc95292e5c892621b002fe8f
-- how would you calculate the average age of the seniors.
-- I use the following ChatGPT prompt to get the formula for calculating their average age
-- "Given some age ranges like 45 - 54 and the number of people in each age range, how would you calculate the average age across all the age ranges"

-- I then wrote the below SQL

WITH age_range AS (
  -- count seniors within each age range
  SELECT SUM(
    "Age_45-54" 
  ) AS age_45_54_total,
  SUM(
    "Age_55-64" 
  ) AS age_55_64_total,
  SUM(
    "Age_65-74" 
  ) AS age_65_74_total,
  SUM(
    "Age_75+" 
  ) AS age_75_total 
  from "seniors"
),

weighted_age AS (
  -- compute the weighted ages for each age range
  SELECT (49.5 * t1.age_45_54_total) as weighted_45_54,
       (59.5 * t1.age_55_64_total) as weighted_55_64,
       (69.5 * t1.age_65_74_total) as weighted_65_74,
       (75 * t1.age_75_total) as weighted_75
  FROM age_range as t1
),

weighted_age_sum AS (
  -- sum the weighted ages across the age ranges
  SELECT SUM(
		weighted_45_54 
  		+ weighted_55_64 
  		+ weighted_65_74 
  		+ weighted_75
  ) as age_sum
  FROM weighted_age
),

everyone AS (
  -- compute the total count of all the seniors
  SELECT SUM (
    age_45_54_total
    + age_55_64_total
    + age_65_74_total
    + age_75_total
  ) as total
  FROM age_range
) 

-- compute sum_of_weighted_age / total_seniors_count
SELECT round(
  age_sum / (
    SELECT total FROM everyone
  ),
  0
) AS agv_age
FROM weighted_age_sum;