57 lines
1.9 KiB
SQL
57 lines
1.9 KiB
SQL
-- 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 current_timestamp - '7 days'::interval and current_timestamp
|
|
group by
|
|
group_and_sims.group,
|
|
group_and_sims.sim_type
|