today_cost_sum: >
    select sum(billing_cost_price) as today_cost
    from dws.dbaas_billing.dedicated_customer_daily_cost 
    where billing_cost_date = date '{today}' and billing_tenant_id = '{tenant_id}'
yesterday_cost_sum: >
    select sum(billing_cost_price) as today_cost
    from dws.dbaas_billing.dedicated_customer_daily_cost 
    where billing_cost_date = date '{today}' - interval '1' day and billing_tenant_id = '{tenant_id}'
big_contribution_projects: >
    select billing_project_id, billing_project_name, today_project_cost_sum, yesterday_project_cost_sum, diff_ratio
    from (
        select 
            coalesce(today.billing_project_id, yesterday.billing_project_id) as billing_project_id, 
            coalesce(today.billing_project_name, yesterday.billing_project_name) as billing_project_name, 
            today.project_cost_sum as today_project_cost_sum, 
            yesterday.project_cost_sum as yesterday_project_cost_sum,
            (today.project_cost_sum - yesterday.project_cost_sum) / ((
                select sum(billing_cost_price) as today_cost
                from dws.dbaas_billing.dedicated_customer_daily_cost 
                where billing_cost_date = date '{today}' and billing_tenant_id = '{tenant_id}'
            ) - (
                select sum(billing_cost_price) as today_cost
                from dws.dbaas_billing.dedicated_customer_daily_cost 
                where billing_cost_date = date '{today}' - interval '1' day and billing_tenant_id = '{tenant_id}'
            )) as diff_ratio
        from (
            select 
                billing_project_id, 
                any_value(billing_project_name) as billing_project_name, 
                sum(billing_cost_price) as project_cost_sum
            from dws.dbaas_billing.dedicated_customer_daily_cost
            where billing_cost_date = date '{today}' and billing_tenant_id = '{tenant_id}'
            group by billing_project_id
        ) today join (
            select 
                billing_project_id, 
                any_value(billing_project_name) as billing_project_name, 
                sum(billing_cost_price) as project_cost_sum
            from dws.dbaas_billing.dedicated_customer_daily_cost
            where billing_cost_date = date '{today}' - interval '1' day and billing_tenant_id = '{tenant_id}'
            group by billing_project_id
        ) yesterday on today.billing_project_id = yesterday.billing_project_id
    ) where diff_ratio > 0.05 order by diff_ratio desc
tenant_unit_contribution: >
    select coalesce(billing_cluster_name, '-') as billing_cluster_name, billing_unit, today_cost, yesterday_cost, diff_ratio
    from (
        select 
            coalesce(today.cluster_name, yesterday.cluster_name) as billing_cluster_name, 
            coalesce(today.billing_unit, yesterday.billing_unit) as billing_unit,
            coalesce(today.unit_cost, 0) as today_cost,
            coalesce(yesterday.unit_cost, 0) as yesterday_cost,
            (coalesce(today.unit_cost, 0) - coalesce(yesterday.unit_cost, 0)) / ((
                select sum(billing_cost_price) as today_cost
                from dws.dbaas_billing.dedicated_customer_daily_cost
                where billing_cost_date = date '{today}' and billing_tenant_id = '{tenant_id}'
            ) - (
                select sum(billing_cost_price) as today_cost
                from dws.dbaas_billing.dedicated_customer_daily_cost 
                where billing_cost_date = date '{today}' - interval '1' day and billing_tenant_id = '{tenant_id}'
            )) as diff_ratio
        from (
            select 
                billing_cluster_id, 
                any_value(billing_cluster_name) as cluster_name,
                billing_unit, 
                sum(billing_cost_price) as unit_cost
            from dws.dbaas_billing.dedicated_customer_daily_cost
            where billing_cost_date = date '{today}' 
                and billing_project_id = '{project_id}'
            group by billing_cluster_id, billing_unit
        ) today full join (
            select
                billing_cluster_id, 
                any_value(billing_cluster_name) as cluster_name, 
                billing_unit, 
                sum(billing_cost_price) as unit_cost
            from dws.dbaas_billing.dedicated_customer_daily_cost
            where billing_cost_date = date '{today}' - interval '1' day 
                and billing_project_id = '{project_id}' 
            group by billing_cluster_id, billing_unit
        ) yesterday on (
            today.billing_cluster_id = yesterday.billing_cluster_id
            or today.billing_cluster_id is null and yesterday.billing_cluster_id is null
        ) and today.billing_unit = yesterday.billing_unit
    ) where diff_ratio > 0.05 order by diff_ratio desc