WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'application' AS app,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'taxi_status' as taxi_status,
        dj -> 'order' ->> 'discount' as discount
    FROM orders
)

SELECT date_trunc('day', created_dttm) AS day,
       avg(cost) AS mean_cost,
       COUNT(*) AS total_rides
FROM parsed_orders
WHERE taxi_status = 'complete' and discount is not null
GROUP BY day
ORDER BY day;

-- discount
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'application' AS app,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'taxi_status' as taxi_status,
        (dj -> 'order' -> 'discount' ->> 'price')::decimal as orig_price,
        (dj -> 'order' -> 'discount' ->> 'value')::decimal as discount_coef
    FROM orders
)
SELECT date_trunc('day', created_dttm) AS day,
       sum(orig_price * discount_coef),
    sum(cost) as cost_sum,
    sum(orig_price) as sum_or
FROM parsed_orders
WHERE taxi_status = 'complete' and orig_price is not null
group by day



--------------------------------------------------
----- revenue ------------------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'taxi_status' as taxi_status,
        (dj -> 'order' -> 'discount' ->> 'price')::decimal as orig_price,
        (dj -> 'order' -> 'discount' ->> 'value')::decimal as discount_coef,
        (dj -> 'order' -> 'coupon' ->> 'value')::decimal as coupon_value,
        (dj -> 'order' -> 'coupon' ->> 'was_used')::boolean as coupon_used
    FROM orders
),
revenues as (
    select
           cost,
           created_dttm,
           taxi_status,
           coalesce(orig_price, 0) as orig_price,
           coalesce(discount_coef, 0) as discount_coef,
           coalesce(coupon_value, 0) as coupon_value,
           coalesce(coupon_used, FALSE) as coupon_used
    from parsed_orders
)
SELECT
     date_trunc('day', created_dttm) AS day,
     sum(orig_price * discount_coef
             + cast(coupon_used as int) * (cast(coupon_value >= cost as int) * cost
                                    + cast(coupon_value < cost as int) * coupon_value)) as discount_sum,
      sum(cost + orig_price * discount_coef) as revenue_sum,
     sum(
                 cast(cost < 100 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.9 + cast(orig_price = 0 as int) * cost * 0.9) +
                 cast(cost > 100 and cost < 200 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.874 + cast(orig_price = 0 as int) * cost * 0.874) +
                 cast(cost > 200 and cost < 300 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.863 + cast(orig_price = 0 as int) * cost * 0.863) +
                 cast(cost > 300 and cost < 400 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.857 + cast(orig_price = 0 as int) * cost * 0.857) +
                 cast(cost > 400 and cost < 500 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.854 + cast(orig_price = 0 as int) * cost * 0.854) +
                 cast(cost > 500 and cost < 600 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.851 + cast(orig_price = 0 as int) * cost * 0.851) +
                 cast(cost > 600 and cost < 700 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.85 + cast(orig_price = 0 as int) * cost * 0.85) +
                 cast(cost > 700 and cost < 800 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.848 + cast(orig_price = 0 as int) * cost * 0.848) +
                 cast(cost > 800 and cost < 900 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.847 + cast(orig_price = 0 as int) * cost * 0.847) +
                 cast(cost > 900 and cost < 1000 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.846 + cast(orig_price = 0 as int) * cost * 0.846) +
                 cast(cost > 1000 as int) *
                 (cast(orig_price > 0 as int) * orig_price * 0.844 + cast(orig_price = 0 as int) * cost * 0.844)
         ) as taxi_comission_sum
FROM
    revenues
WHERE
     taxi_status = 'complete'
 group by
     day, taxi_status