Доля транз по камп в ROPO-транз за последние 30д с конв-окно

#standardSQL
--Доля транзакций по кампаниям в ROPO-транзакциях за последние 30 дней с конверсионными окном 2 недели в сравнении между моделью атрибуции [New] Funnel Based with Completed Orders и онлайн-транзакциями
SELECT
            metric_online_only, --доля транзакций ONLINE
            metric_ropo, --доля транзакций ROPO

        trafficSource_source, --источник трафика
        trafficSource_medium, --канал
        trafficSource_campaign --campaign


FROM
(

    SELECT
            SOURCE AS trafficSource_source,
            medium AS trafficSource_medium,
            campaign AS trafficSource_campaign,

        SAFE_DIVIDE(transactions_count_online_only, transactions_count_online_only_summary) AS metric_online_only,
        SAFE_DIVIDE(transactions_count_ropo, transactions_count_ropo_summary) AS metric_ropo


    FROM
    (
        SELECT
                SOURCE,
                medium,
                campaign,
                    transactions_count_online_only,
    transactions_count_ropo
        ,
        SUM(transactions_count_online_only) OVER() AS transactions_count_online_only_summary,
        SUM(transactions_count_ropo) OVER() AS transactions_count_ropo_summary

        FROM
        (
            SELECT
                    SOURCE,
                    medium,
                    campaign,
                                SUM(IF(has_offline = 0 AND has_online = 1, 1, 0)) AS transactions_count_online_only,
            SUM(IF(has_offline = 1 AND has_online = 1, 1, 0)) AS transactions_count_ropo

            FROM
            (
                SELECT
                        VALUES.SOURCE AS SOURCE,
                        VALUES.medium AS medium,
                        VALUES.campaign AS campaign,
                    has_offline,
                    has_online,
                    attributed_revenue
                FROM
                (
                    SELECT
                        session_id,
                        transaction_id,
                        user_id,
                            MIN(SOURCE) AS SOURCE,
                            MIN(medium) AS medium,
                            MIN(campaign) AS campaign,
                        SUM(revenue * VALUE) AS attributed_revenue,
                        SUM(IF(active_step = 5, 1, 0)) AS transactions_count,
                        MAX(has_offline) AS has_offline,
                        MAX(has_online) AS has_online,
                        MIN(sessionTimeInSeconds) AS sessionTimeInSeconds
                    FROM
                    (
                        SELECT
                            session_id,
                            transaction_id,
                            VALUE,
                            user_id,
                                IFNULL(SOURCE, 'NULL') AS SOURCE,
                                IFNULL(medium, 'NULL') AS medium,
                                IFNULL(campaign, 'NULL') AS campaign,
                            revenue,
                            active_step,
                            TIME / 1000 AS sessionTimeInSeconds,
                            MAX(IF(data_source=101, 1, 0)) OVER(partition BY transaction_id) AS has_offline,
                            MAX(IF(data_source=101, 0, 1)) OVER(partition BY transaction_id) AS has_online,
    RANK() OVER(PARTITION BY transaction_id ORDER BY DATE DESC) AS rowf
                        FROM
                            `owox-140513.Funnel_Based_with_Completed_Orders.values`
                        WHERE
                            _PARTITIONTIME BETWEEN TIMESTAMP_ADD(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY)), INTERVAL -1209600 SECOND) 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)
                            AND TIMESTAMP_MILLIS(transaction_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)
                    )
                    WHERE
                        rowf = 1 AND ((has_offline = 1 AND has_online = 1) OR (has_offline = 0 AND has_online = 1))
                    GROUP BY
                        session_id,
                        transaction_id,
                        user_id
                ) AS VALUES
            )
            GROUP BY
                    SOURCE,
                    medium,
                    campaign
        )
    )
)


        ORDER BY metric_online_only DESC
        LIMIT 2500