multiple windowed counts with distinct user

Given an event table, I want to find the count of distinct user_ids for a specific event type in four different windows:

past 365 days
past 30 days
past 7 days
current day

I came up with this idea of a query (non-working), which probably shows best what I try to accomplish:

select
  d as "Date",
  count(distinct user_id) over (
    order by d
    rows between 365 preceding and current row
  ) "Yearly Active",
  count(distinct user_id) over (
    order by d
    rows between 30 preceding and current row
  ) "Monthly Active",
  count(distinct user_id) over (
    order by d
    rows between 7 preceding and current row
  ) "Weekly Active",
  count(distinct user_id) as "Daily Active"
from (
  select
    date(to_timestamp(event_time/1000) at time zone 'Europe/Berlin') d,
    user_id
  from event_entity
  where type = 'REFRESH_TOKEN'
  group by 1, user_id
  order by d
) daily_users

But we cannot use distinct in window functions. What is a better approach to the query?

CodePudding user response：

In my opinion, better to change the query structure and use group by and filter to calculate the count of users on each date

select
  d as "Date",
  count(distinct user_id) filter (
    where d between current_date - 365 and current_date
  ) as "Yearly Active",
  count(distinct user_id) filter (
    where d between current_date - 30 and current_date
  ) as "Monthly Active",
  count(distinct user_id) filter (
    where d between current_date - 7 and current_date
  ) as "Weekly Active",
  count(distinct user_id) filter (
    where d = current_date
  ) as "Daily Active"
from (
  select
    date(to_timestamp(event_time/1000) at time zone 'Europe/Berlin') d,
    user_id
  from event_entity
  where type = 'REFRESH_TOKEN'
  group by 1, user_id
  order by d
) daily_users
group by d