weather-event-metrics.sql

select version();  -- DuckDB v1.0.0


/* https://www.linkedin.com/posts/constantin-lungu-668b8756_sql-bigquery-dataengineering-activity-7212478238265139201-dS4y */

create or replace table weather_alerts (
    city_id    int,
    alert_name varchar,
    valid_from date,
    valid_to   date,
);
insert into weather_alerts
values
    (1, 'Heatwave Alert',    '2020-12-30', '2020-12-31'),
    (1, 'Heatwave Alert',    '2021-01-01', '2021-01-02'),
    (1, 'Heavy Snow Alert',  '2021-07-01', '2021-07-02'),
    (2, 'Heavy Snow Alert',  '2020-12-26', '2020-12-27'),
    (2, 'Hale Alert',        '2021-01-01', '2021-01-02'),
    (2, 'Strong Wind Alert', '2021-03-01', '2021-03-02'),
;

create or replace table events (
    city_id    int,
    event_name varchar,
    event_date date,
);
insert into events
values
    (1, 'Pop Concert',     '2021-01-01'),
    (1, 'Football Game',   '2021-07-03'),
    (2, 'Rock Concert',    '2021-01-01'),
    (2, 'Basketball Game', '2021-03-03'),
;


/* lateral join */
select
    city_id,
    event_name,
    event_date,
    had_alert_before_or_after,
    had_heatwave_prior,
    count_alert_type_year_prior,
from events
    cross join lateral (
        select
            count_if(
                abs(event_date - alert.valid_to) <= 7
            ) as had_alert_before_or_after,
            count_if(
                alert.alert_name = 'Heatwave Alert'
                and alert.valid_to between event_date - interval '7 days' and event_date
            ) > 0 as had_heatwave_prior,
            count(
                distinct case when alert.valid_to between event_date - interval '1 year' and event_date
                    then alert.alert_name
                end
            ) as count_alert_type_year_prior
        from weather_alerts as alert
        where events.city_id = alert.city_id
    )
order by
    city_id,
    event_date
;

/* ...or just a left join */
select
    city_id,
    event_name,
    event_date,
    count_if(
        abs(event_date - weather_alerts.valid_to) <= 7
    ) as had_alert_before_or_after,
    count_if(
        weather_alerts.alert_name = 'Heatwave Alert'
        and weather_alerts.valid_to between event_date - interval '7 days' and event_date
    ) > 0 as had_heatwave_prior,
    count(
        distinct case when weather_alerts.valid_to between event_date - interval '1 year' and event_date
            then weather_alerts.alert_name
        end
    ) as count_alert_type_year_prior
from events
    left join weather_alerts
        using (city_id)
group by
    city_id,
    event_name,
    event_date
order by
    city_id,
    event_date
;