Pfthb

I have a table on BigQuery with the following columns:

user_id visit_date referral transaction
1234 20180101 site2 0
1234 20180102 site3 1
1234 20180103 site2 1
4567 20180104 site4 0
4567 20180105 site5 0
5678 20180101 site2 0
5768 20180102 site3 1

My goal is to have a table in the following format as output:

path transactions
site2 > site3 2 
site2 1
site4 > site5 0

What I don't understand is how to 'reset' the path for a user that have multiple conversions in the same period, as is the case for user_id = 1234.

So far I manage use the following query, but it is not the desired output.

SELECT
 referral_path,
 SUM(transactions) AS transactions
FROM (
 SELECT
 user_id,
 STRING_AGG(DISTINCT(referral), ',') AS referral_path,
 MAX(transactions) AS transactions
 FROM (
 SELECT
 user_id,
 referral,
 transactions
 FROM
 table
 ORDER BY
 user_id )a
 GROUP BY
 user_id )b
 GROUP BY
 referral_path
 ORDER BY
 transactions DESC

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
 path, 
 SUM(transaction) transactions
FROM (
 SELECT
 STRING_AGG(referral, ' > ') path,
 SUM(transaction) transaction
 FROM (
 SELECT 
 user_id, visit_date, referral, transaction, 
 IFNULL(SUM(transaction) OVER(PARTITION BY user_id ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) grp 
 FROM `project.dataset.table`
 )
 GROUP BY user_id, grp
)
GROUP BY path

You can test, play with above using dummy data from your question as in below

#standardSQL
WITH `project.dataset.table` AS (
 SELECT 1234 user_id, '20180101' visit_date, 'site2' referral, 0 transaction UNION ALL
 SELECT 1234, '20180102', 'site3', 1 UNION ALL
 SELECT 1234, '20180103', 'site2', 1 UNION ALL
 SELECT 4567, '20180104', 'site4', 0 UNION ALL
 SELECT 4567, '20180105', 'site5', 0 UNION ALL
 SELECT 5678, '20180101', 'site2', 0 UNION ALL
 SELECT 5678, '20180102', 'site3', 1 
)
SELECT 
 path, 
 SUM(transaction) transactions
FROM (
 SELECT
 STRING_AGG(referral, ' > ') path,
 SUM(transaction) transaction
 FROM (
 SELECT 
 user_id, visit_date, referral, transaction, 
 IFNULL(SUM(transaction) OVER(PARTITION BY user_id ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) grp 
 FROM `project.dataset.table`
 )
 GROUP BY user_id, grp
)
GROUP BY path
ORDER BY transactions DESC 

with result

Row path transactions 
1 site2 > site3 2 
2 site2 1 
3 site4 > site5 0