A complete query of <Build Funnels with Google BigQuery> presentation.

building-funnels-with-google-bigquery.sql

#standardSQL

WITH events AS (
  SELECT "A" AS user, 100 AS time, "view" AS event
  UNION ALL SELECT "B", 200, "click"
  UNION ALL SELECT "B", 300, "view"
  UNION ALL SELECT "A", 400, "click"
  UNION ALL SELECT "A", 500, "click"
  UNION ALL SELECT "A", 600, "buy"
),

query_1_result AS (
  SELECT
    user,
    time,
    (CASE event WHEN "view" THEN time END) AS view,
    (CASE event WHEN "click" THEN time END) AS click,
    (CASE event WHEN "buy" THEN time END) AS buy
  FROM
    events
),

query_2_result AS (
  SELECT
    LAST_VALUE(view IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS view,
    LAST_VALUE(click IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS click,
    LAST_VALUE(buy IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS buy
  FROM
    query_1_result
),

query_3_result AS (
  SELECT
    view,
    (CASE WHEN view < click THEN click END) AS click,
    (CASE WHEN click < buy THEN buy END) AS buy
  FROM query_2_result
),

query_4_result AS (
  SELECT
    (CASE WHEN view IS NOT NULL THEN view END) AS view,
    (CASE WHEN click IS NOT NULL THEN view END) AS click,
    (CASE WHEN buy IS NOT NULL THEN view END) AS buy
  FROM query_3_result
)

SELECT "view" AS step, COUNT(DISTINCT view) AS count FROM query_4_result
UNION ALL SELECT "click", COUNT(DISTINCT click) FROM query_4_result
UNION ALL SELECT "buy", COUNT(DISTINCT buy) FROM query_4_result
;