Изм-е ROPO-доли дохода по дням за посл- 30 дн- с конв-окном

#standardSQL
--Изменение ROPO доли дохода по дням за последние 30 дней с конверсионным окном 30 дней
SELECT
            metric_online_only, --доля дохода ONLINE
            metric_offline_only, --доля дохода OFFLINE
            metric_ropo, --доля дохода ROPO
    DATE

FROM
(
    SELECT
        DATE,


        SAFE_DIVIDE(revenue_online_only, revenue_total) AS metric_online_only,
        SAFE_DIVIDE(revenue_offline_only, revenue_total) AS metric_offline_only,
        SAFE_DIVIDE(revenue_ropo, revenue_total) AS metric_ropo


    FROM
    (
        SELECT
            DATE,

                    revenue_online_only,
    revenue_offline_only,
    revenue_ropo
    ,
        revenue_online_only + revenue_offline_only + revenue_ropo AS revenue_total,
    SUM(revenue_online_only) OVER() AS revenue_online_only_summary,
    SUM(revenue_offline_only) OVER() AS revenue_offline_only_summary,
    SUM(revenue_ropo) OVER() AS revenue_ropo_summary

        FROM
        (
            SELECT
                DATE(TIME) AS DATE,

                                SUM(IF(l_transaction_id IS NOT NULL AND transaction_id IS NOT NULL, transaction_revenue, 0)) AS revenue_online_only,
            SUM(IF(l_transaction_id IS NULL AND transaction_id IS NOT NULL AND prevSessionTime IS NULL, transaction_revenue, 0)) AS revenue_offline_only,
            SUM(IF(l_transaction_id IS NULL AND transaction_id IS NOT NULL AND prevSessionTime IS NOT NULL, transaction_revenue, 0)) AS revenue_ropo

            FROM
            (
                SELECT
                    FIRST_VALUE(sessionTime IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY sessionTimeInSeconds RANGE BETWEEN 2592000 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(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY))), INTERVAL -30 DAY))) AND FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -1 DAY)), 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(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY))), INTERVAL -30 DAY)) AND TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -1 DAY)), 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(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY)) AND TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -1 DAY)), DAY), INTERVAL 1 DAY), INTERVAL -1 SECOND)
            GROUP BY
                DATE
        )
        WHERE DATE IS NOT NULL
    )
)

        ORDER BY DATE DESC
        LIMIT 2500