title: Calculate 7 Day Retention with SQL
body: <p>Given the following tables, </p>
  
  <pre><code>users                                             page_views
  +-----------------+-----------+                  +----------+-----------+
  | user_id                 |varchar| &lt;----+       | pv_id     | varchar   |
  | reg_ts                  |timestamp|            | pv_ts     | timestamp |
  | reg_device              |varchar|       +----&gt; | user_id   | varchar   |
  | mktg_channel            |varchar|              | url       | varchar   |
  +-----------------+-----------+                  | device    | varchar   |
                                                   +----------+-----------+
  </code></pre>
  
  <ul>
  <li>Table "users" has one row per registered user.</li>
  <li>Table "page_views" has one row per page view event.</li>
  </ul>
  
  <p><strong>What % of users who first visit on a given day came back again 1 week later?</strong></p>
  
  <p>I'm currently using SQLlite and created a sample database but my output is off...</p>
  
  <p>Below is what I have so far:</p>
  
  <pre><code>-- day 1 active users
  SELECT *
  FROM page_views
  LEFT JOIN page_views AS future_page_views 
  ON page_views.user_id = future_page_views.user_id
  AND page_views.pv_ts = future_page_views.pv_ts - datetime(future_page_views.pv_ts, '+7 day')
  
  -- day 7 retained users
  SELECT 
    future_page_views.pv_ts,
    COUNT(DISTINCT page_views.user_id) as active_users,
    COUNT(DISTINCT future_page_views.user_id) as retained_users,
    CAST(COUNT(DISTINCT future_page_views.user_id) / COUNT(DISTINCT page_views.user_id) AS float) retention
  FROM page_views
  LEFT JOIN page_views as future_page_views 
  ON page_views.user_id = future_page_views.user_id
  AND page_views.pv_ts = future_page_views.pv_ts - datetime(page_views.pv_ts, '+7 day')
  GROUP BY 1
  </code></pre>
  
  <p>Not sure if I should use Strftime function (DATEDIFF) in this instance to capture the 7 day. Open to any suggestions and feedback, thanks in advance.</p>
  
  <p>EDIT**
  Sample data below, based on the below data set, </p>
  
  <p>I expect only user_id (8) to show up as 7 day retained (first day 2020-01-02) (last day 2020-01-09)
  <img src="https://i.stack.imgur.com/CQbQw.jpg" alt="![Users">
  <a href="https://i.stack.imgur.com/g7KB5.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g7KB5.jpg" alt="Page_Views"></a></p>
  
  <p>Desired Output:</p>
  
  <ul>
  <li>User_ID</li>
  <li>p.pv_ts as First_Day</li>
  <li>f.pv_ts as Last_Day</li>
  <li>Retention Days (i.e 1,2,3,4,5 days...) </li>
  <li>% of users who visited and came back on day 7</li>
  </ul>