Query: Day 0 retention rate per day

Definitions:

• D0 - Day of first_open event
• Retention - No remove_app event for the user

SELECT 
  event_date, 
  SUM(user_first_open) as first_open_events_count, 
  SUM(user_uninstall) as uninstall_events_count,
  SUM(user_first_open - user_uninstall) as first_open_without_remove,
  SUM(user_first_open - user_uninstall)/SUM(user_first_open) retention_rate_on_first_day
FROM 
  (
  SELECT 
    user_pseudo_id, event_date,
    MAX(CASE WHEN event_name = 'app_remove' THEN 1 ELSE 0 END) as user_uninstall,
    MAX(CASE WHEN event_name = 'first_open' THEN 1 ELSE 0 END) as user_first_open
  FROM 
  (
      SELECT user_pseudo_id, event_name, event_date
      FROM `analytics_xxx.events_*`,
        UNNEST(event_params) as event_dim_params   
      WHERE 
          _TABLE_SUFFIX BETWEEN '20250301' AND '20250324' 
  )
  GROUP BY user_pseudo_id, event_date
  )
WHERE
  user_first_open = 1
GROUP BY event_date
ORDER BY event_date