{%- set incremental_predicate = partition_filter_daily('DBT_INTERNAL_DEST.p_date') -%}

{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        incremental_predicates=[incremental_predicate],
        unique_key='p_date',
        cluster_by=['p_date'],
        snowflake_warehouse='SUNO_PROD_LARGE',
    )
}}

-- Unified Active User Metrics table with DAU, WAU, and MAU by platform and subscription tier
-- Shows all rolling window metrics for every single day with complete date spine
-- Uses active_users_daily dbt model as input (Dagster-managed dependency)
--
-- STRUCTURE: Scalar columns with bot/nonbot breakdown
-- Each metric has separate columns for bot and nonbot users
-- Provides full visibility into bot activity across all dimensions

WITH date_spine AS (
    SELECT date_actual AS p_date
    FROM {{ ref('dim_date') }}
    WHERE {{ partition_filter_daily('date_actual') }}
)

-- Get subscription tiers as of the partition date
-- Filter to single date upfront for efficiency
, user_subscription_on_partition_date AS (
    SELECT
        user_id,
        p_date,
        subscription_tier
    FROM {{ ref('user_subscription_tier_daily') }}
    WHERE 1=1
        AND p_date >= '{{ var("partition_start_date") }}'::date
        AND p_date < '{{ var("partition_end_date") }}'::date
)

-- Get active users for the past 29 days (including current date)
-- Need to look back 29 days to create 30-day MAU window
, user_activity_window AS (
    SELECT
        au.user_id,
        au.user_uid,
        COALESCE(bots.meets_bot_definition, FALSE) AS is_suspected_bot,
        au.p_date,
        au.is_active_web,
        au.is_active_ios,
        au.is_active_android,
        ust.subscription_tier
    FROM {{ ref('active_users_daily') }} au
    LEFT JOIN {{ source('snowflake', 'bot_hourly') }} bots
        ON bots.user_id = au.user_id
    LEFT JOIN user_subscription_on_partition_date ust
        ON ust.user_id = au.user_id
        AND ust.p_date = au.p_date
    WHERE au.is_active
        AND au.p_date >= '{{ var("partition_start_date") }}'::date - INTERVAL '29 days'
        AND au.p_date < '{{ var("partition_end_date") }}'::date
)

-- Pre-aggregate to reduce data volume while maintaining user-level granularity
-- for proper deduplication across WAU/MAU windows
, user_metrics_preagg AS (
    SELECT
        uaw.user_id,
        uaw.p_date,
        uaw.is_suspected_bot,
        uaw.subscription_tier,
        -- Platform flags (using MAX since each user-date has consistent platform activity)
        MAX(CASE WHEN uaw.is_active_web THEN 1 ELSE 0 END) AS is_active_web,
        MAX(CASE WHEN uaw.is_active_ios THEN 1 ELSE 0 END) AS is_active_ios,
        MAX(CASE WHEN uaw.is_active_android THEN 1 ELSE 0 END) AS is_active_android
    FROM user_activity_window uaw
    GROUP BY 
        uaw.user_id,
        uaw.p_date,
        uaw.is_suspected_bot,
        uaw.subscription_tier
)

SELECT
    ds.p_date,

    -- DAU: Daily Active Users (active on p_date only)
    -- Overall DAU
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date THEN uma.user_id END) AS dau,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_nonbot,

    -- DAU by Platform
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 THEN uma.user_id END) AS dau_web,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 THEN uma.user_id END) AS dau_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 THEN uma.user_id END) AS dau_android,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_android_nonbot,

    -- DAU Free Tier
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS dau_free,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_free_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS dau_free_web,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_free_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS dau_free_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_free_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS dau_free_android,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_free_android_nonbot,

    -- DAU Pro Tier
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS dau_pro,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_pro_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS dau_pro_web,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_pro_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS dau_pro_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_pro_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS dau_pro_android,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_pro_android_nonbot,

    -- DAU Premier Tier
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS dau_premier,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_premier_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS dau_premier_web,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_premier_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS dau_premier_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_premier_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS dau_premier_android,
    COUNT(DISTINCT CASE WHEN uma.p_date = ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS dau_premier_android_nonbot,

    -- WAU: Weekly Active Users (7-day rolling window ending on p_date)
    -- Overall WAU
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date THEN uma.user_id END) AS wau,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_nonbot,

    -- WAU by Platform
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 THEN uma.user_id END) AS wau_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 THEN uma.user_id END) AS wau_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 THEN uma.user_id END) AS wau_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_android_nonbot,

    -- WAU Free Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS wau_free,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_free_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS wau_free_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_free_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS wau_free_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_free_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS wau_free_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_free_android_nonbot,

    -- WAU Pro Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS wau_pro,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_pro_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS wau_pro_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_pro_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS wau_pro_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_pro_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS wau_pro_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_pro_android_nonbot,

    -- WAU Premier Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS wau_premier,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_premier_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS wau_premier_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_premier_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS wau_premier_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_premier_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS wau_premier_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -6, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS wau_premier_android_nonbot,

    -- MAU: Monthly Active Users (30-day rolling window ending on p_date)
    -- Overall MAU
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date THEN uma.user_id END) AS mau,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_nonbot,

    -- MAU by Platform
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 THEN uma.user_id END) AS mau_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 THEN uma.user_id END) AS mau_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 THEN uma.user_id END) AS mau_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_android_nonbot,

    -- MAU Free Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS mau_free,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_free_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS mau_free_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_free_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS mau_free_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_free_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' THEN uma.user_id END) AS mau_free_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Free Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_free_android_nonbot,

    -- MAU Pro Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS mau_pro,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_pro_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS mau_pro_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_pro_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS mau_pro_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_pro_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' THEN uma.user_id END) AS mau_pro_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Pro Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_pro_android_nonbot,

    -- MAU Premier Tier
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS mau_premier,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_premier_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS mau_premier_web,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_web = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_premier_web_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS mau_premier_ios,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_ios = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_premier_ios_nonbot,

    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' THEN uma.user_id END) AS mau_premier_android,
    COUNT(DISTINCT CASE WHEN uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date AND uma.is_active_android = 1 AND uma.subscription_tier = 'Premier Plan' AND NOT uma.is_suspected_bot THEN uma.user_id END) AS mau_premier_android_nonbot,

    -- Metadata
    CURRENT_TIMESTAMP() AS dbt_updated_at
FROM date_spine ds
LEFT JOIN user_metrics_preagg uma
    ON uma.p_date BETWEEN DATEADD(day, -29, ds.p_date) AND ds.p_date
GROUP BY ds.p_date
