WITH log_level_mta_pivot AS (

    -- Aggregate attribution from log-level MTA
    SELECT
        date,
        tier_1,
        tier_2,
        tier_3,
        tier_4,
        tier_5,
        spend_key,

        -- First Touch Attribution
        SUM(
            CASE 
                WHEN new_to_file = 1 THEN first_touch
                ELSE 0
            END
        ) AS ntf_first_touch,
        SUM(first_touch) AS first_touch,

        SUM(
            CASE 
                WHEN new_to_file = 1 THEN revenue_first_touch
                ELSE 0
            END
        ) AS ntf_revenue_first_touch,
        SUM(revenue_first_touch) AS revenue_first_touch,

        -- Last Touch Attribution
        SUM(
            CASE 
                WHEN new_to_file = 1 THEN last_touch
                ELSE 0
            END
        ) AS ntf_last_touch,
        SUM(last_touch) AS last_touch,

        SUM(
            CASE 
                WHEN new_to_file = 1 THEN revenue_last_touch
                ELSE 0
            END
        ) AS ntf_revenue_last_touch,
        SUM(revenue_last_touch) AS revenue_last_touch,

        -- Even Attribution
        SUM(
            CASE 
                WHEN new_to_file = 1 THEN even
                ELSE 0
            END
        ) AS ntf_even,
        SUM(even) AS even,

        SUM(
            CASE 
                WHEN new_to_file = 1 THEN revenue_even
                ELSE 0
            END
        ) AS ntf_revenue_even,
        SUM(revenue_even) AS revenue_even,

        -- Modeled Attribution
        SUM(
            CASE 
                WHEN new_to_file = 1 THEN normalized
                ELSE 0
            END
        ) AS ntf_normalized,
        SUM(normalized) AS normalized,

        SUM(
            CASE 
                WHEN new_to_file = 1 THEN revenue_normalized
                ELSE 0
            END
        ) AS ntf_revenue_normalized,
        SUM(revenue_normalized) AS revenue_normalized

    FROM log_level_mta

    WHERE
        date >= {{start_date}}
        AND date <= {{end_date}}

    GROUP BY
        1,2,3,4,5,6,7
),

aggregate_mta_spend AS (

    -- Gather spend from buckets breakdown
    SELECT
        date,
        tier_1,
        tier_2,
        tier_3,
        tier_4,
        tier_5,
        platform,
        platform_join_key,
        included_spend AS spend

    FROM mta_tiers

    WHERE
        date >= {{start_date}}
        AND date <= {{end_date}}
        --choose the same conversion event matching the log level MTA table
        AND conversion_event_id = {{conversion identifier}}
        AND included_spend > 0
)

-- Combine attribution with spend
SELECT
    COALESCE(l.date, s.date) AS date,
    COALESCE(l.tier_1, s.tier_1) AS tier_1,
    COALESCE(l.tier_2, s.tier_2) AS tier_2,
    COALESCE(l.tier_3, s.tier_3) AS tier_3,
    COALESCE(l.tier_4, s.tier_4) AS tier_4,
    COALESCE(l.tier_5, s.tier_5) AS tier_5,

    COALESCE(l.spend_key, s.platform_join_key) AS spend_key,
    s.platform,
    COALESCE(s.spend,0) AS spend,

    COALESCE(l.first_touch,0) AS first_touch,
    COALESCE(l.revenue_first_touch,0) AS revenue_first_touch,

    COALESCE(l.last_touch,0) AS last_touch,
    COALESCE(l.revenue_last_touch,0) AS revenue_last_touch,

    COALESCE(l.even,0) AS even,
    COALESCE(l.revenue_even,0) AS revenue_even,

    COALESCE(l.normalized,0) AS normalized,
    COALESCE(l.revenue_normalized,0) AS revenue_normalized

FROM log_level_mta_pivot l

FULL OUTER JOIN aggregate_mta_spend s
    ON s.platform_join_key = l.spend_key
   AND s.date = l.date;

SELECT * FROM log_level_mta_pivot
UNION ALL
SELECT * FROM aggregate_mta_spend;

date
tier_1
tier_2
tier_3
tier_4
tier_5