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