select 
    * 
from ""dashboard-metrics"".""client_overview""
where 
    1=1 
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]

with 
    _dates as (
        select 
            date_add('day', -1, current_date) as current_day,
            date_trunc('month', date_add('day', -1, current_date)) as current_month,
            date_add('month', -1, date_trunc('month', date_add('day', -1, current_date))) as prior_month,
            date_add('year', -1, date_trunc('month', date_add('day', -1, current_date))) as prior_year
    ),
    _current as (
        select 
            d.current_day as period_day,
            c.client,
            c.product,
            coalesce(sum(payout_count), 0) as claim_payout_count,
            coalesce(sum(total_claim_payout_amount), 0) as claim_payout_amount,
            coalesce(sum(claim_count), 0) as claim_count,
            coalesce(sum(claim_count) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout,
            coalesce(sum(claim_count) filter(where c.claim_status = 'in_review'), 0) as claims_in_review,
            coalesce(sum(total_claim_requested_payout), 0) as claim_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'in_review'), 0) as claims_in_review_value
        from _dates d,
        ""dashboard-metrics"".""reflective_claim_view"" c
        where  
            1=1 
            and date(c.period_date) = d.current_day
            [[and partition_stack = {{properties.stack}}]]             
            [[and client = {{customer_name}}]]
        group by 1,2,3
    ),
    _prior_month as (
        select 
            d.prior_month as period_day,
            c.client,
            c.product,
            coalesce(sum(payout_count), 0) as claim_payout_count,
            coalesce(sum(total_claim_payout_amount), 0) as claim_payout_amount,
            coalesce(sum(claim_count), 0) as claim_count,
            coalesce(sum(claim_count) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout,
            coalesce(sum(claim_count) filter(where c.claim_status = 'in_review'), 0) as claims_in_review,
            coalesce(sum(total_claim_requested_payout), 0) as claim_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'in_review'), 0) as claims_in_review_value
        from _dates d,
        ""dashboard-metrics"".""reflective_claim_view"" c
        where  
            1=1 
            and date(c.period_date) = d.prior_month
            [[and partition_stack = {{properties.stack}}]]             
            [[and client = {{customer_name}}]]
        group by 1,2,3
    ),
    _prior_year as (
        select 
            d.prior_year as period_day,
            c.client,
            c.product,
            coalesce(sum(payout_count), 0) as claim_payout_count,
            coalesce(sum(total_claim_payout_amount), 0) as claim_payout_amount,
            coalesce(sum(claim_count), 0) as claim_count,
            coalesce(sum(claim_count) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout,
            coalesce(sum(claim_count) filter(where c.claim_status = 'in_review'), 0) as claims_in_review,
            coalesce(sum(total_claim_requested_payout), 0) as claim_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'pending_payout'), 0) as claims_pending_payout_value,
            coalesce(sum(total_claim_requested_payout) filter(where c.claim_status = 'in_review'), 0) as claims_in_review_value
        from _dates d,
        ""dashboard-metrics"".""reflective_claim_view"" c
        where  
            1=1 
            and date(c.period_date) = d.prior_year
            [[and partition_stack = {{properties.stack}}]]
            [[and client = {{customer_name}}]]
        group by 1,2,3
    ),
_main as (
select * from _current
union all
select * from _prior_month
union all 
select * from _prior_year
)
    select * from _main

with 
    _aggregation as (
        select 
            date(period_date) as period_date,
            year,
            month,
            day,
            policy_count
        from ""dashboard-metrics"".""reflective_policy_view""
        where 
            1=1
            [[and partition_stack = {{properties.stack}}]]
            [[and client = {{customer_name}}]]
    ),
    _group_month as (
        select 
            year,
            month,
            sum(policy_count) as monthly_total
        from _aggregation
        where (extract(day from date_add('day', -1, date_add('month', 1, date_trunc('month', period_date)))) = day 
                    and extract(month from date_add('day', -1, date_add('month', 1, date_trunc('month', period_date)))) = month
                    and extract(year from date_add('day', -1, date_add('month', 1, date_trunc('month', period_date)))) = year)
or (extract(year from period_date) = extract(year from date_add('day', -1, current_date)) 
                    and extract(month from period_date) = extract(month from date_add('day', -1, current_date)) 
                    and extract(day from period_date) = extract(day from date_add('day', -1, current_date)))
        group by 1,2
    ),
    _lag_month as (
        select 
            year,
            month,
            monthly_total,
            coalesce(lag(monthly_total) over (order by year, month), 0) as lag_month_total
        from _group_month),
    _lag_month_year as (
        select 
            year,
            month,
            monthly_total,
            coalesce(lag(monthly_total) over (partition by month order by year), 0) as lag_month_year_total
        from _group_month
    ),
    _month_main as (
        select distinct
            date_parse(concat(cast(a.year as varchar), '-', cast(a.month as varchar)), '%Y-%m') as period,
            cast(coalesce(m.monthly_total,0) as double) as monthly_total,
            cast(coalesce(m.lag_month_total,0) as double) as lag_monthly_total,
            cast(coalesce(ym.lag_month_year_total,0) as double) as lag_month_year_total,
            cast(coalesce(m.monthly_total,0) - coalesce(m.lag_month_total,0) as double) as month_diff,
            cast(coalesce(ym.monthly_total,0) - coalesce(ym.lag_month_year_total,0) as double) as month_diff_year
        from _aggregation a
        left join _lag_month m on m.month = a.month and m.year = a.year
        left join _lag_month_year ym on ym.month = a.month and ym.year = a.year
        order by 1,2
    ),
    _trailing_year as (
        select 
            period,
            sum(month_diff) over(order by period range between interval '12' month preceding and current row) as trailing_year
        from _month_main
    ),
    _lag_trailing_year as (
        select 
            *,
            coalesce(lag(trailing_year) over(order by period),0) as lag_trailing_year
        from _trailing_year
    ),
    _year_main as (
        select 
            *,
            cast(coalesce(trailing_year,0) - coalesce(lag_trailing_year,0) as double) as year_diff
        
        from _lag_trailing_year
    )
    select 
        mm.*,
        ym.trailing_year,
        ym.lag_trailing_year,
        ym.year_diff,
        
        case 
            when lag_monthly_total = 0 
                then 0
                else month_diff / lag_monthly_total
            end as month_on_month_growth,
            
        case 
            when lag_month_year_total = 0 
                then 0
                else month_diff_year / lag_month_year_total
            end as month_on_year_growth,
            
        avg(case 
                when ym.lag_trailing_year = 0 
                    then 0
                    else ym.year_diff / ym.lag_trailing_year
                end) over (partition by extract(year from mm.period))
            as average_annual_growth
        
        
from _month_main mm
left join _year_main ym on ym.period = mm.period

select 
    * 
from ""dashboard-metrics"".""policy_issue_overview""
where 
    1=1 
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]

select 
    date(period_date) as period_day,
    *
from ""dashboard-metrics"".""reflective_policy_view""
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]

with 
    _dates as (
        select 
            date(period_date) as period_day,
            *,
            [[case 
                when date(substr({{Date selector.endDate}},1,10)) >= current_date
                    then date_add('day', -1, current_date)
                    else date(substr({{Date selector.endDate}},1,10))
                end as endDate,]]
            [[case 
                when date(substr({{Date selector.startDate}},1,10)) >= current_date
                    then date_add('day', -1, current_date)
                    else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
                end as startDate]]

        from ""dashboard-metrics"".""reflective_policy_view""
        where 
            1=1
            [[and partition_stack = {{properties.stack}}]]
            [[and client = {{customer_name}}]]
    ),
    _main as (
    select 
        period_day,
        product,
        coalesce(sum(case 
                    when endDate = period_day
                        then policy_count
                    when startDate = period_day
                        then policy_count
                end), 0) as total_policy_count, 
        coalesce(sum(case 
                when endDate = period_day
                    then policy_count
                when startDate = period_day
                    then policy_count
            end) filter(where event_status = 'active'), 0) as total_active_policy_count,
        coalesce(sum(case 
                when endDate = period_day
                    then policy_count
                when startDate = period_day
                    then policy_count
            end) filter(where event_status = 'cancelled'), 0) as total_cancelled_policy_count,
        coalesce(sum(case 
                when endDate = period_day
                    then policy_count
                when startDate = period_day
                    then policy_count
            end) filter(where event_status = 'lapsed'), 0) as total_lapsed_policy_count,
        coalesce(sum(case 
                when endDate = period_day
                    then policy_count
                when startDate = period_day
                    then policy_count
            end) filter(where event_status = 'not_taken_up'), 0) as total_ntu_policy_count,
        coalesce(sum(case 
                when endDate = period_day
                    then policyholder_count
                when startDate = period_day
                    then policyholder_count
            end) filter(where event_status = 'active'), 0) as total_active_policyholder_count,
        coalesce(sum(case 
                when endDate = period_day
                    then total_sum_assured
                when startDate = period_day
                    then total_sum_assured
            end) filter(where event_status = 'active'), 0) as total_active_sum_assured,
        coalesce(sum(case 
                when endDate = period_day
                    then abs(total_premium)
                when startDate = period_day
                    then abs(total_premium)
            end), 0) as total_premium_calculated,
        coalesce(sum(case 
                when endDate = period_day
                    then abs(total_sum_assured)
                when startDate = period_day
                    then abs(total_sum_assured)
            end), 0) as total_sum_assured_calculated
    from _dates
    where period_day = endDate or period_day = startDate
    group by 1,2
    order by 1,2
    )
select 
    *,
    cast(total_premium_calculated as double)/cast(total_policy_count as double) as average_premium_value,
    cast(total_sum_assured_calculated as double)/cast(total_policy_count as double) as average_sum_assured_value
from _main

select 
    date(period_date) as period_day,
    product,
    coalesce(sum(cast(policy_count as double)) filter(where event_status = 'active') / sum(cast(policy_count as double)), 0) as active_ratio,
    coalesce(sum(cast(policy_count as double)) filter(where event_status = 'lapsed') / sum(cast(policy_count as double)), 0) as lapse_ratio,
    coalesce(sum(cast(policy_count as double)) filter(where event_status = 'cancelled') / sum(cast(policy_count as double)), 0) as cancelled_ratio,
    coalesce(sum(cast(policy_count as double)) filter(where event_status = 'not_taken_up') / sum(cast(policy_count as double)), 0) as ntu_ratio,
    coalesce(sum(cast(policy_count as double)) filter(where event_status = 'pending') / sum(cast(policy_count as double)), 0) as pending_ratio

from ""dashboard-metrics"".""reflective_policy_view""
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
group by 1,2

with _policies as(

select 
    date(period_day) as period_day,
    client,
    product,
    sum(policy_count) as policy_count
from ""dashboard-metrics"".""policy_issue_overview"" 
where 
    1=1
    [[and client = {{customer_name}}]]
group by 1,2,3
),
_payments as (

select 
    date(period_date) as period_day,
    client,
    product,
    sum(payment_amount) as payment_amount
from ""dashboard-metrics"".""payment_history"" 
where 
    1=1
    [[and client = {{customer_name}}]]
group by 1,2,3
),
_main as (
select 
    pol.period_day,
    pol.client,
    pol.product,
    pol.policy_count,
    pay.payment_amount,
    cast(sum(pol.policy_count) over (order by pol.period_day) as double) as running_policy_count,
    cast(sum(pay.payment_amount) over ( order by pay.period_day) as double) as running_payment_count,
    [[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]]
from _policies pol 
left join _payments pay on pay.client = pol.client and pay.product = pol.product and pay.period_day = pol.period_day
order by 1 desc
)
select 
    period_day,
    client,
    product,
    coalesce(case 
                when endDate = period_day
                    then running_policy_count/running_payment_count
                when startDate = period_day
                    then running_policy_count/running_payment_count
                end, 0) as running_average_premium
from _main
order by 1 desc

with _main as (
select 
    *,
    [[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]]
from ""dashboard-metrics"".""reflective_application_view""
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
)
select 
    *,
    coalesce(case 
            when running_issued_count is null or running_issued_count = 0 or running_pending_count is null or running_pending_count = 0 
                then 0
            when endDate = period_day
                then cast(running_issued_count as double)/cast(running_pending_count as double)
            when startDate = period_day
                then cast(running_issued_count as double)/cast(running_pending_count as double)
            end, 0) as application_conversion_rate,
    case 
        when running_issued_count is null or running_issued_count = 0 or running_pending_count is null or running_pending_count = 0 
            then 0
            else coalesce(cast(running_issued_count as double)/cast(running_pending_count as double), 0) 
        end as general_application_conversion_rate
from _main
order by 1 desc

with _main as (
select 
    period_day,
    client,
    product,
    channel,
    p.status,
    case
        when p.status = 'pending'
            then pending_count - issued_count - archived_count 
        when p.status = 'issued'
            then issued_count 
        when p.status = 'archived'
            then archived_count
    end as application_count
            
    
from ""dashboard-metrics"".""reflective_application_view""
cross join unnest(array['pending', 'issued', 'archived']) as p (status)
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
)
select 
*,
sum(application_count) over (partition by client, product, channel, status order by period_day) as running_application_count
from _main

order by 1 desc

with _claims as (
select 
    [[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]],
    * 
from ""dashboard-metrics"".""claim_overview"" 
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
)
select 
    period_day,
    client,
    product,
    average_beneficiary_count,
    time_to_close,
    claim_age,
    total_payout_value,
    total_reserved_payout,
    total_payments,
    running_claim_age,
    running_payout_value,
    running_reserved_payout,
    running_total_payments_received,
    coalesce((running_payout_value + running_reserved_payout) / running_total_payments_received, 0) as claim_ratio,
    case 
        when endDate = period_day
            then running_total_payments_received
        when startDate = period_day
            then running_total_payments_received
        end as running_running_total_payments_received,
    avg(case 
                when endDate = period_day
                    then coalesce((running_payout_value + running_reserved_payout) / running_total_payments_received, 0)
                when startDate = period_day
                    then coalesce((running_payout_value + running_reserved_payout) / running_total_payments_received, 0)
                end) as claim_ratio_in_period,
    avg(case 
                when endDate = period_day
                    then running_average_beneficiary_count
                when startDate = period_day
                    then running_average_beneficiary_count
                end) as average_beneficiary_count_in_period,
    avg(case 
                when endDate = period_day
                    then running_average_time_to_close
                when startDate = period_day
                    then running_average_time_to_close
                end) as average_time_to_close_in_period
from _claims
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
order by 1 desc

with _main as (
select 
date(period_date) as period_day, 
[[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]],
* 
from ""dashboard-metrics"".""reflective_claim_view"" 
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
    ),
    _final as (
select *, 
coalesce(case 
                when endDate = period_day
                    then claim_count
                when startDate = period_day
                    then claim_count
                end, 0) as claim_count_in_period,
coalesce(case 
                when endDate = period_day
                    then payout_count
                when startDate = period_day
                    then payout_count
                end, 0) as payout_count_in_period,
coalesce(case 
                when endDate = period_day
                    then total_claim_payout_amount
                when startDate = period_day
                    then total_claim_payout_amount
                end, 0) as total_claim_payout_amount_in_period,
coalesce(case 
                when endDate = period_day
                    then total_claim_requested_payout
                when startDate = period_day
                    then total_claim_requested_payout
                end, 0) as total_claim_requested_payout_in_period
from _main
)

select *,
case 
    when total_claim_requested_payout_in_period = 0 or claim_count_in_period = 0
        then 0 
        else total_claim_requested_payout_in_period/claim_count_in_period 
    end as avg_claim_requested_payout_in_period,
case 
    when total_claim_payout_amount_in_period = 0 or payout_count_in_period = 0
        then 0 
        else total_claim_payout_amount_in_period/payout_count_in_period 
    end as avg_total_claim_payout_amount_in_period
from _final
order by 1 desc

with _filtered as (
    select 
        rejection_reason 
    from """"dashboard-metrics"""".""""claim_overview"""" 
    where 
        1=1
        [[and partition_stack = {{properties.stack}}]]
        [[and client = {{customer_name}}]]
        and rejection_reason is not null
),
_main as (
select 
    trim(r) as reason,
    length(trim(r)) as reason_size,
    count(distinct r) reason_count
from _filtered
cross join unnest(rejection_reason) as rejection(r)
group by 1
order by 3,2,1
),
_final as (
select 
row_number() over (order by reason_count, reason_size, reason) as row_num,
reason
from _main
)
select 
concat(cast(row_num as varchar),'.  ', reason) as reason
from _final
limit 10

with 
    _main as (
    select 
        *,
        [[case 
            when date(substr({{Date selector.endDate}},1,10)) >= current_date
                then date_add('day', -1, current_date)
                else date(substr({{Date selector.endDate}},1,10))
            end as endDate,]]
        [[case 
            when date(substr({{Date selector.startDate}},1,10)) >= current_date
                then date_add('day', -1, current_date)
                else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
            end as startDate]]
    from ""dashboard-metrics"".""policyholder_history""
            where 
                1=1
                [[and partition_stack = {{properties.stack}}]]
                [[and client = {{customer_name}}]]
)
select 
        *
from _main
where 
    endDate = period_day or startDate = period_day

select 
    trim(o.name) as client,
    trim(pm.name) as product,
    coalesce(gender, 'unknown') as gender,
    count(ph.policyholder_id) as ph_count

[[from ""{{properties.stackDatabase.raw()}}"""".public_policyholders ph]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_organizations o on o.organization_id = ph.organization_id]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_policies p on p.policyholder_id = ph.policyholder_id]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_product_modules pm on pm.product_module_id = p.product_module_id]]

where
    1=1
    and ph.environment = 'production'
    [[and ph.organization_id = {{customer_id}}]]
group by 1,2,3

with _main as (
select 
    trim(o.name) as client,
    trim(pm.name) as product,
    trim(coalesce(lower(ph.city), 'unknown')) as ""location"",
    count(ph.policyholder_id) as ph_count

[[from ""{{properties.stackDatabase.raw()}}"""".public_policyholders ph]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_organizations o on o.organization_id = ph.organization_id]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_policies p on p.policyholder_id = ph.policyholder_id]]
[[left join ""{{properties.stackDatabase.raw()}}"".public_product_modules pm on pm.product_module_id = p.product_module_id]]

where
    1=1
    and ph.environment = 'production'
    [[and ph.organization_id = {{customer_id}}]]
group by 1,2,3
)
select 
    client,
    product,
    ph_count,
    case 
        when rank() over(order by ph_count desc) > 30 
            then 'Other'
            else lower(""location"")
    end as policyholder_location
from _main

with _main as (
select 
    date(period_date) as period_day,
    *,
    [[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]]
from ""dashboard-metrics"".""payment_history"" 
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]

)
select 
    *,
    coalesce(case 
                when running_number_root_managed_no_status <= 0 or running_payment_count_no_status <= 0
                    then 0
                when endDate = period_day
                    then cast(running_number_root_managed_no_status as double)/cast(running_payment_count_no_status as double)
                when startDate = period_day
                    then cast(running_number_root_managed_no_status as double)/cast(running_payment_count_no_status as double)
                end, 0) as use_root_managed

from _main
order by 1 desc

select 
date(ph.period_date) as period_day,
ph.*,
sum(
    case
        when ph.payment_status = 'successful'
            then rp.policy_count
    end) as active_policy_count


from ""dashboard-metrics"".""reflective_policy_view"" rp
left join ""dashboard-metrics"".""payment_history"" ph on rp.stack = ph.stack 
                                                        and rp.client = ph.client
                                                        and rp.product = ph.product
                                                        and date(rp.period_date) = date(ph.period_date)
where 
    1=1
    [[and ph.stack = {{properties.stack}}]]
    [[and ph.client = {{customer_name}}]]
    and rp.event_status = 'active'
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39
order by 1 desc

with _main as (
select 
    *,
    [[case 
        when date(substr({{Date selector.endDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date(substr({{Date selector.endDate}},1,10))
        end as endDate,]]
    [[case 
        when date(substr({{Date selector.startDate}},1,10)) >= current_date
            then date_add('day', -1, current_date)
            else date_add('day', -1, date(substr({{Date selector.startDate}},1,10)))
        end as startDate]]
from ""dashboard-metrics"".""payment_method_breakdown"" 
where 
    1=1
    [[and partition_stack = {{properties.stack}}]]
    [[and client = {{customer_name}}]]
)
select 
    *,
    coalesce(case 
                when method_count <= 0 
                    then 0
                when endDate = period_day
                    then cast(method_count as double)
                when startDate = period_day
                    then cast(method_count as double)
                end, 0) as use_method_count
from _main