Having a little trouble with a query provided by Periscope. Can you help point me in the right direction?
Error is - ERROR: aggregate functions are not allowed in GROUP BY Position: 305
with monthly_activity as (
select distinct
date_trunc('month', created_at) as month,
user_id
from oauth_refresh_tokens
),
first_activity as (
select user_id, date(min(created_at)) as month
from oauth_refresh_tokens
group by 2
)
select
this_month.month,
count(distinct user_id)
from monthly_activity this_month
left join monthly_activity last_month
on this_month.user_id = last_month.user_id
and this_month.month = last_month.month + interval '1 month'
join first_activity
on this_month.user_id = first_activity.user_id
and first_activity.month != this_month.month
where last_month.user_id is null
group by 1