title: 7 Day Return/Retention Rate
body: <p>I've been trying to calculate 7 Day Return Rate (also known as Classic Retention Rate, as described here: <a href="https://www.braze.com/blog/calculate-retention-rate/" rel="nofollow noreferrer">https://www.braze.com/blog/calculate-retention-rate/</a>) and then taking a 30 day average to reduce noise in Postgresql. </p>
  
  <p>However, I'm sure I'm doing something wrong. First of all, the numbers look waaay higher than intuitively I feel they should be (generally around 5% for the rest of the sector). Also, I believe the first 7 days should show 0, as theoretically users should take at least 7 days to count as a "return". However, I get around 40-70%, as shown below.</p>
  
  <p>Would someone mind taking a look at the code below and seeing if there are any errors? 7 Day Return Rate is a really common metric for apps, and I haven't found any questions using postgresql that calculate it to this level of sophistication on Stack Exchange (or even the rest of the web), so I feel like a solid response could be very useful to a lot of people.</p>
  
  <p>Sample data</p>
  
  <pre class="lang-none prettyprint-override"><code>Wednesday, August 1, 2018 12:00 AM    71.14
  Thursday, August 2, 2018 12:00 AM     55.44
  Friday, August 3, 2018 12:00 AM       50.09
  Saturday, August 4, 2018 12:00 AM     45.81
  Sunday, August 5, 2018 12:00 AM       43.27
  Monday, August 6, 2018 12:00 AM       40.61
  Tuesday, August 7, 2018 12:00 AM      39.38
  Wednesday, August 8, 2018 12:00 AM    38.46
  Thursday, August 9, 2018 12:00 AM     36.81
  Friday, August 10, 2018 12:00 AM      35.94
  </code></pre>
  
  <pre><code>with
  
  user_first_event as (
      select distinct id, min(timestamp)::date as first_event_date
      from log
      where 
          timestamp &lt;= current_date
          and timestamp &gt;= {{start_date}} and timestamp &lt;= {{end_date}}
      group by id),
  
  event as (
      select distinct id, timestamp::date as user_event_date
      from log
      where timestamp &lt;= current_date and timestamp &gt;= {{start_date}}),
  
  gap as (
      select 
          user_first_event.id, 
          user_first_event.first_event_date,
          event.user_event_date,
          event.user_event_date - user_first_event.first_event_date as days_since_signup
      from user_first_event
      join event on user_first_event.id = event.id
      where user_first_event.first_event_date &lt;= event.user_event_date),
  
  conversion_rate as (
  select
      first_event_date,
      (sum(case when days_since_signup = 7 then 1 else 0 end) * 100.0 /
          count(distinct id)
        ) as seven_day_retention_rate
  from gap
  group by first_event_date
  )
  
  SELECT first_event_date,  
         AVG(seven_day_retention_rate)
              OVER(ORDER BY first_event_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_avg_retention_rate
  FROM conversion_rate
  </code></pre>