К-во транз- от CPA-вебмастера по вр- после смены ист-траф--п

--Количество транзакций от CPA вебмастера по времени после смены источника трафика за последние 30 дней
SELECT
        trafficSource_source,
        trafficSource_medium,
        trafficSource_campaign,
        trafficSource_adContent,
    transaction_numberOfTransactions,
    bucket1,
    bucket2,
    bucket3,
    bucket4,
    bucket5,
    bucket6,


FROM
(
    SELECT
            trafficSource_source,
            trafficSource_medium,
            trafficSource_campaign,
            trafficSource_adContent,
        transaction_numberOfTransactions,
        bucket1Value / transaction_numberOfTransactions AS bucket1,
        bucket2Value / transaction_numberOfTransactions AS bucket2,
        bucket3Value / transaction_numberOfTransactions AS bucket3,
        bucket4Value / transaction_numberOfTransactions AS bucket4,
        bucket5Value / transaction_numberOfTransactions AS bucket5,
        bucket6Value / transaction_numberOfTransactions AS bucket6
    FROM
    (
        SELECT
                trafficSource_source,
                trafficSource_medium,
                trafficSource_campaign,
                trafficSource_adContent,
            SUM(transaction_numberOfTransactions) AS transaction_numberOfTransactions,
            SUM(IF(time_diff_sec IS NULL OR NOT different, transaction_numberOfTransactions, 0)) AS bucket1Value,
            SUM(IF(different AND time_diff_sec < 60, transaction_numberOfTransactions, 0)) AS bucket2Value,
            SUM(IF(different AND time_diff_sec >= 60 AND time_diff_sec < 300, transaction_numberOfTransactions, 0)) AS bucket3Value,
            SUM(IF(different AND time_diff_sec >= 300 AND time_diff_sec < 600, transaction_numberOfTransactions, 0)) AS bucket4Value,
            SUM(IF(different AND time_diff_sec >= 600 AND time_diff_sec < 1200, transaction_numberOfTransactions, 0)) AS bucket5Value,
            SUM(IF(different AND time_diff_sec >= 1200, transaction_numberOfTransactions, 0)) AS bucket6Value
        FROM
        (
            SELECT
                sessionId,
                transaction_numberOfTransactions,
                    trafficSource_source,
                    trafficSource_medium,
                    trafficSource_campaign,
                    trafficSource_adContent,
                (IF(trafficSource_source IS NOT NULL, prev_source IS NOT NULL AND trafficSource_source != prev_source, prev_source IS NULL))
                OR (IF(trafficSource_medium IS NOT NULL, prev_medium IS NOT NULL AND trafficSource_medium != prev_medium, prev_medium IS NULL))
                OR (IF(trafficSource_adContent IS NOT NULL, prev_adContent IS NOT NULL AND trafficSource_adContent != prev_adContent, prev_adContent IS NULL))
                OR (IF(trafficSource_keyword IS NOT NULL, prev_keyword IS NOT NULL AND trafficSource_keyword != prev_keyword, prev_keyword IS NULL))
                OR (IF(trafficSource_campaign IS NOT NULL, prev_source IS NOT NULL AND trafficSource_campaign != prev_campaign, prev_campaign IS NULL)) AS different,
                visitStartTime - prev_time AS time_diff_sec
            FROM
            (
                SELECT
                    sessionId,
                    visitStartTime,
                    transaction_numberOfTransactions,
                    SOURCE AS trafficSource_source,
                    medium AS trafficSource_medium,
                    adContent AS trafficSource_adContent,
                    keyword AS trafficSource_keyword,
                    campaign AS trafficSource_campaign,
                    LAG(visitEndTime) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_time,
                    LAG(SOURCE) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_source,
                    LAG(medium) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_medium,
                    LAG(adContent) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_adContent,
                    LAG(keyword) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_keyword,
                    LAG(campaign) OVER(PARTITION BY visitorId ORDER BY visitStartTime) AS prev_campaign
                FROM
                (
                    SELECT
                        sessionId AS sessionId,
                        FIRST(trafficSource.SOURCE) AS SOURCE,
                        FIRST(trafficSource.medium) AS medium,
                        FIRST(trafficSource.adContent) AS adContent,
                        FIRST(trafficSource.keyword) AS keyword,
                        FIRST(trafficSource.campaign) AS campaign,
                        MIN(hits.TIME) AS visitStartTime,
                        MAX(hits.TIME) AS visitEndTime,
                        FIRST(IF(USER.id IS NULL OR REGEXP_MATCH(USER.id, '^$|not set|undefined|^0$|(not set)|false|none'), clientId, USER.id)) AS visitorId,
                        SUM(IF(hits.TRANSACTION.transactionId IS NOT NULL AND hits.eCommerceAction.action_type!='refund', 1, 0)) AS transaction_numberOfTransactions,
                        FIRST(DATE) AS DATE
                    FROM
                        TABLE_DATE_RANGE([owox-140513:OWOXBI_Streaming.session_streaming_], DATE_ADD(TIMESTAMP(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -30,'DAY')), -7, 'DAY'), TIMESTAMP(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second')))
                    GROUP BY
                        sessionId
                )
                WHERE TIMESTAMP(DATE) >= TIMESTAMP(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -30,'DAY'))
            )
        )
        WHERE transaction_numberOfTransactions > 0 AND      REGEXP_MATCH(LOWER(trafficSource_medium), r'(?i)cpa|partn|af|ret')

        GROUP BY
                trafficSource_source,
                trafficSource_medium,
                trafficSource_campaign,
                trafficSource_adContent
    )
)

        ORDER BY transaction_numberOfTransactions DESC
        LIMIT 2500