Counting Rows That Had A Previous Event With A Condition In Bigquery

I have a big query table which looks like this: event_date event_timestamp event_name user_pseudo_id 20210207 1612690020556013 purc

Solution 1:

Aggregate twice. Once to user level, where you can ascertain if that user had a trial or not, completed a purchase of note, etc. Then aggregate again to your final results.

WITH
  user_summary
AS
(
  SELECT
    geo.country as country,
    platform,
    event_date,
    pseudo_user_id,
    MAX(CASE WHEN event_name = 'session_start'            THEN 1 ELSE 0 end)   AS `has_session_start`,
    MAX(CASE WHEN event_name = 'purchase_preview_page'    THEN 1 ELSE 0 end)   AS `has_purchase_preview_page`,
    MAX(CASE WHEN event_name = 'purchase_trial_activated' THEN 1 ELSE 0 end)   AS `has_purchase_trial_activated`,
    MAX(CASE WHEN event_name = 'purchase_completed'       THEN 1 ELSE 0 end)   AS `has_purchase_completed`
  FROM
    `project.dataset.*`
  WHERE
    event_date > '20200101'
  GROUP BY
    geo.country,
    platform,
    event_date,
    pseudo_user_id
)
SELECT
  country,
  platform,
  event_date,
  SUM(has_session_start)                                      AS count_session_start,
  SUM(has_purchase_preview_page)                              AS count_purchase_preview_page,
  SUM(has_purchase_trial_activated)                           AS count_purchase_trial_activated,
  SUM(has_purchase_completed)                                 AS count_purchase_completed,
  SUM(has_purchase_trial_activated * has_purchase_completed)  AS count_trial_activated_and_purchased
FROM
  user_summary
GROUP BY
  country,
  platform,
  event_date