I have some issues with this query, I tried to move from memsql to SF:
WITH
-- events for each active account
evs as (
select distinct e.pulse_user_id
,e.created_at
from swap s
join platform_events_light e
ON s.pulse_user_id = e.pulse_user_id
and s._week = date_trunc('week', e.created_at)
where e.pulse_account_id is not null
and e.pulse_user_id is not null
and ( e.created_at between '{{start_date}}' and current_timestamp )
and (((e.name in ('create_board', 'change_column_value', 'change_pulse_order',
'create_column', 'create_group', 'create_note_post', 'create_pulse', 'duplicate_board',
'duplicate_column', 'duplicate_group', 'duplicate_pulse', 'edit_board', 'edit_column',
'edit_labels', 'edit_pulse', 'open_pulse', 'subscribe', 'board_filter', 'archive_pulse',
'archive_group', 'archive_board', 'unsubscribe', 'resize_column', 'open_comment',
'move_group_to_board', 'move_pulse_to_board', 'move_pulse_to_group', 'subscribe_other_user',
'toggle_collapse_group', 'board_load')) or (e.name in ('posted', 'like', 'mention', 'attach_file', 'pin', 'edit_post',
'watched', 'download_all_attachments')) or (e.name in ('new_session', 'app_session_start', 'delete_group',
'sort_board_by_column', 'timeline_open', 'notification_mark_as_read',
'unselect_pulses'))) or (e.name in ('app_session_start', 'mobile_page_view')) or (e.name in (
'inbox_view', 'board_load')))
and e.pulse_account_id != 5
),
-- distinct days each account was active
agg as (
SELECT pulse_user_id,
date_trunc('week', created_at) as _week,
count(distinct date_trunc('day', created_at)) as L1L7
FROM evs
GROUP BY 1, 2
),
-- find percentiles for activity levels
s as (
SELECT _week, pulse_user_id, L1L7,
percentile_cont(0.1) OVER w AS pct10,
percentile_cont(0.25) OVER w AS pct25,
percentile_cont(0.5) OVER w AS median,
percentile_cont(0.75) OVER w AS pct75,
percentile_cont(0.9) OVER w AS pct90
FROM agg
-- where _week > date_trunc('week', '{{start_date}}' )
-- and _week < date_trunc('week', current_timestamp )
WINDOW w AS (partition by _week ORDER BY L1L7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
SELECT _week
,count(distinct pulse_user_id) as cnt_users
,AVG(L1L7) as mean
,ANY_VALUE(pct10) AS pct10
,ANY_VALUE(pct25) AS pct25
,ANY_VALUE(median) AS median
,ANY_VALUE(pct75) AS pct75
,ANY_VALUE(pct90) AS pct90
FROM s
where _week < date_trunc('week', current_timestamp )
and _week > date_trunc('week', '{{ start_date }}')
group by _week
ORDER BY _week desc