WITH retail_data AS (
    SELECT t1.order_id, t1.fulfillment_end_area_code, t1.latest_order_status, t2.statename,
    year(date_parse(date_format(from_unixtime(t1.order_created_at / 1000000), '%Y-%m-%d'), '%Y-%m-%d')) year,
    CASE
        WHEN month(date_parse(date_format(from_unixtime(t1.order_created_at / 1000000), '%Y-%m-%d'), '%Y-%m-%d')) IN (1, 2, 3) THEN 'Q1'
        WHEN month(date_parse(date_format(from_unixtime(t1.order_created_at / 1000000), '%Y-%m-%d'), '%Y-%m-%d')) IN (4, 5, 6) THEN 'Q2'
        WHEN month(date_parse(date_format(from_unixtime(t1.order_created_at / 1000000), '%Y-%m-%d'), '%Y-%m-%d')) IN (7, 8, 9) THEN 'Q3'
        WHEN month(date_parse(date_format(from_unixtime(t1.order_created_at / 1000000), '%Y-%m-%d'), '%Y-%m-%d')) IN (10, 11, 12) THEN 'Q4'
    END AS quarter
    FROM hudi_order_fulfillment_orders t1 LEFT JOIN pincode_state_mapping t2
    ON (t1.fulfillment_end_area_code = t2.pincode)
),
retail_agg AS (
    SELECT
        fulfillment_end_area_code,
        year,
        quarter,
        statename,
        COUNT(order_id) num_orders,
        SUM(CASE WHEN LOWER(latest_order_status) = 'completed' THEN 1 ELSE 0 END) AS num_completed_orders,
        SUM(CASE WHEN LOWER(latest_order_status) = 'in-progress' THEN 1 ELSE 0 END) AS num_in_progress_orders,
        SUM(CASE WHEN LOWER(latest_order_status) = 'cancelled' THEN 1 ELSE 0 END) AS num_cancelled_orders
    FROM retail_data
    GROUP BY fulfillment_end_area_code, year, quarter, statename
)
SELECT t1.fulfillment_end_area_code, t1.year, t1.quarter, t1.statename, t1.num_orders, t1.num_completed_orders, t1.num_in_progress_orders, t1.num_cancelled_orders, t2.lat, t2.long
FROM
    retail_agg t1
LEFT JOIN
    fulfillment_end_area_code_coordinates_mapping t2
ON (t1.fulfillment_end_area_code = t2.pincode)