title: Daily Rolling Count of Distinct Users on Different time periods
body: <p>I am trying to find the most optimal way to run the following query which I need to connect to tableau and visualise. The idea is to count 7 day active users, 30 day active users and 90 day active users for each day. So for today I want who was active and for yesterday and I want who was active within those timeframes.</p>
  <p>To clarify users can be active multiple times within my time frames.
  A count of 7 day actives users would be the distinct number of users who had a session with in the period todays date and todays date -6. I need to calculate this for every date within the last 6 month.</p>
  <p>This is the query I have.</p>
  <pre><code>with dau as (
      select date_trunc('day', created_date) created_at,
      count(distinct customer_id) dau
      from sessions
      where created_date &gt;= date_trunc('day', dateadd('month', -6, getdate()))
      group by date_trunc('day', created_date)
  )
  select created_at,
      dau,
      (select count(distinct customer_id)
      from sessions
      where date_trunc('day', created_date) between created_at - 6 and created_at) wau,
      (select count(distinct customer_id)
      from sessions
      where date_trunc('day', created_date) between created_at - 29 and created_at) as mau,
      (select count(distinct customer_id)
      from session_s
      where date_trunc('day', created_date) between created_at - 89 and created_at) as three_mau
  from dau
  </code></pre>
  <p>It takes 30 min to run which seems crazy. Is there a better way to do it? I am also looking into the use of materialised views as a faster way to use this in a dashboard. Would this work?</p>
  <p>The result I am looking to get would be a table where the rows are dates within the last 6 months and each column is the count of distinct users on 7, 30 and 90 periods from that date.</p>
  <p><a href="https://i.stack.imgur.com/vyuyz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vyuyz.png" alt="enter image description here" /></a></p>
  <p>Thanks in advance!</p>