Послед-ть шагов воронки по зак-м FBC за посл 30д

#standardSQL
--Последовательность шагов воронки по заказам в модели атрибуции [New] Funnel Based with Completed Orders за последние 30 дней
CREATE TEMP FUNCTION AggArrayToGroupString(arr ARRAY<STRING>)
    RETURNS STRING AS ( (
    SELECT STRING_AGG(s_a, ' > ') FROM (
    SELECT
    IF (COUNT(n) = 1, ANY_VALUE(n), CONCAT(ANY_VALUE(n),' (x',CAST(COUNT(n) AS STRING),')')) AS s_a
    FROM (
    SELECT
    COUNTIF(n != p) OVER(ORDER BY rn) AS nb, n, p, rn
    FROM (
    SELECT
    LAG(n) OVER(ORDER BY rn) AS p, n, rn
    FROM (
    SELECT
    e AS n, ROW_NUMBER() OVER() AS rn
    FROM
    UNNEST(arr) AS e)
    ORDER BY
    rn ))
    GROUP BY
    nb )));

SELECT
    attribution_chain_transaction, --количество транзакций
    chains_steps
FROM
(
    SELECT
        MIN(chain_transaction_summary) AS attribution_chain_transaction_summary,
        SUM(transaction_count) AS attribution_chain_transaction, --количество транзакций
        agg_string AS chains_steps
    FROM
    (
        SELECT
            transaction_id,
            agg_string,
            chain_transaction_summary,
            transaction_count,
            transaction_revenue,
            SUM(transaction_revenue) OVER() AS chain_revenue_summary
        FROM
        (
            SELECT
                transaction_id,
                AggArrayToGroupString(ARRAY_AGG(CONCAT(steps))) AS agg_string,
                MIN(revenue) AS transaction_revenue,
                COUNT(DISTINCT transaction_id) AS transaction_count,
                COUNT(DISTINCT transaction_id) OVER() AS chain_transaction_summary
            FROM
            (
                SELECT
                    transaction_id AS transaction_id,
                    revenue AS revenue,
                    SOURCE,
                    medium,
                    campaign,
                    CASE active_step
WHEN 1 THEN '{AttributionPage.Edit.DetailTab.Columns.Step1.Title}'
WHEN 2 THEN '{AttributionPage.Edit.DetailTab.Columns.Step3.Title}'
WHEN 3 THEN '{AttributionPage.Edit.DetailTab.Columns.Step4.Title}'
WHEN 4 THEN '{AttributionPage.Edit.DetailTab.Columns.Step5.Title}'
WHEN 5 THEN 'Purchase Offline'
ELSE '(not set)' END AS steps,
    RANK() OVER(PARTITION BY transaction_id ORDER BY DATE DESC) AS rowf
                    FROM
                        `owox-140513.Funnel_Based_with_Completed_Orders.values`
                    WHERE
        DATE(_PARTITIONTIME) BETWEEN DATE(TIMESTAMP_ADD(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY)), INTERVAL -30 DAY)) AND DATE(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 DATE(TIMESTAMP_MILLIS(transaction_time)) BETWEEN DATE(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY))) AND DATE(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))

                    ORDER BY
                        TIME
            )
            WHERE
                rowf = 1
            GROUP BY
                transaction_id
        )
    )
        GROUP BY
            agg_string
)
        ORDER BY attribution_chain_transaction DESC
        LIMIT 2500