Three Step Funnel

three-steps.sql

SELECT
  pricing.distinct_id,
  viewed_pricing_at,
  viewed_sign_up_at,
  signed_up_at
FROM (
  SELECT
    distinct_id,
    time AS viewed_pricing_at
  FROM mp.event
  WHERE
    name = "Viewed Page" AND
    current_url LIKE "https://www.helpscout.net/pricing/%"
) pricing
LEFT JOIN (
  SELECT
    distinct_id,
    time AS viewed_sign_up_at
  FROM mp.event
  WHERE
    name = "Viewed Page" AND
    current_url LIKE "https://secure.helpscout.net/members/register/%"
) sign_up_page ON sign_up_page.distinct_id = pricing.distinct_id AND sign_up_page.viewed_sign_up_at > viewed_pricing_at
LEFT JOIN (
  SELECT
    distinct_id,
    time AS signed_up_at
  FROM mp.event
  WHERE
    name = "Signed Up"
) signed_up ON signed_up.distinct_id = pricing.distinct_id AND signed_up.signed_up_at > viewed_sign_up_at

+-----------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
|                        distinct_id                        |       viewed_pricing_at        |       viewed_sign_up_at        |          signed_up_at          |
+-----------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:31:26.000000 UTC | 2018-04-09 06:08:08.000000 UTC |                                |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-05 12:48:37.000000 UTC | 2018-04-09 06:08:08.000000 UTC |                                |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:28:42.000000 UTC | 2018-04-09 06:08:08.000000 UTC |                                |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:27:49.000000 UTC | 2018-04-09 06:08:08.000000 UTC |                                |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:35:51.000000 UTC | 2018-04-09 06:08:08.000000 UTC |                                |
| 162914788c5fb-09337b4c437dc4-b34356b-144000-162914788c628 | 2018-04-04 17:30:18.000000 UTC | 2018-04-04 17:31:13.000000 UTC | 2018-04-04 17:34:51.000000 UTC |
| 162914788c5fb-09337b4c437dc4-b34356b-144000-162914788c628 | 2018-04-04 08:31:16.000000 UTC | 2018-04-04 17:24:52.000000 UTC | 2018-04-04 17:34:51.000000 UTC |
+-----------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+