Untitled
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