Untitled

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 
    sale_info.*,
    main_driver.*,
    main_vehicle.*
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  
where sale_info.policy_num = '10000004160257'