select T1.month, T1.order_count,
    100 * (T1.order_count - lag(T1.order_count, 1) over (order by T1.month)) / lag(T1.order_count, 1) over (order by T1.month) ||'%' as order_growth,
    T1.order_count/T2.hour_count as drh,
    T1.restaurants,
    100 * (T1.restaurants - lag(T1.restaurants, 1) over (order by T1.month)) / lag(T1.restaurants, 1) over (order by T1.month) ||'%' as restaurant_growth,
    T1.runners,
    100 * (T1.runners - lag(T1.runners, 1) over (order by T1.month)) / lag(T1.runners, 1) over (order by T1.month) ||'%' as runner_growth,
    T1.GR,
    to_char((100 * (T1.GR::numeric - lag(T1.GR::numeric, 1) over (order by T1.month)) / lag(T1.GR::numeric, 1) over (order by T1.month)), '999D99%') as GR_growth,
    cast(((T1.order_count * 1.50) + (4 * T2.hour_count))::numeric as money) as COGS,
    to_char((100 * (((T1.order_count * 1.50) + (4 * T2.hour_count)) - lag(((T1.order_count * 1.50) + (4 * T2.hour_count)), 1) over (order by T1.month))
        / lag(((T1.order_count * 1.50) + (4 * T2.hour_count)), 1) over (order by T1.month)), '999D99%') as COGS_growth,
    to_char((((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count))) / T1.GR::numeric::float8)*100.0), '999D99%') as GM,
    to_char((100 * ((((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count))) / T1.GR::numeric::float8)*100.0)
        - lag((((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count))) / T1.GR::numeric::float8)*100), 1) over (order by T1.month))
        / lag((((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count))) / T1.GR::numeric::float8)*100.0), 1) over (order by T1.month))
        , '999D99%') as GM_Growth,
    cast(((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count)))/T1.restaurants)::numeric as money) as GPPR,
    to_char((100 * (((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count)))/T1.restaurants)
        - lag(((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count)))/T1.restaurants), 1) over (order by T1.month))
        / lag(((T1.GR::numeric::float8 - ((T1.order_count * 1.50) + (4 * T2.hour_count)))/T1.restaurants), 1) over (order by T1.month))
        , '999D99%') as GPPR_Growth
from
(
    select
        to_char(transactions.delivered_at_est, 'YYYY-MM') || ' (' || trim(trailing ' ' from to_char(time_requested, 'Month')) || ')' as month,
        count(distinct transactions.id) as order_count,
        count(distinct transactions.seller_id) as restaurants,
        count(distinct transactions.runner_id) as runners,
        cast(SUM(daas_rate)::numeric as money) as GR
    from transactions
    join territories on transactions.territory_id = territories.id
    join businessprofiles on transactions.seller_id = businessprofiles.id
    where
        method = 'Delivery' and
        time_requested between ((now() + interval '-720 day')::date + interval '3 hours') and ((now()::date + interval '4 hours')) and
        status = 'completed' and
        transactions.is_test = false and
        transactions.company_name != 'Wawa' and transactions.company_name != 'TEST VENDOR'
    group by month
    order by month
) T1

join
(
    select
        to_char(shifts.start, 'YYYY-MM') || ' (' || trim(trailing ' ' from to_char(shifts.start, 'Month')) || ')' as month,
        sum(length_hours) as hour_count
    from shifts
    join retreat_points on shifts.retreat_point = retreat_points.id
    join territories on territories.id = retreat_points.territory_id
    where shifts.start between ((now() + interval '-720 day')::date + interval '3 hours') and ((now()::date + interval '4 hours')) and
        shifts.stop between ((now() + interval '-720 day')::date + interval '3 hours') and ((now()::date + interval '4 hours')) and
        shifts.role != 'dispatch' and
        shifts.user_id is not null
    group by month
    order by month
) T2 on T1.month = T2.month