title: Sql query to map user log table to user first, last and previous activity
body: <p>I have a user log table, partitioned on action_date (table name - user_action_log) consisting billions of rows, with columns</p>
  
  <blockquote>
    <p>user_id, action_name, action_date </p>
  </blockquote>
  
  <p>Sample data -</p>
  
  <pre><code>+---------+-------------+-------------+
  | user_id | action_name | action_date |
  +---------+-------------+-------------+
  | 123     | login       | 2018-01-30  |
  | 123     | logout      | 2018-01-31  |
  | 123     | click       | 2018-02-28  |
  | 123     | comment     | 2018-02-15  |
  | 123     | post        | 2018-03-15  |
  +---------+-------------+-------------+
  </code></pre>
  
  <p>I want to write an ETL/sql to transform this data into something like this (table name - user_action_record). </p>
  
  <blockquote>
    <p>user_id (Primary key), first_action_date, last_action_date, previous_action_date</p>
  </blockquote>
  
  <p>Sample output data -</p>
  
  <pre><code>+---------+-------------------+------------------+---------------------------+
  | user_id | first_action_date | last_action_date | previous_last_action_date |
  +---------+-------------------+------------------+---------------------------+
  | 123     | 2018-01-30        | 2018-03-15       | 2018-02-28                |
  +---------+-------------------+------------------+---------------------------+
  </code></pre>
  
  <p>I tried dividing problem into 2 steps - </p>
  
  <ol>
  <li>Insert new users which are not present in user_action_record. </li>
  <li>Update existing user by updating "previous_last_action_date" from value in "last_action_date" and update last_action_date based on user_action_log table.  </li>
  </ol>
  
  <p>The problem is that, since user_action_log is partitioned on action_date, i can query that table on a daily basis (action_date = CURRENT_DATE)</p>
  
  <p>Can anybody help me with sqls to populate my target table in this case?</p>
  
  <p>-- Edited with additional info below </p>
  
  <ol>
  <li>Source and expected target table on day '2018-01-30'</li>
  </ol>
  
  <pre><code>+---------+-------------+-------------+
  | user_id | action_name | action_date |
  +---------+-------------+-------------+
  | 123     | login       | 2018-01-30  |
  | 123     | logout      | 2018-01-30  |
  | 123     | click       | 2018-01-30  |
  +---------+-------------+-------------+
  
  </code></pre>
  
  <pre><code>+---------+-------------------+------------------+---------------------------+
  | user_id | first_action_date | last_action_date | previous_last_action_date |
  +---------+-------------------+------------------+---------------------------+
  | 123     | 2018-01-30        | 2018-01-30       | 2018-01-30                |
  +---------+-------------------+------------------+---------------------------+
  </code></pre>
  
  <ol start="2">
  <li>Source and expected target table on day '2018-01-31'</li>
  </ol>
  
  <pre><code>+---------+-------------+-------------+
  | user_id | action_name | action_date |
  +---------+-------------+-------------+
  | 123     | login       | 2018-01-30  |
  | 123     | logout      | 2018-01-30  |
  | 123     | click       | 2018-01-30  |
  | 123     | login       | 2018-01-31  |
  | 123     | logout      | 2018-01-31  |
  +---------+-------------+-------------+
  
  </code></pre>
  
  <pre><code>+---------+-------------------+------------------+---------------------------+
  | user_id | first_action_date | last_action_date | previous_last_action_date |
  +---------+-------------------+------------------+---------------------------+
  | 123     | 2018-01-30        | 2018-01-31       | 2018-01-30                |
  +---------+-------------------+------------------+---------------------------+
  </code></pre>
  
  <ol start="3">
  <li>Source and expected target table on day '2018-02-15'</li>
  </ol>
  
  <pre><code>+---------+-------------+-------------+
  | user_id | action_name | action_date |
  +---------+-------------+-------------+
  | 123     | login       | 2018-01-30  |
  | 123     | logout      | 2018-01-30  |
  | 123     | click       | 2018-01-30  |
  | 123     | login       | 2018-01-31  |
  | 123     | logout      | 2018-01-31  |
  | 123     | logout      | 2018-02-15  |
  | 123     | logout      | 2018-02-15  |
  +---------+-------------+-------------+
  
  </code></pre>
  
  <pre><code>+---------+-------------------+------------------+---------------------------+
  | user_id | first_action_date | last_action_date | previous_last_action_date |
  +---------+-------------------+------------------+---------------------------+
  | 123     | 2018-01-30        | 2018-02-15       | 2018-01-31                |
  +---------+-------------------+------------------+---------------------------+
  </code></pre>