title: LEFT OUTER JOIN with DATEDIFF in Hive QL
body: <p>I have two tables: let us call them INSTALLS and EXECUTES.</p>
  
  <p>INSTALLS table has the structure: user_id BIGINT, install_ts BIGINT.</p>
  
  <p>EXECUTES table has the same structure: user_id BIGINT, exec_ts BIGINT.</p>
  
  <p>The user_id is obvious, the _ts fields are timestamps expressed as seconds counted from Unix epoch.</p>
  
  <p>These two tables get populated like this:</p>
  
  <p>Every time a user installs my application, the INSTALLS table is populated. He could then uninstall and reinstall, in which case another row for the same user appears in this table (different ts, though). For all my analysis, I need to use the oldest installation timestamp.</p>
  
  <p>Every time a user uses my application, the EXECUTES table is populated - with the user_id and the time of execution.</p>
  
  <p>I need to create a summary table with this structure:</p>
  
  <p>date, count of installs on that date, count of usage on subsequent date</p>
  
  <p>This is how I approached the problem:</p>
  
  <ol>
  <li><p>Get oldest installation date:</p>
  
  <p>SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id</p></li>
  <li><p>Get dates of execution (need to cater for multiple executions in a given date):</p>
  
  <p>SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)</p></li>
  <li><p>Combine these two:</p>
  
  <p>SELECT a.install_date, COUNT(a.user_id) AS install_count, COUNT(b.user_id) AS usage_count
  FROM
      (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
      LEFT OUTER JOIN
      (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
      ON a.user_id = b.user_id
  GROUP BY a.install_date</p></li>
  </ol>
  
  <p>This will count all users who used my application anytime. From this dataset, I now need to extract only those users who used my application on the immediately subsequent date of their installation date.</p>
  
  <p>One approach I considered was to have an additional JOIN condition. When I do that, I get (query first, error below that):</p>
  
  <pre><code>SELECT a.install_date, COUNT(a.user_id) AS install_count, COUNT(b.user_id) AS usage_count
  FROM
      (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
      LEFT OUTER JOIN
      (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
      ON a.user_id = b.user_id AND DATEDIFF(b.exec_date, a.install_date) = 1
  GROUP BY a.install_date
  </code></pre>
  
  <blockquote>
    <p>Both left and right aliases encountered in JOIN '1'</p>
  </blockquote>
  
  <p>The second approach I considered was to have the DATEDIFF in the WHERE clause:</p>
  
  <pre><code>SELECT a.install_date, COUNT(a.user_id) AS install_count, COUNT(b.user_id) AS usage_count
  FROM
      (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
      LEFT OUTER JOIN
      (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
      ON a.user_id = b.user_id
  WHERE b.user_id IS NULL OR DATEDIFF(b.exec_date, a.install_date) = 1
  GROUP BY a.install_date
  </code></pre>
  
  <p>But I can see that this is simply wrong - if a user installs on day 1 and returns on day 3, he will not be counted in install_count (since he will not be present in the joined table). So now I am sort of out of ideas. I am a bit of a novice to SQL, so any assistance would be really appreciated.</p>