-- NOTE(simkir): Materialized view called by the crosstab to pivot sim types to cols
create view weekly_sim_submit_count as
  with
  -- NOTE(simkir): Select all groups and sim kinds
  group_and_sims as (
    select
      "group",
      sim_type
    from
      (
        select substring(distinct_id similar '%#"@%#"' escape '#') as group
        from session
        where distinct_id is not null and distinct_id like '%@%'
        group by substring(distinct_id similar '%#"@%#"' escape '#')
      )
      cross join
      (
        select distinct string_value as sim_type
        from event_data
        where event_data.data_key = 'kind'
      )
  ),
  events as (
    select
      substring(session.distinct_id similar '%#"@%#"' escape '#') as group,
      event_data.string_value as sim_type,
      event_data.website_event_id as event_id,
      event_data.created_at
    from
      website_event
      join session on session.session_id = website_event.session_id
      left outer join event_data on event_data.website_event_id = website_event.event_id
    where
      website_event.website_id = '16e7d807-4db5-45fd-92a9-27393445a153'
      and website_event.event_type = 2
      and website_event.event_name = 'mapster-submit-drifters'
      and event_data.data_key = 'kind'
  )

  select
    group_and_sims.group,
    group_and_sims.sim_type,
    count(events.event_id)
  from
    group_and_sims
    -- NOTE(simkir): Allow missing events
    left outer join events on
      -- NOTE(simkir): Join on the unique combinations of group name and sim
      -- kind which we've already fetched in `group_and_sims`.
      events.group = group_and_sims.group
      and events.sim_type = group_and_sims.sim_type
      -- NOTE(simkir): Filter here in the join to, again, allow missing rows
      and events.created_at between '2025-09-01' and current_timestamp
  group by
    group_and_sims.group,
    group_and_sims.sim_type