-- 假设 epidemiology.csv 和 demographics.csv 已下载到同目录
-- epidemiology.csv 字段: date, country_code, new_confirmed, new_deceased
-- demographics.csv 字段: country_code, population

WITH last30 AS (
    -- 读取原始指标数据，duckdb会自动识别首行和 schema
    SELECT location_key, new_confirmed, new_deceased
    FROM './epidemiology.csv'
    WHERE date between STRPTIME('2021-05-01', '%Y-%m-%d') - INTERVAL 30 DAY and STRPTIME('2021-05-01', '%Y-%m-%d')
),
agg30 AS (
    -- 统计每个城市的确诊、死亡人数
    SELECT
        location_key,
        SUM(new_confirmed)  AS new_confirmed_30d,
        SUM(new_deceased)   AS new_deceased_30d
    FROM last30
    GROUP BY location_key
),
pop AS (
    -- 读取原始维度数据，duckdb会自动识别首行和 schema
    SELECT location_key, population
    FROM './demographics.csv'
)
SELECT
    p.location_key,
    -- 每百万人口新增确诊
    ROUND(new_confirmed_30d / population * 1000000, 2) AS confirmed_per_million,
    -- 死亡率
    ROUND(CAST(new_deceased_30d AS DOUBLE) / NULLIF(new_confirmed_30d, 0), 4) AS fatality_rate,
    -- 原始 30 天新增确诊
    new_confirmed_30d
FROM agg30 a
JOIN pop p on a.location_key = p.location_key
WHERE population > 0
ORDER BY confirmed_per_million DESC
LIMIT 10;

SELECT *
FROM read_parquet('orders/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2021 and month = 1;