Доля дох по к-ву дн м/перв онл сесс и ROPO покупк с конв-окн

#standardSQL
--Доля дохода по количеству дней между первой онлайн сессией и ROPO покупкой с конверсионным окном 30 дней за последние 7 дней
SELECT
    days_diff,
        transaction_revenueShare --доля дохода

FROM
(
    SELECT
        days_diff,
        transactions_count AS transaction_numberOfTransactions,
        transactions_count_summary AS transaction_numberOfTransactions_summary,
        transactions_count / transactions_count_summary AS transaction_transactionsShare,
        transaction_revenue AS transaction_totalRevenue,
        transaction_revenue_summary AS transaction_totalRevenue_summary,
        transaction_revenue / transaction_revenue_summary AS transaction_revenueShare,
        users_count AS transaction_customersCount,
        users_count_summary AS transaction_customersCount_summary,
        users_count / users_count_summary AS transaction_customerShare,
        aov AS transaction_aov,
        aov_summary AS transaction_aov_summary
    FROM
    (
        SELECT
            days_diff,
            transactions_count,
            transaction_revenue,
            users_count,
            aov,
            SUM(transactions_count) OVER() AS transactions_count_summary,
            SUM(transaction_revenue) OVER() AS transaction_revenue_summary,
            SUM(users_count) OVER() AS users_count_summary,
            AVG(aov) OVER() AS aov_summary
        FROM
        (
            SELECT
                TIMESTAMP_DIFF(transaction_created, prevSessionTime, DAY) AS days_diff,
                COUNT(DISTINCT transaction_id) AS transactions_count,
                SUM(transaction_revenue) AS transaction_revenue,
                AVG(transaction_revenue) AS aov,
                COUNT(DISTINCT user_id) AS users_count
            FROM
            (
                SELECT
                        FIRST_VALUE(sessionTime IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY sessionTimeInSeconds RANGE BETWEEN 2592000 PRECEDING AND CURRENT ROW) AS prevSessionTime,
                    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,
                        l.sessionTime AS sessionTime,
                        r.transaction_created AS transaction_created,
                        l.transaction_id AS l_transaction_id,
                        r.transaction_id AS transaction_id,
                        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,
                                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 -7 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 -7 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 l_transaction_id IS NULL
                AND transaction_id IS NOT NULL
                AND prevSessionTime IS NOT NULL
                AND transaction_created BETWEEN TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -7 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
                days_diff
        )
    )
)

        ORDER BY days_diff ASC
        LIMIT 2500