----------------------------------------------------------
----- DAU ------------------------------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'user_id' AS user_id,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'city' AS city
    FROM orders
)

select
    city,
    date_trunc('day', created_dttm) as day,
    count(user_id)
from
    parsed_orders
group by
    city, day
order by
    city, day;

-------------------------------------------------------
----- MAU ---------------------------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'user_id' AS user_id,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'city' AS city
    FROM orders
)

select
    city,
    date_trunc('month', created_dttm) as month,
    count(user_id)
from
    parsed_orders
group by
    city, month
order by
    city, month;



-------------------------------------------------
----- жалобы ------------------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj -> 'order' -> 'feedback' -> 'choices' ->> 'low_rating_reason' as lr_reason,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'city' AS city
    FROM orders
),
reasons as (
    SELECT
           city,
           unnest(string_to_array(substring(lr_reason, 3, length(lr_reason) - 4), '", "')) as complain,
           created_dttm
    FROM parsed_orders
    WHERE lr_reason is not null
)

select
    city,
    date_trunc('day', created_dttm) as day,
    complain,
    count(complain)
from
     reasons
group by
    city, day, complain
order by
    city, day;


------------------------------------------------
----- колл-центр ------------------------------
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' ->> 'city' AS city
    FROM orders
)

SELECT
    city,
    date_trunc('day', created_dttm) AS day,
    avg(cost) AS mean_cost,
    COUNT(*) AS total_calls
FROM parsed_orders
WHERE app = 'callcenter'
GROUP BY city, day, app
ORDER BY city, day



-------------------------------------------------------
----- прогноз и реальное время в пути -----------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        (dj -> 'order' -> 'calc' ->> 'time')::decimal AS ml_time,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> 0 ->> 'c')::timestamp as cr_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -1 ->> 'c')::timestamp as finish_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -1 ->> 't') as finish_status,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -2 ->> 'c')::timestamp as riding_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -2 ->> 't') as riding_status,
        dj -> 'order' ->> 'city' AS city
    FROM orders
)

select
    city,
    avg(ml_time - date_part('minute', (finish_time - riding_time)) * 60 + date_part('second', (finish_time - riding_time))) as time_difference,
    date_trunc('day', created_dttm) as day
from parsed_orders
where finish_status = 'complete' and ml_time < 9000
group by city, day


-------------------------------------------------------
----- среднее время оидания ---------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> 0 ->> 'c')::timestamp as cr_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 'c')::timestamp as waiting_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 't') as waiting_status,
        dj -> 'order' ->> 'city' as city
    FROM orders
)

select
       city,
       avg(date_part('minute', waiting_time - cr_time) * 60 + date_part('second', waiting_time - cr_time)) as avg_waiting_time,
       date_trunc('day', created_dttm) as day
from parsed_orders
where waiting_status = 'waiting'
group by city, day;

-- ожидание с параметром
WITH orders AS (
SELECT
doc::json AS dj
FROM common.order_proc_rotax
LIMIT 100000
),
parsed_orders AS (
SELECT
    dj ->> '_id' AS id,
    (dj ->> 'created')::TIMESTAMP AS created,
    (dj -> 'performer' ->> 'driver_id') AS driver_id,
    dj -> 'order' ->> 'user_id' AS user_id,
    dj -> 'order' -> 'request' ->> 'offer' AS offer_id,

    dj -> 'order' ->> 'city' AS city,
    (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> 0 ->> 'c')::timestamp as cr_time,
    (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 'c')::timestamp as waiting_time,
    (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 't') as waiting_status,
       dj -> 'order_info' ->> 'statistics' as stat,

    dj -> 'order' ->> 'status' AS status,
    dj -> 'order' ->> 'taxi_status' AS taxi_status,
    dj -> 'order' -> 'creditcard' -> 'tips' AS tips,
    dj -> 'order' -> 'request' -> 'requirements' ->> 'childchair_moscow' AS child_chair,
    (dj -> 'order' -> 'request' -> 'requirements' ->> 'ski')::BOOLEAN AS ski,
    (dj -> 'order' -> 'request' -> 'requirements' ->> 'animaltransport')::BOOLEAN AS animals
FROM orders
)

SELECT
    DATE_TRUNC('day', created) AS DAY,
    city,
    avg(date_part('minute', waiting_time - cr_time) * 60 + date_part('second', waiting_time - cr_time)) as avg_waiting_time
FROM
    parsed_orders
WHERE
    parsed_orders.child_chair IS NOT NULL and waiting_status = 'waiting'
GROUP BY
    DAY, city;



-------------------------------------------------------------
----- чаевые ------------------------------------------------

-- средняя величина чаевых
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'application' AS app,
        dj -> 'order' -> 'creditcard' -> 'tips' ->> 'type' as tips_type,
        (dj -> 'order' -> 'creditcard' -> 'tips' ->> 'value')::decimal as tips_value,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'city' AS city
    FROM orders
)
SELECT
       city,
       app,
       tips_type,
       avg(tips_value),
       date_trunc('day', created_dttm) as day
FROM parsed_orders
where tips_type is not null and tips_value > 0
GROUP BY city, day, tips_type, app
ORDER BY city, app;

-- сколько людей оставили чаевые и сколько не оставили
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj -> 'order' ->> 'application' AS app,
        dj -> 'order' -> 'creditcard' -> 'tips' ->> 'type' as tips_type,
        (dj -> 'order' -> 'creditcard' -> 'tips' ->> 'value')::decimal as tips_value,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' ->> 'city' AS city
    FROM orders
)
SELECT
       city,
       app,
       count(app),
       tips_value > 0 as tips_exist,
    date_trunc('day', created_dttm) as day
FROM parsed_orders
where tips_type is not null
GROUP BY city, day, app, tips_exist
ORDER BY city, day


-------------------------------------------------------------
----- клиенты с Я.плюсом ------------------------------------
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj -> 'order' ->> 'calc_total')::DECIMAL AS calc_total,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        dj -> 'order' -> 'price_modifiers' -> 'items' -> 0 ->> 'reason' as ya_plus
    FROM orders
)

SELECT
    date_trunc('day', created_dttm) as day, count(ya_plus) as ya_plus_total
FROM parsed_orders
where ya_plus = ya_plus
group by day
order by day