Merge Monthly

WITH order_order AS (
    SELECT *
    FROM
        (
            SELECT f.*, ROW_NUMBER() OVER(partition BY id ORDER BY modified_at DESC) AS rn
            FROM pathao_food.order_order AS f
            WHERE modified_at >= '2019-01-01'
        )
    WHERE rn = 1
),
restaurant_restaurant AS (
    SELECT *
    FROM
        (
            SELECT f.*, ROW_NUMBER() OVER(partition BY id ORDER BY modified_at DESC) AS rn
            FROM pathao_food.restaurant_restaurant AS f
            WHERE modified_at >= '2017-01-01'
        )
    WHERE rn = 1
),

order_orderevent AS (
    SELECT *
    FROM
        (
            SELECT f.*, ROW_NUMBER() OVER(partition BY id ORDER BY TIME DESC) AS rn
            FROM pathao_food.order_orderevent AS f
            WHERE TIME >= '2019-01-01'
        )
    WHERE rn = 1
),
dump1 AS (
SELECT a.*, b.assignment_
FROM
    (SELECT
    EXTRACT(YEAR FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_year,
    EXTRACT(MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))AS event_month,
    COUNT(DISTINCT (CASE WHEN a.STATUS = 'DELIVERED' THEN a.restaurant_id END))active_restaurants,
    COUNT(DISTINCT (CASE WHEN a.STATUS = 'DELIVERED' AND b.is_partner = TRUE THEN a.restaurant_id END))active_partner_restaurants,
    COUNT(DISTINCT (a.tracking_id))total_request,
    COUNT(DISTINCT (CASE WHEN c.STATUS = 'ACCEPTED' THEN c.order_id END))accepted_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS  = 'DELIVERED' THEN a.tracking_id END))delivered_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS  = 'DELIVERED' THEN a.tracking_id END))/COUNT(DISTINCT (CASE WHEN a.STATUS  = 'DELIVERED' THEN EXTRACT (HOUR FROM (timestamp_add(a.created_at, INTERVAL 6 HOUR)))END)) hourly_delivered_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS  = 'DELIVERED' AND promo_code IS NOT NULL THEN a.tracking_id END))delivered_promo_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS = 'DELIVERED' AND b.is_partner = TRUE THEN a.tracking_id END))partner_resto_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS  = 'CANCELED' THEN a.tracking_id END))cancelled_order,
    COUNT(DISTINCT (CASE WHEN a.STATUS = 'CANCELED' AND b.is_partner = TRUE THEN a.tracking_id END))partner_resto_cancelled_order

    FROM order_order a, restaurant_restaurant b, order_orderevent c
    WHERE a.restaurant_id = b.id AND CAST(a.id AS string) = CAST(c.order_id AS string) AND DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{ start_date}}' AND '{{ end_date }}' AND a.city = 1
    GROUP BY 1,2)a

LEFT JOIN

    (SELECT EXTRACT(MONTH FROM a.event_time) AS event_month, COUNT(DISTINCT tracking_id)assignment_
    FROM
        (SELECT a.*
        FROM
                (SELECT timestamp_add(a.created_at, INTERVAL 6 HOUR)event_time,tracking_id, driver

                FROM order_order a
                CROSS JOIN UNNEST(split(drivers_tried, ',') )  AS driver
                WHERE DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{ start_date}}' AND '{{ end_date }}' AND a.city = 1)a
        WHERE driver <> '')a
    GROUP BY 1)b
ON a.event_month = b.event_month
ORDER BY 1,2
),
dump2 AS (
    SELECT
EXTRACT(YEAR FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_year,
EXTRACT(MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR))) AS event_month,
SUM(CASE WHEN delivery_fee = 0 THEN total_amount + estimated_delivery_fee ELSE total_amount END)/85 gmv_in_usd,
(SUM(CASE WHEN delivery_fee = 0 THEN total_amount + estimated_delivery_fee ELSE total_amount END)/85)/COUNT(tracking_id) gmv_per_order_in_usd,
SUM(CASE WHEN delivery_fee = 0 THEN promo_deductible_amount + estimated_delivery_fee ELSE promo_deductible_amount END)/85 total_burn_in_usd,
SUM(CASE WHEN free_delivery = TRUE THEN estimated_delivery_fee END)/84.5 free_delivery_burn_usd,
(SUM(CASE WHEN delivery_fee = 0 THEN promo_deductible_amount + estimated_delivery_fee ELSE promo_deductible_amount END)/85)/COUNT(tracking_id) burn_per_order_in_usd,
(SUM(CASE WHEN delivery_fee = 0 THEN promo_deductible_amount + estimated_delivery_fee ELSE promo_deductible_amount END)*100/SUM(CASE WHEN delivery_fee = 0 THEN total_amount + estimated_delivery_fee ELSE total_amount END)) promo_prsnt_of_gmv
FROM order_order a, restaurant_restaurant b
WHERE STATUS = 'DELIVERED' AND a.restaurant_id = b.id AND DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{ start_date}}' AND '{{ end_date }}' AND a.city = 1
GROUP BY 1,2
ORDER BY 1,2
),
dump3 AS (
    SELECT a.*, b.user_first_trip, c.unserved_first_trip_user
    FROM
        (SELECT
        EXTRACT(YEAR FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_year,
        EXTRACT(MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR))) AS event_month,
        --date(timestamp_add(a.created_at, interval 6 hour))event_date,
        COUNT(DISTINCT a.USER) requesting_user,
        COUNT(tracking_id)/COUNT(DISTINCT a.USER) request_per_user,
        COUNT(DISTINCT (CASE WHEN STATUS = 'DELIVERED' THEN a.USER END)) served_user
        FROM order_order a
        WHERE DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}' AND a.city = 1
        GROUP BY 1,2)a

    LEFT JOIN

        (SELECT EXTRACT(MONTH FROM a.ftd)  AS mon, COUNT(a.USER) user_first_trip
        FROM
            (SELECT a.*
            FROM order_order b,
                (SELECT a.USER, DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) ftd, RANK() OVER (PARTITION BY a.USER ORDER BY timestamp_add(a.created_at, INTERVAL 6 HOUR) ASC) AS rank, tracking_id
                FROM order_order a
                WHERE a.city = 1)a
            WHERE a.tracking_id = b.tracking_id AND b.STATUS = 'DELIVERED' AND rank = 1)a
        WHERE a.ftd BETWEEN '{{start_date}}' AND '{{end_date}}'
        GROUP BY 1)b

    ON a.event_month = b.mon


    LEFT JOIN

        (SELECT EXTRACT(MONTH FROM a.ftd)  AS moon, COUNT(a.USER) unserved_first_trip_user
        FROM
            (SELECT a.*
            FROM order_order b,
                (SELECT a.USER, DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) ftd, RANK() OVER (PARTITION BY a.USER ORDER BY timestamp_add(a.created_at, INTERVAL 6 HOUR) ASC) AS rank, tracking_id
                FROM order_order a
                WHERE a.city = 1)a
            WHERE a.tracking_id = b.tracking_id AND b.STATUS = 'CANCELED' AND rank = 1)a
        WHERE a.ftd BETWEEN '{{start_date}}' AND '{{end_date}}'
        GROUP BY 1)c

    ON a.event_month = c.moon
    ORDER BY a.event_month
),
dump4 AS (
    SELECT a.*,b.assigned_driver
FROM
    (SELECT a.*, b.avg_delivered_time_in_min, b.avg_accepted_time_in_min
    FROM
        (SELECT
        EXTRACT(YEAR FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_year,
        EXTRACT(MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR))) AS event_month,
        --date(timestamp_add(a.created_at, interval 6 hour))event_date,
        COUNT(DISTINCT driver)total_driver,
        COUNT(DISTINCT (CASE WHEN STATUS = 'DELIVERED' THEN driver END))active_driver
        FROM order_order a
        WHERE DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}' AND city = 1
        GROUP BY 1,2)a

    RIGHT JOIN


            (SELECT a.*, b.avg_accepted_time_in_min
            FROM
                (SELECT a.event_month, (avg_delivered_time)/60 AS avg_delivered_time_in_min
                FROM

                    (SELECT EXTRACT (MONTH FROM a.event_date)  AS event_month, SUM(timestamp_diff(delivered_time, accepted_time,SECOND))/COUNT(tracking_id) avg_delivered_time
                    FROM
                        (SELECT a.*, timestamp_add(c.TIME, INTERVAL 6 HOUR) accepted_time
                        FROM order_orderevent c,
                            (SELECT c.order_id,
                            a.tracking_id,
                            DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) event_date,
                            timestamp_add(c.TIME, INTERVAL 6 HOUR) delivered_time
                            FROM order_order a, order_orderevent c
                            WHERE CAST(a.id AS string) = CAST(c.order_id AS string) AND c.STATUS = 'DELIVERED' AND a.city = 1
                            )a
                        WHERE a.order_id = c.order_id AND c.STATUS = 'ACCEPTED')a
                    WHERE a.event_date BETWEEN '{{start_date}}' AND '{{end_date}}'
                    GROUP BY 1)a
            )a,
                (SELECT a.event_month, (avg_delivered_time)/60 AS avg_accepted_time_in_min
                FROM

                (SELECT EXTRACT (MONTH FROM a.event_date) AS event_month, SUM(timestamp_diff(accepted_time,assigned_time,SECOND))/COUNT(tracking_id) avg_delivered_time
                FROM
                        (SELECT a.*, timestamp_add(c.TIME, INTERVAL 6 HOUR) assigned_time
                        FROM order_orderevent c,
                            (SELECT c.order_id,
                            a.tracking_id,
                            DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) event_date,
                            timestamp_add(c.TIME, INTERVAL 6 HOUR) accepted_time
                            FROM order_order a, order_orderevent c
                            WHERE CAST(a.id AS string) = CAST(c.order_id AS string) AND c.STATUS = 'ACCEPTED' AND a.city = 1
                            )a
                        WHERE a.order_id = c.order_id AND c.STATUS = 'ASSIGNED')a
                    WHERE a.event_date BETWEEN '{{start_date}}' AND '{{end_date}}'
                    GROUP BY 1)a
            )b
            WHERE a.event_month = b.event_month)b



    ON a.event_month = b.event_month)a

RIGHT JOIN

     (SELECT EXTRACT(MONTH FROM a.event_date) AS event_month, COUNT(DISTINCT driver_id)assigned_driver
        FROM
            (SELECT a.*
            FROM
                    (SELECT DATE(timestamp_add(created_at, INTERVAL 6 HOUR))event_date,tracking_id, driver_id
                    FROM order_order
                    CROSS JOIN UNNEST(split(drivers_tried, ',')) AS driver_id
                    WHERE DATE(timestamp_add(created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}' AND city = 1)a
            WHERE driver_id <> '')a
        GROUP BY 1)b
ON a.event_month = b.event_month
ORDER BY 1,2
),
dump5 AS (
    SELECT a.*,c.avg_driver_being_tried_accepted_orders,b.avg_user_cancellation_time,b.avg_driver_Cancellation_time,b.avg_dispatch_time
FROM
    (SELECT
    EXTRACT(YEAR FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_year,
    EXTRACT(MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)))event_month,
    -- extract(week from date(timestamp_add(a.created_at, interval 6 hour)))+1 as event_week,
    -- date(timestamp_add(a.created_at, interval 6 hour))event_date,
    CAST(SUM(CASE WHEN STATUS = 'CANCELED' AND  a.driver = cancelled_by THEN 1 ELSE 0 END) AS NUMERIC)*100 / SUM(CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) Driver_Cancellation_prsnt ,
    CAST(SUM(CASE WHEN STATUS = 'CANCELED' AND  cancelled_by=-1 AND drivers_tried IS NULL THEN 1 ELSE 0 END) AS NUMERIC)*100 / SUM(CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) No_driver_Available_Cancellation_prsnt,
    CAST(SUM(CASE WHEN  STATUS = 'CANCELED' AND cancelled_by=-1 AND drivers_tried IS NOT NULL THEN 1 ELSE 0 END) AS NUMERIC)*100 / SUM(CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) No_driver_Accepted_Cancellation_prsnt,
    CAST(SUM(CASE WHEN STATUS = 'CANCELED' AND cancelled_by = 0 OR cancelled_by IS NULL AND STATUS = 'CANCELLED' THEN 1 ELSE 0 END) AS NUMERIC)*100 / SUM(CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) ER_Cancellation_prsnt,
    CAST(SUM(CASE WHEN  a.USER = cancelled_by THEN 1 ELSE 0 END) AS NUMERIC)*100 / SUM(CASE WHEN STATUS = 'CANCELED' THEN 1 ELSE 0 END) User_Cancellation_prsnt,
    avg(CASE WHEN STATUS = 'DELIVERED' THEN (CHAR_LENGTH(drivers_tried) - CHAR_LENGTH(REPLACE(drivers_tried, ',', ''))) / CHAR_LENGTH(',') END) avg_drivers_being_tried_delivered_orders,
    avg(CASE WHEN STATUS = 'CANCELED' THEN (CHAR_LENGTH(drivers_tried) - CHAR_LENGTH(REPLACE(drivers_tried, ',', ''))) / CHAR_LENGTH(',') END) avg_drivers_being_tried_cancelled_orders
    FROM order_order  A
    WHERE DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}'
    AND A.city= 1
    GROUP BY 1,2)a

LEFT JOIN

    (SELECT EXTRACT(MONTH FROM event_date) AS Months, avg(CASE WHEN cancellation_time>0 THEN cancellation_time ELSE NULL END)  avg_user_cancellation_time,avg(dispatch_time) avg_dispatch_time,avg(d_cancellation_time) avg_driver_Cancellation_time
    FROM
        (SELECT DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) event_date,b.*,
        CASE WHEN b.STATUS = 'CANCELED' AND a.cancelled_by = a.USER  THEN timestamp_diff(b.TIME, a.created_at,SECOND) ELSE 0 END cancellation_time,
        CASE WHEN b.STATUS = 'CANCELED' AND a.cancelled_by = a.driver  THEN timestamp_diff(b.TIME, a.created_at,SECOND) ELSE 0 END d_cancellation_time,
        CASE WHEN b.STATUS = 'ACCEPTED' THEN timestamp_diff(b.TIME, a.created_at,SECOND) ELSE 0 END dispatch_time
        FROM order_order a, order_orderevent b
        WHERE  CAST(a.id AS string ) = CAST(b.order_id AS string)
        AND b.STATUS IN ('CANCELED','ACCEPTED')
        AND a.city= 1
        AND DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}') a
    GROUP BY 1)b
ON a.event_month = b.Months

LEFT JOIN

    (SELECT
EXTRACT (MONTH FROM DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR))) AS event_month,
avg((CHAR_LENGTH(drivers_tried) - CHAR_LENGTH(REPLACE(drivers_tried, ',', ''))) / CHAR_LENGTH(',')) avg_driver_being_tried_accepted_orders
FROM order_order a
WHERE drivers_tried IS NOT NULL
AND DATE(timestamp_add(a.created_at, INTERVAL 6 HOUR)) BETWEEN '{{start_date}}' AND '{{end_date}}'
GROUP BY 1)c
ON a.event_month = c.event_month
ORDER BY a.event_month
),
dump6 AS (
    SELECT event_month, SUM (driver_count) AS driver_count
FROM
(SELECT EXTRACT (MONTH FROM first_ride_date) AS event_month,a.*
FROM
(
    SELECT first_ride_date,COUNT(*) driver_count
    FROM
    (
        SELECT order_order.driver ,DATE(MIN(timestamp_add(created_at, INTERVAL 6 HOUR))) first_ride_date
        FROM order_order
        WHERE STATUS = 'DELIVERED'
        AND city = 1
        GROUP BY 1
    ) A
    GROUP BY 1
) A
WHERE first_ride_date BETWEEN '{{start_date}}' AND '{{end_date}}'

ORDER BY 1)
GROUP BY 1
ORDER BY 1
)

    SELECT
        dump1.*,
        dump2.gmv_in_usd ,
        dump2.gmv_per_order_in_usd ,
        dump2.total_burn_in_usd ,
        dump2.free_delivery_burn_usd ,
        dump2.burn_per_order_in_usd ,
        dump2.promo_prsnt_of_gmv ,
        dump3.requesting_user ,
        dump3.request_per_user ,
        dump3.served_user ,
        dump3.user_first_trip ,
        dump3.unserved_first_trip_user ,
        dump4.total_driver ,
        dump4.active_driver ,
        dump4.avg_delivered_time_in_min ,
        dump4.avg_accepted_time_in_min ,
        dump4.assigned_driver ,
        dump5.Driver_Cancellation_prsnt ,
        dump5.No_driver_Available_Cancellation_prsnt ,
        dump5.No_driver_Accepted_Cancellation_prsnt ,
        dump5.ER_Cancellation_prsnt ,
        dump5.User_Cancellation_prsnt ,
        dump5.avg_drivers_being_tried_delivered_orders ,
        dump5.avg_drivers_being_tried_cancelled_orders ,
        dump5.avg_driver_being_tried_accepted_orders ,
        dump5.avg_user_cancellation_time ,
        dump5.avg_driver_Cancellation_time ,
        dump5.avg_dispatch_time ,
        dump6.driver_count
    FROM dump1
    JOIN dump2
        ON dump2.event_month = dump1.event_month
    JOIN dump3
        ON dump3.event_month = dump1.event_month
    JOIN dump4
        ON dump4.event_month = dump1.event_month
    JOIN dump5
        ON dump5.event_month = dump1.event_month
    JOIN dump6
        ON dump6.event_month = dump1.event_month