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 
        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  
    where sale_info.policy_num = '10000004160257'