Изм-е ROPO транз- по дням с 010519-310519 с конверс-м окном

#standardSQL
--Изменение ROPO транзакций по дням с 1 мая 2019 по 31 мая 2019 с конверсионным окном 2 недели
SELECT
            metric_online_only, --количество транзакций ONLINE
            metric_offline_only, --количество транзакций OFFLINE
            metric_ropo, --количество транзакций ROPO
    DATE

FROM
(
    SELECT
        DATE,


        transactions_count_online_only AS metric_online_only,
        transactions_count_offline_only AS metric_offline_only,
        transactions_count_ropo AS metric_ropo

        ,
        transactions_count_online_only_summary AS metric_online_only_summary,
        transactions_count_offline_only_summary AS metric_offline_only_summary,
        transactions_count_ropo_summary AS metric_ropo_summary

    FROM
    (
        SELECT
            DATE,

                    transactions_count_online_only,
    transactions_count_offline_only,
    transactions_count_ropo
    ,
        transactions_count_online_only + transactions_count_offline_only + transactions_count_ropo AS transactions_count_total,
    SUM(transactions_count_online_only) OVER() AS transactions_count_online_only_summary,
    SUM(transactions_count_offline_only) OVER() AS transactions_count_offline_only_summary,
    SUM(transactions_count_ropo) OVER() AS transactions_count_ropo_summary

        FROM
        (
            SELECT
                DATE(TIME) AS DATE,

                                SUM(IF(l_transaction_id IS NOT NULL AND transaction_id IS NOT NULL, 1, 0)) AS transactions_count_online_only,
            SUM(IF(l_transaction_id IS NULL AND transaction_id IS NOT NULL AND prevSessionTime IS NULL, 1, 0)) AS transactions_count_offline_only,
            SUM(IF(l_transaction_id IS NULL AND transaction_id IS NOT NULL AND prevSessionTime IS NOT NULL, 1, 0)) AS transactions_count_ropo

            FROM
            (
                SELECT
                    FIRST_VALUE(sessionTime IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY sessionTimeInSeconds RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS prevSessionTime,
                    TIME,
                    transaction_id,
                    transaction_created,
                    transaction_revenue,
                    l_transaction_id,
                    user_id
                FROM
                (
                    SELECT
                        l.sessionId AS sessionId,
                        UNIX_SECONDS(COALESCE(l.sessionTime, r.transaction_created)) AS sessionTimeInSeconds,
                        COALESCE(l.sessionTime, r.transaction_created) AS TIME,
                        l.sessionTime AS sessionTime,
                        r.transaction_created AS transaction_created,
                        l.transaction_id AS l_transaction_id,
                        r.transaction_id AS transaction_id,
                        COALESCE(l.transaction_revenue , r.transaction_revenue) AS transaction_revenue,
                        COALESCE(r.user_id, l.user_id, l.prev_user_id, l.next_user_id, l.client_id) AS user_id
                    FROM
                    (
                        SELECT
    *,
    FIRST_VALUE(user_id IGNORE NULLS) OVER(
        PARTITION BY client_id
        ORDER BY sessionTime DESC
        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS prev_user_id,
    FIRST_VALUE(user_id IGNORE NULLS) OVER(
        PARTITION BY client_id
        ORDER BY sessionTime DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS next_user_id
                        FROM (
                            SELECT
                                hits.TRANSACTION.transactionId AS transaction_id,
                                MAX(IF(USER.id IS NULL OR REGEXP_CONTAINS(USER.id, '^$|not set|undefined|^0$|(not set)|false|none'), NULL, USER.id)) AS user_id,
                                MAX(clientId) AS client_id,
                                sessionId AS sessionId,
                                SUM(IF(hits.eCommerceAction.action_type='refund' AND hits.TRANSACTION.transactionRevenue > 0, -hits.TRANSACTION.transactionRevenue, hits.TRANSACTION.transactionRevenue)) AS transaction_revenue,
                                TIMESTAMP_SECONDS(MIN(hits.TIME)) AS sessionTime
                            FROM
                                `owox-140513.OWOXBI_Streaming.session_streaming_*`, unnest(hits) AS hits
                            WHERE
                                _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP('2019-05-01')), INTERVAL -2 WEEK))) AND FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP('2019-05-31'), DAY), INTERVAL 1 DAY), INTERVAL -1 SECOND))
                            GROUP BY
                              sessionId,
                              transaction_id
                        )
                    ) AS l
                    FULL OUTER JOIN
                    (
                        SELECT
                            transaction_id,
                            SUM(product_price * product_quantity) AS transaction_revenue,
                            user_id AS user_id,
                            MIN(TIME) AS transaction_created
                        FROM
                            `owox-140513.1c.view_snapshot_completed_orders_view`
                        WHERE
                            TIME BETWEEN TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP('2019-05-01')), INTERVAL -2 WEEK)) AND TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP('2019-05-31'), DAY), INTERVAL 1 DAY), INTERVAL -1 SECOND)
                        GROUP BY
                          transaction_id,
                          user_id
                    ) AS r
                    ON l.transaction_id = r.transaction_id
                )
            )
            WHERE TIME BETWEEN TIMESTAMP('2019-05-01') AND TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP('2019-05-31'), DAY), INTERVAL 1 DAY), INTERVAL -1 SECOND)
            GROUP BY
                DATE
        )
        WHERE DATE IS NOT NULL
    )
)

        ORDER BY DATE DESC
        LIMIT 2500