title: Retention of users on an Ott platform
body: <p>I have table called user_info</p>
  <p>It has two columns:</p>
  <pre><code>User_id
  Date
  </code></pre>
  <p>How do I get to the table like the one mentioned below:</p>
  <pre><code>----------------------------------------------------------------
  Date        |total_number_of_users | D2    |D5  |  D7 |   D14|
  --------------------------------------------------------------
  2020-07-01      1000                 700    500   200    150
  2020-07-02      400                  300    250   200    100
  </code></pre>
  <p>For example consider the first row in the table I am trying to achieve:</p>
  <pre><code>total_number_of_users = Total number of users who have visited the site on 2020-07- 01
  </code></pre>
  <p>D2=Out of total users who visited the site on 2020-07-01, visited on 2020-07-2</p>
  <pre><code>D7=Out of total users who visited the site on 2020-07-01, visited on 2020-07-7
  </code></pre>
  <p>I have tried the following, how do I get the exact the solution:</p>
  <pre><code>
  SELECT user_id, week(login_date) AS login_week
      FROM user_info
      GROUP BY user_id,week(login_date);
  SELECT user_id, min(week(login_date)) AS first_week
         FROM user_info
         GROUP BY user_id;
  select a.user_id,a.login_week,b.first_week as first_week  from   
                (SELECT
                  user_id,
                  week(login_date) AS login_week
                  FROM user_info
                  GROUP BY user_id,week(login_date)) a,
                (SELECT
                  user_id,
                  min(week(login_date)) AS first_week
                  FROM user_info
                  GROUP BY user_id) b
          where a.user_id=b.user_id;
  
  </code></pre>