new_coip_query

new_coip_query.sql

-- Note:
-- 1) This query does not filter out non-rated MTAs (as per COIP requirements, 
--     as COIP wants counts of all MTA types). This means that the result can
--     contain multiple events with the same summaryreference value, since a
--     non-rated MTA will contain the same summaryreference as the previous
--     rated event. This shouldn't be a problem, since joining to the IHP data 
--     to get any features/dimensions should be correct - for a non-rated MTA, 
--     any dimensions that are sent to the IHP during rated events should not 
--     have changed. Care should be taken when adding joins however, to ensure 
--     that no duplicates creep in.

WITH past_events AS (
    -- Filter out any events 
    -- 1) that occurred after the relevant reporting period
    -- 2) that appear in the cloned busevent table
    -- 3) where policynumber is NULL (i.e. they weren't saved against a policy)
    -- 4) where the process type is not relevant to this report
    SELECT be.businessevent_id
         , be.busevent_created_datetime_utc
         , be.processtype_code
         , be.processtype_group
         , be.donetoresult_id
         , be.donetorisk_id
         , be.associatedevent_id
         , be.policynumber
         , be.donebyparty_id
         , be.policy_created_datetime_local
         , date_diff('year',date(policy_created_datetime_local),date(risk_created_datetime_local))+1 as renew_tenure
    FROM proddwnlake_transformed.kf_core_busevent be
    LEFT JOIN proddwnlake_transformed.kf_core_busevent_cloned bec
           ON be.businessevent_id = bec.businessevent_id
    WHERE to_char(be.busevent_created_datetime_utc, 'yyyymm') <= date_format(date_add('month', -1, current_date), '%Y%m')
      AND bec.businessevent_id IS NULL
      AND be.policynumber IS NOT NULL
      AND be.processtype_code IN (
        'NB CONF', 'NB TAKEUP', -- NB events
        'NB CANCEL', 'UndoCanPol', -- cancellation events
        'MTA CONF', 'MTA TAKEUP', 'PERMTACANC', -- MTA events
        'REN TERMS', 'AutoRenT', -- renewal invite events
        'REN CONF', 'AutoRenew', -- renewal events
        'LAPSEPOL', 'AutoLapse', 'UndoLapse' -- lapse events
      )        
),

-- Small number of NB CONF events fail, causing an NB TAKEUP which is followed by 
-- NB CONF at some point in the future. We should take the first event only, as
-- this is what we should derive the accept date from.
deduped_takeup_conf_nb_events AS (
    SELECT subq.businessevent_id
         , subq.busevent_created_datetime_utc
         , subq.processtype_code
         , subq.processtype_group
         , subq.donetoresult_id
         , subq.donetorisk_id
         , subq.associatedevent_id
         , subq.policynumber
         , subq.donebyparty_id
    FROM (
        SELECT be.*
             , ROW_NUMBER() OVER (PARTITION BY summaryreference ORDER BY busevent_created_datetime_utc ASC) AS event_rn
        FROM past_events be
        LEFT JOIN proddwnlake_transformed.kf_core_general_result grt
               ON be.donetoresult_id = grt.result_id
        WHERE processtype_code IN ('NB CONF', 'NB TAKEUP')
    ) subq
    WHERE event_rn = 1
),

-- Small number of MTA CONF events fail, causing an MTA TAKEUP which is followed by 
-- MTA CONF at some point in the future. We need to dedup these, but the only 
-- column both have in common is "summaryreference", which will be shared by any 
-- subsequent non-rated MTAs, so is not appropriate to use for grouping. Instead 
-- use the associatedevent_id of MTA CONF events which will point back to the 
-- MTA TAKEUP events in these situations. When the join is successful, there 
-- has been a TAKEUP event followed by a CONF event, and we should take the 
-- timestamp of the TAKEUP event, but everything else should be taken from the 
-- CONF event.
deduped_takeup_conf_mta_events AS (
    SELECT be.businessevent_id
         , COALESCE(be_tu.busevent_created_datetime_utc, be.busevent_created_datetime_utc) AS busevent_created_datetime_utc
         , be.processtype_code
         , be.processtype_group
         , be.donetoresult_id
         , be.donetorisk_id
         , be.associatedevent_id
         , be.policynumber
         , be.donebyparty_id
    FROM past_events be
    LEFT JOIN (
        SELECT be.businessevent_id
             , be.associatedevent_id
             , be.processtype_code
             , be.policynumber
             , be.busevent_created_datetime_utc
        FROM past_events be
        WHERE be.processtype_code = 'MTA TAKEUP'
    ) be_tu
          ON be.associatedevent_id = be_tu.businessevent_id
    WHERE be.processtype_code = 'MTA CONF'
),

deduped_events AS (
    SELECT businessevent_id
         , busevent_created_datetime_utc
         , processtype_code
         , processtype_group
         , donetoresult_id
         , donetorisk_id
         , associatedevent_id
         , policynumber
         , donebyparty_id
    FROM past_events
    WHERE processtype_code IN (
        'NB CANCEL', 'UndoCanPol', -- cancellation events
        'PERMTACANC', -- MTA events
        'REN TERMS', 'AutoRenT', -- renewal invite events
        'REN CONF', 'AutoRenew', -- renewal events
        'LAPSEPOL', 'AutoLapse', 'UndoLapse' -- lapse events
    )
    UNION ALL 
    SELECT * FROM deduped_takeup_conf_nb_events
    UNION ALL 
    SELECT * FROM deduped_takeup_conf_mta_events
),

-- Identify events that undo previous events
undo_events AS (
    SELECT businessevent_id
         , processtype_code
         , associatedevent_id
    FROM deduped_events
    WHERE processtype_code IN ('UndoCanPol', 'UndoLapse', 'PERMTACANC')
)
,
-- Remove any events which were followed by an event that cancels it out
-- Filter out event types that undo previous events - we only want the events
-- that actually changed th state of the policy
events_with_undone_removed AS (
    SELECT be.*
         , grt.summaryreference
         , grt.status_desc
         , grk.start_datetime_local
         , grk.renewalcount AS tenure
    FROM deduped_events be
    LEFT JOIN undo_events ue
           ON be.businessevent_id = ue.associatedevent_id
    LEFT JOIN kf_core_general_result grt
           ON be.donetoresult_id = grt.result_id
    LEFT JOIN kf_core_general_risk grk
           ON be.donetorisk_id = grk.risk_id
    WHERE ue.associatedevent_id IS NULL
      AND be.processtype_code IN (
        'NB CONF', 'NB TAKEUP', -- NB events
        'NB CANCEL', -- cancellation events
        'MTA CONF', 'MTA TAKEUP', -- MTA events
        'REN TERMS', 'AutoRenT', -- renewal invite events
        'REN CONF', 'AutoRenew', -- renewal events
        'LAPSEPOL', 'AutoLapse' -- lapse events
      )
),
--SELECT * FROM events_with_undone_removed LIMIT 100

-- TODO:

  -- Collapse renewal cycles to a single event where the enquiry date is the initial enquiry date

  -- Generate two columns: 
  --  1) "accept_date" - date of the *CONF/AutoRenew business events
  --  2) "incept date" - the risk start_date

  -- Remove CANCEL/LAPSE events from event level dataset

  -- Produce policy-level dataset that has  
  --  1) policy_number
  --  2) cancellation_date
  --  3) lapse_date



-- -- Undo events almost always associated with the undone event
-- SELECT be1.processtype_code, be2.processtype_code, COUNT(1)
-- FROM proddwnlake_transformed.kf_core_busevent be1
-- LEFT JOIN proddwnlake_transformed.kf_core_busevent be2
--       ON be1.associatedevent_id = be2.businessevent_id
-- WHERE be1.processtype_code IN ('UndoCanPol', 'UndoLapse', 'PERMTACANC')
-- GROUP BY 1, 2

-- 3237181 vs 3257409: 20,228 events that were later undone
renewals as (select policynumber, renew_tenure, processtype_group, policy_created_datetime_local
from past_events where processtype_group in ('INVITE', 'RENEWAL')),
feature as (
select distinct be.policynumber, summaryreference, start_datetime_local,
case when tenure > 10 then '10+' else cast(tenure as varchar) end as tenure, 
case when main_driver_age_years < 22 then '<22'
    when main_driver_age_years >= 22 and main_driver_age_years < 26 then '>=22 - <26'
    when main_driver_age_years >= 26 and main_driver_age_years < 30 then '>=26 - <30'
    when main_driver_age_years >= 30 and main_driver_age_years < 35 then '>=30 - <35'
    when main_driver_age_years >= 35 and main_driver_age_years < 40 then '>=35 - <40'
    when main_driver_age_years >= 40 and main_driver_age_years < 50 then '>=40 - <50'
    when main_driver_age_years >= 50 and main_driver_age_years < 60 then '>=50 - <60'
    when main_driver_age_years >= 60 and main_driver_age_years < 65 then '>=60 - <65'
    when main_driver_age_years >= 65 and main_driver_age_years < 70 then '>=65 - <70'
    when main_driver_age_years >= 70 and main_driver_age_years < 80 then '>=70 - <80'
    else 'more than 80'
    end as policy_holder_age, 
    case when proposer_credit_score >= 900 then '>= 900'
    when proposer_credit_score >= 850 then '>=850-<900'
    when proposer_credit_score >= 800 then '>=800-<850'
    when proposer_credit_score >= 700 then '>=700-<800'
    when proposer_credit_score >= 600 then '>=600-<700'
    else '<600' end as credit_score, 
    case when channel = '001' then 'Digital' 
    when channel = '002' then 'Contact Centre' 
    when channel in ('003', '009', '013', '015') then 'Price Comparison Website' end as originating_channel, 
    case when scheme_ref = 'Y0' then 'Standard' when scheme_ref = 'YI' then 'Gold'
             when scheme_ref = 'YU' then 'Platinum' else 'Standard' end as product_type, 
    case when be.donebyparty_id = -3 then 'Digital' 
             when be.donebyparty_id != -3 and be.donebyparty_id is not null then 'Contact Centre' 
             else 'Unknown' end as bound_channel, 
             payment_method_code, COALESCE(shortresponse, LAG(shortresponse) IGNORE NULLS OVER (PARTITION BY be.policynumber ORDER BY created_date_local ASC)) AS filled_forward --, policy_date
from events_with_undone_removed be
-- join kf_core_policy p on be.policynumber = p.policynumber
-- left join kf_core_policy_history as ph on p.policynumber = ph.policynumber
--join kf_core_general_result as gre on ph.donetoresult_id = gre.result_id
--join kf_core_general_risk as gri on be.donetorisk_id = gri.risk_id
--join events_with_undone_removed be on be.businessevent_id = ph.businessevent_id and be.policynumber = ph.policynumber
-- left join kf_core_busevent_cloned bc on bc.businessevent_id = be.businessevent_id
left join ihp_enhanced_request ier on summaryreference = transaction_id
--join pol_ifp ifp on be.policynumber = ifp.policy_number
left join (select distinct businessevent_id, payment_method_code from proddwnlake_transformed.kf_core_account) acc on be.businessevent_id = acc.businessevent_id
left join (select * from kf_strata_ufrpxxxx WHERE userfieldcode = 'VULNCUST') uf ON be.donetorisk_id = uf.risk_id
where be.processtype_code in ('NB CONF', 'NB TAKEUP', 'AutoRenew', 'REN CONF')
and be.status_desc = 'Taken Up' and process_type in ('RENEWAL_AGED', 'NEW_BUSINESS') 
-- and bc.businessevent_id is null
-- and date(policy_date) = date_add('day', -1, current_date)
)
,metrics as (
-- select 'C355' as metrics_id, 'Total In-Force Policies' as metric_name, tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, 'NA' as vulnerable, 0 as RATE_NUMERATOR, 0 as RATE_DENOMINATOR
-- from feature p
-- join pol_ifp ifp on p.policynumber = ifp.policy_number and p.tenure = (case when ifp.policy_tenure_years > 10 then '10+' else cast(policy_tenure_years as varchar) end)
-- where date(policy_date) = date_add('day', -1, date(concat(date_format(current_date, '%Y-%m-'),'01' )))
-- union all
-- select '' as metrics_id, 'NB Transactions' as metric_name, p.tenure, '0' as policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, 'NA' as vulnerable, 0 as RATE_NUMERATOR, 0 as RATE_DENOMINATOR
-- from feature p
-- join proddwnlake_data_model.darwin_enquiry_event enq
-- on p.policynumber = enq.policy_number
-- where accept_indicator = 1 and event_type = 'NB' 
-- and to_char(accept_date, 'yyyymm') = date_format(date_add('month', -1, current_date), '%Y%m')
-- and p.tenure = cast(enq.tenure as varchar)
-- union all
-- select 'C349' as metrics_id, 'MTA Volume' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, 'NA' as vulnerable, 0 as RATE_NUMERATOR, 0 as RATE_DENOMINATOR
-- from feature p
-- join deduped_takeup_conf_mta_events mta
-- on p.policynumber = mta.policynumber
-- where to_char(busevent_created_datetime_utc, 'yyyymm') = date_format(date_add('month', -1, current_date), '%Y%m')
-- union all
select 'C307' as metrics_id, metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, vulnerable, excess_value as RATE_NUMERATOR, sum(excess_value) over(partition by metric_name) as RATE_DENOMINATOR
from (select *, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable from feature) p
left join (select *, case when excess_value > 0 and excess_value < 100 then 'Excess Mix - Compulsory Accidental Damage >0<100' when excess_value >= 100 and excess_value < 200 then 'Excess Mix - Compulsory Accidental Damage >=100<200' when excess_value >= 200 and excess_value < 300 then 'Excess Mix - Compulsory Accidental Damage >=200<300' when excess_value >= 300 and excess_value < 400 then 'Excess Mix - Compulsory Accidental Damage >=300<400' when excess_value >= 400 and excess_value < 500 then 'Excess Mix - Compulsory Accidental Damage >=400<500' when excess_value >= 500 and excess_value < 600 then 'Excess Mix - Compulsory Accidental Damage >=500<600' when excess_value >= 600 and excess_value < 700 then 'Excess Mix - Compulsory Accidental Damage >=600<700' when excess_value >= 700 and excess_value < 800 then 'Excess Mix - Compulsory Accidental Damage >=700<800' when excess_value >= 800 and excess_value < 900 then 'Excess Mix - Compulsory Accidental Damage >=800<900' when excess_value >= 900 and excess_value < 1000 then 'Excess Mix - Compulsory Accidental Damage >=900<1000' else 
'Excess Mix - Compulsory Accidental Damage >=1000' end as metric_name from ihp_excesses) excess
on p.summaryreference = excess.transaction_id
where excess_type = 7 and driver_position = 1
-- union all
-- select 'C319' as metrics_id, case when excess_value > 0 and excess_value < 100 then 'Excess Mix - Voluntary Accidental Damage >0<100' when excess_value >= 100 and excess_value < 200 then 'Excess Mix - Voluntary Accidental Damage >=100<200' when excess_value >= 200 and excess_value < 300 then 'Excess Mix - Voluntary Accidental Damage >=200<300' when excess_value > 300 and excess_value < 400 then 'Excess Mix - Voluntary Accidental Damage >=300<400' when excess_value > 400 and excess_value < 500 then 'Excess Mix - Voluntary Accidental Damage >=400<500' else 
-- 'Excess Mix - Voluntary Accidental Damage >=500' end as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable
-- from feature p
-- left join ihp_excesses excess
-- on p.summaryreference = excess.transaction_id
-- where excess_type = 1 and driver_position = 1
-- union all
-- select 'C325' as metrics_id, case when excess_value > 0 and excess_value < 100 then 'Excess Mix - Total Accidental Damage >0<100 ' when excess_value >= 100 and excess_value < 200 then 'Excess Mix - Total Accidental Damage >=100<200' when excess_value >= 200 and excess_value < 300 then 'Excess Mix - Total Accidental Damage >=200<300' when excess_value >= 300 and excess_value < 400 then 'Excess Mix - Total Accidental Damage >=300<400' when excess_value >= 400 and excess_value < 500 then 'Excess Mix - Total Accidental Damage >=400<500' when excess_value >= 500 and excess_value < 600 then 'Excess Mix - Total Accidental Damage >=500<600' when excess_value >= 600 and excess_value < 700 then 'Excess Mix - Total Accidental Damage >=600<700' when excess_value >= 700 and excess_value < 800 then 'Excess Mix - Total Accidental Damage >=700<800' when excess_value >= 800 and excess_value < 900 then 'Excess Mix - Total Accidental Damage >=800<900' when excess_value >= 900 and excess_value < 1000 then 'Excess Mix - Total Accidental Damage >=900<1000' else 
-- 'Excess Mix - Total Accidental Damage >=1000' end as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable
-- from feature p
-- left join (select transaction_id, sum(excess_value) as excess_value from ihp_excesses
-- where excess_type in (1, 7) and driver_position = 1 group by 1) excess
-- on p.summaryreference = excess.transaction_id
-- union all
-- select 'C353' as metrics_id, 'Mid-Term Cancellations - Within 14 Days As Percentage Of IFPs' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable
-- from feature p
-- join kf_core_policy pol on p.policynumber = pol.policynumber 
-- where cancellation_datetime_local is not null 
-- and day(cancellation_datetime_local - start_datetime_local) <= 14
-- union all 
-- select 'C354' as metrics_id, 'Mid-Term Cancellation - After 14 Days As Percentage Of IFPs' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable
-- from feature p
-- join kf_core_policy pol on p.policynumber = pol.policynumber 
-- where cancellation_datetime_local is not null 
-- and day(cancellation_datetime_local - start_datetime_local) > 14
-- union all
-- select 'C304' as metrics_id, 'Cancellation From Inception Rate' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable
-- from feature p
-- -- join (select count(*) from kf_core_busevent join kf_core_general_risk on risk_id = donetorisk_id
-- -- where date(policy_created_datetime_local) = date(start_datetime_local) and processtype_code = 'NB CANCEL')
-- join kf_core_policy pol on p.policynumber = pol.policynumber 
-- where date(pol.start_datetime_local) = date(pol.cancellation_datetime_local)
-- and month(policy_updated_datetime_local) = month(cancellation_datetime_local)
-- and year(policy_updated_datetime_local) = year(cancellation_datetime_local)
--  SELECT COUNT(*) FROM kf_core_policy pol 
--  where date(pol.start_datetime_local) = date(pol.cancellation_datetime_local)

-- union all
-- select 'C303' as metrics_id, 'Policy Mix' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable, 1 as RATE_NUMERATOR, sum(accept_indicator) over() as RATE_DENOMINATOR
-- from feature p
-- join proddwnlake_data_model.darwin_enquiry_event enq on enq.ihp_transaction_id = p.summaryreference
-- where accept_indicator = 1 and ((event_type = 'NB' and 
-- to_char(accept_date, 'yyyymm') = date_format(date_add('month', -1, current_date), '%Y%m'))
-- or (event_type = 'REN' and
-- to_char(policy_start_date, 'yyyymm') = date_format(date_add('month', -1, current_date), '%Y%m')))
-- union all
-- select 'C352' as metrics_id, 'Gross Retention Rate - Product & Service, Price & Value' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable, RATE_NUMERATOR, RATE_DENOMINATOR
-- from feature p
-- join (select policynumber, renew_tenure, to_char(date_add('year', renew_tenure, policy_created_datetime_local), 'yyyymm') as policy_month,
-- case when min(processtype_group) = 'INVITE' then 1 else 0 end as RATE_DENOMINATOR,
-- case when max(processtype_group) = 'RENEWAL' then 1 else 0 end as RATE_NUMERATOR
-- from renewals
-- group by 1, 2, 3) renew
-- on p.policynumber = renew.policynumber
-- and p.tenure = cast(renew.renew_tenure as varchar)
-- and renew.policy_month = date_format(date_add('month', -1, current_date), '%Y%m')
-- union all
-- select 'C356' as metrics_id, 'Number Of Renewals Coming Up In The Next Month' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, 'NA' as vulnerable, 0 as RATE_NUMERATOR, 0 as RATE_DENOMINATOR
-- from (select *, row_number() over(partition by policynumber) as rn from feature) p
-- join kf_core_policy pol using (policynumber)
-- where to_char(renewal_datetime_local, 'yyyymm') = date_format(date_add('month', 1, current_date), '%Y%m')
-- and rn = 1
-- -- join proddwnlake_data_model.darwin_enquiry_event enq on enq.ihp_transaction_id = p.summaryreference     --- policy_history next_renewal_date
-- -- where accept_indicator = 1 and event_type in ('REN')
-- where to_char(renewal_date, 'yyyymm') = date_format(date_add('month', 1, current_date), '%Y%m')
-- union all
-- select 'C305' as metrics_id, 'Loss Ratio' as metric_name, p.tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_method_code as payment_type, case when filled_forward = '1' then 'No' when filled_forward in ('2', '3', '4', '5') then 'Yes' else 'Not mapped' end as vulnerable, least(risk_premium, unprotected_final_premium_no_ipt) as RATE_NUMERATOR, unprotected_final_premium_no_ipt as RATE_DENOMINATOR
-- from feature p
-- join (select prm.transaction_id, prm.process_type, unprotected_final_premium_no_ipt, layer_value as risk_premium, prc.process_type
-- from (select transaction_id, unprotected_final_premium_no_ipt, 
-- case when process_type[1] = 'MTA_ORIGINAL' then 'MTA' 
-- when process_type[1] = 'RENEWAL_DISGUISED_AS_NB' then 'NEW_BUSINESS'
-- when process_type[1] = 'RENEWAL' then 'RENEWAL_AGED' else process_type[1] end as process_type
-- from ihp_premiums
-- where cardinality(process_type) > 0) prm
-- join (select transaction_id, layer_name, layer_value,
-- case when process_type in ('MTA_ORIGINAL', 'MTA_CURRENT') then 'MTA' else process_type end as process_type from ihp_pricing where layer_name = 'Risk_Premium') prc 
-- on prm.transaction_id = prc.transaction_id and prc.process_type = prm.process_type) ihp on ihp.transaction_id = p.summaryreference     --- policy_history next_renewal_date
-- where to_char(start_datetime_local, 'yyyymm') = date_format(date_add('month', -1, current_date), '%Y%m')
),
grouped as 
(
select metrics_id, metric_name, tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_type, vulnerable, sum(RATE_NUMERATOR) as RATE_NUMERATOR, sum(RATE_DENOMINATOR) as RATE_DENOMINATOR, count(*) as cnt
from metrics
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
),
final as (
select metrics_id, metric_name, tenure, policy_holder_age, credit_score, originating_channel, bound_channel, product_type, payment_type, vulnerable, RATE_NUMERATOR, RATE_DENOMINATOR, case when RATE_DENOMINATOR = 0 and RATE_NUMERATOR = 0 then cnt else RATE_NUMERATOR*100/RATE_DENOMINATOR end as METRIC_VALUE
from grouped
)
,check as (
select current_date as FILE_RECEIVED_DATE, 'Darwin' as SOURCE_SYSTEM_NAME, date_format(date_add('month', -1, current_date), '%Y-%m') as REPORTING_PERIOD, 'Motor' as business,  'Motor Darwin' as PRODUCT_LINE, 'DARWIN' as brand, * 
from final
)
select metric_name, 0, 0, sum(METRIC_VALUE) from check where RATE_DENOMINATOR = 0 group by 1
union all
select metric_name, avg(RATE_NUMERATOR), avg(RATE_DENOMINATOR), avg(METRIC_VALUE) from check where RATE_DENOMINATOR != 0 group by 1


-- select * from proddwnlake_data_model.darwin_enquiry_event where accept_indicator = 1 limit 10 group by 1, 2
-- select * from kf_core_busevent where policynumber = '00709228'   --processtype_code = 'NB CANCEL' limit 10
-- select count(*) --policynumber, p.policy_updated_datetime_local, busevent_created_date_local, p.busevent_created_datetime_local --policy_created_datetime_local, policy_updated_datetime_local, cancellation_datetime_local 
-- from kf_core_policy p 
-- where to_char(renewal_datetime_local, 'yyyymm') = date_format(date_add('month', 1, current_date), '%Y%m')
-- and policynumber is not null
-- -- join (select policynumber, max(busevent_created_date_local) as busevent_created_date_local 
-- from kf_core_busevent join kf_core_busevent_cloned using (businessevent_id) 
-- where processtype_code = 'NB CANCEL' group by 1) b using (policynumber)--where cancellation_datetime_local is not null
-- --where processtype_code = 'NB CANCEL'
-- where date(p.busevent_created_datetime_local) != date(busevent_created_date_local)
-- and cancellation_datetime_local is not null --and policynumber = '00709228'
-- limit 100
-- where date(start_datetime_local) = date(cancellation_datetime_local)
-- limit 10
-- and month(policy_updated_datetime_local) = month(cancellation_datetime_local)
-- and year(policy_updated_datetime_local) = year(cancellation_datetime_local) limit 10
-- SELECT table_name, column_name FROM information_schema.columns
-- where lower(column_name) like '%renewal%' and lower(table_name) like '%kf%' 
-- select count(*) --date(policy_date), date_add('day', -1, current_date), to_char(accept_date, 'yyyymm'), date_format(date_add('month', -1, current_date), '%Y%m')
-- from proddwnlake_data_model.darwin_enquiry_event enq
-- left join pol_ifp ifp on enq.policy_number = ifp.policy_number
-- and date(policy_date) = date_add('day', -1, current_date)
-- where accept_indicator = 1 and event_type = 'NB'
-- and to_char(accept_date, 'yyyymm') = date_format(date_add('month', -2, current_date), '%Y%m')
-- limit 1
-- select count(*) from kf_core_busevent 
-- where processtype_code like '%MTA%' 
-- and date(busevent_created_datetime_utc) between date('2025-04-01') and date('2025-04-30')


--new business last month : 17177
--last IFP : 287, 450
-- MTA - 37934  -- 27208
-- renewals next month - 29274

-- select count(*) from ihp_excesses excess
-- where excess_type = 7 and driver_position = 1