title: Calculate retention for daily users with PostgreSQL
body: <p>I am trying to calculate the daily retention of users login sessions using pgadminIII / postgresql.</p>
  
  <p><code>table1</code> has <code>user_id</code>, <code>first_login_date</code>, <code>last_login_date</code></p>
  
  <pre><code>CREATE TABLE table1 (user_id numeric, first_login_date date, last_login_date date);
  
    INSERT INTO table1 (user_id, first_login_date, last_login_date) VALUES (12346, '2010-06-01', '2010-06-02'),
  (67890, '2010-03-01', '2010-03-10'),
  (67890, '2010-03-01', '2010-03-10'),
  (90123, '2010-08-01', '2010-08-15'),
  (45678, '2010-08-01', '2010-08-20'),
  (76543, '2010-07-01', '2010-07-01');
  </code></pre>
  
  <p><code>table2</code> has <code>user_id</code>, <code>session_id</code>, <code>login_date</code></p>
  
  <pre><code>CREATE TABLE table2 (user_id numeric, session_id numeric, login_date date);
  
  INSERT INTO table2 (user_id, session_id, login_date) VALUES
  (12346, '8764', '2010-06-02'),
  (67890, '4657', '2010-03-05'),
  (90123, '3945', '2010-08-09'),
  (45678, '20845', '2010-08-02'),
  (67890, '29384', '2010-03-07'),
  (90123, '3424', '2010-08-12'),
  (45678, '349284', '2010-08-10');
  </code></pre>
  
  <p>There are some duplicates in <code>table1</code>. 
  Because of this, I am unsure if my query to calculate the users that have 2-day retention and 5-day retention is correct.</p>
  
  <p>The query that I am using for the 2-day is: </p>
  
  <pre><code>SELECT table1.user_id, first_login_date, table2.login_date,
  (table2.login_date - table1.first_login_date) as datediff, FROM table1
  JOIN table2 ON table2.user_id = table2.user_id WHERE
  (table2.login_date - table1.first_login_date) = 1;
  </code></pre>
  
  <p>which gives the result of 7 users with 2-day retention</p>
  
  <p>However, if I add the distinct clause, such as:</p>
  
  <pre><code>SELECT distinct table1.user_id, first_login_date, table2.login_date,
      (table2.login_date - table1.first_login_date) as datediff FROM table1
      JOIN table2 ON table2.user_id = table2.user_id WHERE
      (table2.login_date - table1.first_login_date) = 1;
  </code></pre>
  
  <p>I get the result of 3 users with 2-day retention.</p>
  
  <p>I have consulted <a href="https://stackoverflow.com/questions/16492842/how-to-calculate-retention-month-over-month-using-sql">HERE</a>, <a href="http://blog.forcerank.it/sql-for-calculating-churn-retention-reengagement" rel="nofollow noreferrer">HERE</a> and <a href="https://www.periscopedata.com/blog/use-self-joins-to-calculate-your-retention-churn-and-reactivation-metrics.html" rel="nofollow noreferrer">HERE</a> regarding the calculation of daily retention, and I am unsure if my technique is giving me the correct result. For instance, to calculate the DAU, would a <code>self-join</code> be more appropriate.</p>
  
  <p>Given the data in <code>table1</code> and <code>table2</code> are my 2-day retention results accurate using my defined query?
  Is there an optimized way to calculate this retention?</p>