insert into {{ rolling_wau_table_name }} (
    p_date,
    n_active_users,
    n_nonbot_active_users,
    n_7d_active_users,
    n_nonbot_7d_active_users,
    n_30d_active_users,
    n_nonbot_30d_active_users
)

with

date_spine as (
    select
        date_actual as p_date
    from
        suno_prod.prod.dim_date
    where 1=1
        and date_actual >= '{{ partition_start_date }}'::date - interval '29 days'
        and date_actual < '{{ partition_end_date }}'::date
)

, user_day_active as (
    select
        u.user_id
        , u.user_uid
        , coalesce(bh.meets_bot_definition, false) as is_suspected_bot
        , p_date
    from
        suno_prod.prod_marts.active_users_daily u
    left join
        suno_prod.prod.bot_hourly bh
        on bh.user_id = u.user_id
    where 1=1
        and is_active
        and p_date >= '{{ partition_start_date }}'::date - interval '29 days'
        and p_date < '{{ partition_end_date }}'::date
)

, daily_metrics as (
    select
        ds.p_date
        , count(distinct case when uda.p_date = ds.p_date then uda.user_id end) as n_active_users
        , count(distinct case when uda.p_date = ds.p_date and not uda.is_suspected_bot then uda.user_id end) as n_nonbot_active_users
        , count(distinct case when uda.p_date between dateadd(day, -6, ds.p_date) and ds.p_date then uda.user_id end) as n_7d_active_users
        , count(distinct case when uda.p_date between dateadd(day, -6, ds.p_date) and ds.p_date and not uda.is_suspected_bot then uda.user_id end) as n_nonbot_7d_active_users
        , count(distinct case when uda.p_date between dateadd(day, -29, ds.p_date) and ds.p_date then uda.user_id end) as n_30d_active_users
        , count(distinct case when uda.p_date between dateadd(day, -29, ds.p_date) and ds.p_date and not uda.is_suspected_bot then uda.user_id end) as n_nonbot_30d_active_users
    from
        date_spine ds
    left join
        user_day_active uda
        on uda.p_date between dateadd(day, -29, ds.p_date) and ds.p_date
    group by
        ds.p_date
)

select
    p_date
    , n_active_users
    , n_nonbot_active_users
    , n_7d_active_users
    , n_nonbot_7d_active_users
    , n_30d_active_users
    , n_nonbot_30d_active_users
from
    daily_metrics;
