SELECT 

    t1.manufacturer,

    t1.channel_growth,

    SUM(t1.total_clv) AS current_month_total,

    CASE 

        WHEN SUM(t2.total_clv) IS NULL OR SUM(t2.total_clv) = 0 THEN NULL

        ELSE (SUM(t1.total_clv) - SUM(t2.total_clv)) / SUM(t2.total_clv)

    END AS HB,

     CASE 

        WHEN SUM(t3.total_clv) IS NULL OR SUM(t3.total_clv) = 0 THEN NULL

        ELSE (SUM(t1.total_clv) - SUM(t3.total_clv)) / SUM(t3.total_clv)

    END AS TB

FROM app_sm_newuser_ltv_clv_a_d t1

LEFT JOIN app_sm_newuser_ltv_clv_a_d t2 

    ON t2.stat_date = DATE_SUB(t1.stat_date, INTERVAL 1 MONTH)

LEFT JOIN app_sm_newuser_ltv_clv_a_d t3

   ON T3.stat_date = DATE_SUB(t1.stat_date, INTERVAL 1 YEAR)

WHERE t1.stat_date = '${p_day}' AND t1.manufacturer in( 'OPPO','VIVO','华为','其他') AND t1.channel_growth in ('厂商商店-核心','厂商商店-其他')

group by t1.manufacturer,

    t1.channel_growth