title: Calculate Day 7 retention rate of all players acquired on specific data
body: <p>I'm  new to SQL and I'm struggling with few of the questions on my exercise. How to calculate Day 7 retention rate of all players acquired on 01 October 2019. The tables shown is just a sample of what the extended table is.</p>
  <p>My answer was:</p>
  <pre><code>SELECT 
    Session_table,
    COUNT(DISTINCT user_id) as active_users,
    COUNT(DISTINCT future_ user_id) as retained_users,
    CAST(COUNT(DISTINCT future_ user_id) / COUNT(DISTINCT user_id) AS float) retention
  FROM inapp_purchase
  LEFT JOIN user_id as future_id 
  ON user_id = future_ user_id
  AND user_id. inapp_purchase = user_id.session_table - datetime(page_views.pv_ts, '+7 day')
  GROUP BY 1
  </code></pre>
  <p>There are two tables:</p>
  <p><a href="https://i.stack.imgur.com/Ore9N.png" rel="nofollow noreferrer">session_table</a></p>
  <p>When each <em>user_id</em> starts a session , the session will have a unique <em>session_id</em>. <em>Session_length_seconds</em> tells how long a session lasted in seconds. <em>level</em> tells what game level the player was at the end of that session. The session_table will have one line for each session each user has (users can have multiple sessions per day each with unique <em>session_id</em>).</p>
  <p><a href="https://i.stack.imgur.com/DJWB8.png" rel="nofollow noreferrer">inapp_purchase</a></p>
  <p>The <strong>inapp_purchase</strong> table has one line for each product that the user (denoted by <em>user_id</em>) purchased. There can be multiple purchases in a day per user, per session. The <em>session_id</em> and <em>user_id</em> here can link to the <strong>session_table</strong> to track active users who also make a payment. <em>Product_id</em> tells which product was purchased and the <em>purchase_value</em> tells the amount the user paid in $.</p>
  <p>There are also three requests for the <strong>inapp_purchase</strong> table:</p>
  <ul>
  <li>Calculate daily average revenue per daily active user for last 3 months</li>
  <li>Calculate the daily average revenue per <strong>paying</strong> user year to date?</li>
  <li>Calculate the daily conversion rate over the last month? This measure is defined by the proportion of daily active users who make a purchase on the day in question.</li>
  </ul>
  <p>Please let me know if the above information is sufficient.</p>
  <p>Thank you for your help.</p>