-- 最近 30 天高消耗素材聚合
-- 日期窗口:2026-06-07 ~ 2026-07-06
-- 目标:按 dynamic creative 聚合素材表现,用于归纳高消耗/高CTR素材方法论。

WITH metric_day AS (
    SELECT  creative_id
            ,dt
            ,valid_click_count
            ,view_count
            ,cost
            ,conversions_count
            ,key_page_view_count
            ,key_page_uv
            ,thousand_display_price
    FROM    (
                SELECT  creative_id
                        ,dt
                        ,valid_click_count
                        ,view_count
                        ,cost
                        ,conversions_count
                        ,key_page_view_count
                        ,key_page_uv
                        ,thousand_display_price
                        ,ROW_NUMBER() OVER (PARTITION BY creative_id,dt ORDER BY update_time DESC) AS rn
                FROM    loghubods.ad_put_tencent_creative_data_day
                WHERE   dt >= '2026-06-07'
                AND     dt <= '2026-07-06'
                AND     creative_id IS NOT NULL
            ) t
    WHERE   rn = 1
),
metric_agg AS (
    SELECT  creative_id
            ,MIN(dt) AS first_dt
            ,MAX(dt) AS last_dt
            ,COUNT(DISTINCT CASE WHEN cost > 0 THEN dt ELSE NULL END) AS active_days
            ,SUM(cost) / 100 AS cost_yuan
            ,SUM(view_count) AS view_count
            ,SUM(valid_click_count) AS valid_click_count
            ,SUM(key_page_view_count) AS key_page_view_count
            ,SUM(key_page_uv) AS key_page_uv
            ,SUM(conversions_count) AS conversions_count
            ,AVG(thousand_display_price) AS avg_thousand_display_price
    FROM    metric_day
    GROUP BY creative_id
),
latest_creative AS (
    SELECT  account_id
            ,ad_id
            ,creative_id
            ,creative_name
            ,creative_status
            ,create_time AS creative_created_time
    FROM    (
                SELECT  c.account_id
                        ,c.ad_id
                        ,c.creative_id
                        ,c.creative_name
                        ,c.creative_status
                        ,c.create_time
                        ,ROW_NUMBER() OVER (PARTITION BY c.creative_id ORDER BY c.create_time DESC) AS rn
                FROM    loghubods.ad_put_tencent_creative_day c
                JOIN    metric_agg m
                ON      c.creative_id = m.creative_id
            ) t
    WHERE   rn = 1
),
latest_component AS (
    SELECT  creative_id
            ,MAX(page_spec) AS page_spec
    FROM    loghubods.ad_put_tencent_creative_components
    WHERE   page_type = 'PAGE_TYPE_WECHAT_MINI_PROGRAM'
    GROUP BY creative_id
),
latest_ad AS (
    SELECT  ad_id
            ,account_id
            ,ad_name
            ,create_time AS ad_create_time
            ,ad_status
            ,system_status AS ad_system_status
            ,optimization_goal
            ,bid_amount
            ,day_amount
            ,'' AS site_set
            ,targeting
    FROM    (
                SELECT  a.ad_id
                        ,a.account_id
                        ,a.ad_name
                        ,a.create_time
                        ,a.ad_status
                        ,a.system_status
                        ,a.optimization_goal
                        ,a.bid_amount
                        ,a.day_amount
                        ,a.targeting
                        ,ROW_NUMBER() OVER (PARTITION BY a.ad_id ORDER BY a.update_time DESC) AS rn
                FROM    loghubods.ad_put_tencent_ad a
                JOIN    latest_creative c
                ON      a.ad_id = c.ad_id
            ) t
    WHERE   rn = 1
),
ad_package AS (
    SELECT  ad_id
            ,package_id
            ,package_name
            ,min_people
    FROM    (
                SELECT  m.ad_id
                        ,m.package_id
                        ,p.package_name
                        ,p.min_people
                        ,ROW_NUMBER() OVER (PARTITION BY m.ad_id ORDER BY CAST(p.min_people AS BIGINT) ASC) AS rn
                FROM    loghubods.ad_put_tencent_ad_package_mapping m
                LEFT JOIN loghubods.ad_put_tencent_package p
                ON      m.package_id = p.tencent_audience_id
                JOIN    latest_creative c
                ON      m.ad_id = c.ad_id
                WHERE   m.is_delete = 0
            ) t
    WHERE   rn = 1
),
creative_analysis AS (
    SELECT  creative_id
            ,MAX(title) AS title
            ,MAX(image_url) AS image_url
    FROM    loghubods.ad_put_tencent_creative_analysis
    GROUP BY creative_id
)
SELECT  c.account_id
        ,c.ad_id
        ,a.ad_name
        ,m.creative_id
        ,c.creative_name
        ,SPLIT(SPLIT(GET_JSON_OBJECT(cp.page_spec,'$.wechat_mini_program_spec.mini_program_path'),'rootSourceId%3D')[1],'_')[3] AS video_id
        ,ca.title
        ,ca.image_url
        ,p.package_id
        ,p.package_name
        ,p.min_people
        ,a.optimization_goal
        ,a.bid_amount
        ,a.day_amount
        ,a.site_set
        ,a.ad_status
        ,a.ad_system_status
        ,c.creative_status
        ,m.first_dt
        ,m.last_dt
        ,m.active_days
        ,m.cost_yuan
        ,m.view_count
        ,m.valid_click_count
        ,CASE WHEN m.view_count > 0 THEN m.valid_click_count / m.view_count ELSE NULL END AS ctr
        ,m.key_page_view_count
        ,CASE WHEN m.valid_click_count > 0 THEN m.key_page_view_count / m.valid_click_count ELSE NULL END AS key_page_rate
        ,m.key_page_uv
        ,m.conversions_count
        ,CASE WHEN m.valid_click_count > 0 THEN m.conversions_count / m.valid_click_count ELSE NULL END AS conversion_rate
        ,m.avg_thousand_display_price
FROM    metric_agg m
JOIN    latest_creative c
ON      m.creative_id = c.creative_id
LEFT JOIN latest_ad a
ON      c.ad_id = a.ad_id
LEFT JOIN latest_component cp
ON      m.creative_id = cp.creative_id
LEFT JOIN ad_package p
ON      c.ad_id = p.ad_id
LEFT JOIN creative_analysis ca
ON      m.creative_id = ca.creative_id
WHERE   m.cost_yuan > 0
ORDER BY m.cost_yuan DESC
LIMIT 5000