Untitled

mail@pastecode.io avatar
unknown
sql
10 months ago
7.3 kB
6
Indexable
Never
SELECT a.* FROM (/*
    Split NB Data Set
*/
SELECT 
    /*
        KPIs Indexes
        - Policy Number
        - Transaction ID, linked to the Submission transactions in Bound status
        - Total Premium Amount, coming straight from GW.  
    */
    sales_master.policy_num,
    sales_master.transaction_id,
    sales_master.total_premium_amt,
    /*
        Dates
        - Purhcase and Effective Date 
    */    
    sales_master.purchase_dt,
    sales_master.policy_effective_dt,
    -- sales_master.eff_year,
    -- sales_master.eff_month,
    
    /*
        Attributes
        - Gender
        - Channel Generation Cluster
        - Age Main Driver Cluster
        - Main Car Age Cluster
        - Cu Class Cluster
        - Fuel Type Clustyer
    */
    sales_master.gender_desc,
    sales_master.channel_quote_generation,
    CASE 
        WHEN sales_master.channel_quote_generation in ('Facile.it','Assicurazione.it') then 'Facile'
        WHEN sales_master.channel_quote_generation in ('Internet (Online quote engine)') then 'Web'
        WHEN sales_master.channel_quote_generation in ('Cercassicurazione.it Plus','Segugio') then 'Segugio'
        WHEN sales_master.channel_quote_generation in ('Fast Quote') then 'Fast_Quote'
        WHEN sales_master.channel_quote_generation in ('Instant Quote') then 'Instant_Quote'
        WHEN sales_master.channel_quote_generation in ('6Sicuro','Chiarezza.it','ComparaMeglio','SOSTariffe','SuperMoney.eu','Telefono') then 'Altro'
        else 'z. XXX' 
    END as channel_quote_generation_cl, 
    sales_master.main_driver_age,
    CASE 
        WHEN sales_master.main_driver_age >=18 AND sales_master.main_driver_age<=27 THEN 'a. 18-27'
        WHEN sales_master.main_driver_age >=28 AND sales_master.main_driver_age<=35 THEN 'b. 28-35'
        WHEN sales_master.main_driver_age >=36 AND sales_master.main_driver_age<=45 THEN 'c. 36-45'
        WHEN sales_master.main_driver_age >=46 AND sales_master.main_driver_age<=60 THEN 'd. 46-60'
        WHEN sales_master.main_driver_age >=61 THEN 'e. 61+'
        ELSE 'z. XXX' 
    END as main_driver_age_cl,
    sales_master.cu_class_cd,
    CASE
        WHEN sales_master.cu_class_cd = 1 THEN 'a. 1'
        WHEN sales_master.cu_class_cd >=2 and sales_master.cu_class_cd <=3 THEN 'b. 2-3'
        WHEN sales_master.cu_class_cd >=4 and sales_master.cu_class_cd <=9 THEN 'c. 4-9'
        WHEN sales_master.cu_class_cd >=10 and sales_master.cu_class_cd <=13 THEN 'd. 10-13'
        WHEN sales_master.cu_class_cd = 14 THEN 'e. 14'
        WHEN sales_master.cu_class_cd >=15 THEN 'f. 15-18'
        ELSE 'z. XXX' 
    end as cu_class_cl,
    /*

        TODO: Main Vehicle Age Group

    */
    sales_master.main_vehicle_age,
    sales_master.fuel_type_desc,
    CASE    
        WHEN sales_master.fuel_type_desc  in ('Ibrida','Ibrido Benzina/Elettrica','Ibrido Gasolio/Elettrica') THEN 'Ibrida'
        WHEN sales_master.fuel_type_desc  = 'Elettrica' then 'Elettrica'
        WHEN sales_master.fuel_type_desc  = 'Metano' then 'Metano'
        WHEN sales_master.fuel_type_desc  = 'GPL' then 'GPL'
        WHEN sales_master.fuel_type_desc  = 'Diesel' then 'Diesel'
        WHEN sales_master.fuel_type_desc  = 'Benzina' then 'Benzina'
        ELSE 'z. XXX'
    end as fuel_cl
FROM (
    /*
        Master Table 
    */
    with sale_info as (
        SELECT 
            -- IDs
            transaction_id,
            vehicle_id_fk,
            policy_term_key,
            policy_period_key,
            transaction_key,
            policy_num,
            -- Dates 
            policy_effective_dt,
            transaction_issue_dts,
            submission_dts,
            create_dts,
            purchase_dt,
            -- Info of transaction 
            transaction_type_cd,
            status_cd,
            -- Origin
            open_channel_detail_desc,
            current_channel_cd,
            open_channel_cd,
            close_channel_cd,
            -- Premium
            total_cost_amt,
            total_premium_amt,
            last_version_fl
        FROM pol_policy_tran
        WHERE 
            transaction_type_cd = 'Submission' and status_cd = 'Bound'
            AND purchase_dt >= TIMESTAMP '2023-01-01' AND  purchase_dt < TIMESTAMP '2024-01-01'
    ),
    main_driver AS (
        SELECT 
            policy_party_id,
            transaction_id_fk,
            policy_period_key,
            last_version_fl,
            pol_party_role_type_cd,
            -- Age
            birth_dt,
            -- Geo
            birth_country_name,
            birth_city_name,
            birth_province_name,
            residence_postal_code,
            -- Status 
            marital_status_cd,
            marital_status_desc,
            -- Gender
            gender_cd,
            gender_desc,
            -- occupation
            occupation,
            --License
            driver_license_year,
            driver_license_month_cd,
            qualification_cd,
            qualification_desc
        FROM pol_policy_party 
        WHERE pol_party_role_type_cd = 'MAIN_DRIVER' AND gender_desc in ('Male', 'Female')
    ),
    main_vehicle as (
        SELECT 
            policy_period_key,
            vehicle_id,
            cast(cu_class_cd AS INT) AS cu_class_cd,
            curr_insurance_situ_cd,
            curr_insurance_situ_desc,
            vehicle_grv_group,
            vehicle_own_insured_years,
            fnb_fl,
            vehicle_purchased_year,
            car_usage_cd,
            car_usage_desc,
            vehicle_type_cd,
            vehicle_type_desc,
            fuel_type_cd,
            fuel_type_desc
        FROM pol_vehicle
        where vehicle_number = 1 
    )    
    SELECT 
        policy_num,
        transaction_id,
        transaction_type_cd,
        status_cd,
        -- Dates 
        sale_info.purchase_dt,
        sale_info.policy_effective_dt,
        date_format(sale_info.purchase_dt, '%Y-%m-%d') as eff_date,
        -- CAST(date_format(sale_info.purchase_dt, '%Y') as int) eff_year,
        -- CAST(date_format(sale_info.purchase_dt, '%m') as int) eff_month,
        -- CAST(date_format(sale_info.purchase_dt, '%v') as int) eff_week_of_year,
        CAST(date_format(sale_info.purchase_dt, '%Y') as int) - CAST(date_format(main_driver.birth_dt, '%Y') as int) as main_driver_age,
        CAST(date_format(sale_info.purchase_dt, '%Y') as int) - CAST(main_vehicle.vehicle_purchased_year as int) as main_vehicle_age,
        open_channel_detail_desc AS channel_quote_generation,
        open_channel_cd,
        close_channel_cd,
        total_premium_amt,
        birth_country_name,
        marital_status_desc,
        cu_class_cd,
        gender_desc,
        main_vehicle.fuel_type_desc,
        main_vehicle.vehicle_purchased_year
    FROM sale_info
    LEFT JOIN main_driver ON main_driver.transaction_id_fk = sale_info.transaction_id
    LEFT JOIN main_vehicle ON main_vehicle.policy_period_key = sale_info.policy_period_key
)
as sales_master ) a 
where a.policy_num = '10000004160257'
Leave a Comment