title: Joining with the same table twice with different conditions
body: <p>I have a table which contains a summer of user activity in a suite of products for each day. There is theoretically only one row per <code>&lt;UserId, Product, Client, Date&gt;</code> tuple, since this table is generated by a <code>GROUP BY</code>. Let's call it <code>UserActivity</code>.</p>
  
  <p><code>UserActivity:</code></p>
  
  <pre><code>UserId | Product | Client     | Date
  ------------------------------------------
  John   | Bank    | Mobile App | 2019-02-28
  John   | Bank    | Desktop App| 2019-02-28
  Sally  | Gym     | Web App    | 2019-02-28
  </code></pre>
  
  <p>I have another table, let's call it <code>FirstLastSeen</code>, where we have the <code>UserId</code>s and when they first used each <code>Product</code> and <code>Client</code>.</p>
  
  <p><code>FirstLastSeen:</code></p>
  
  <pre><code>UserId | Product | Client     | Date
  ------------------------------------------
  John   | Bank    | Mobile App | 2019-01-01
  John   | Bank    | Desktop App| 2019-02-28
  Sally  | Gym     | Web App    | 2019-02-28
  </code></pre>
  
  <p>I want to compute if the user is "new" to that <code>Product</code> AND if they are new to that <code>Client</code>. What that means is the day they first used this <code>Product</code> is equal to <code>Date</code>, and the date they first used this <code>Product</code> AND <code>Client</code> is equal to <code>Date</code>. So, a table like this:</p>
  
  <pre><code>UserId | Product | Client     | Date       | IsNewProduct | IsNewClient
  -----------------------------------------------------------------
  John   | Bank    | Mobile App | 2019-02-28 | False        | False        // Used on 01-01
  John   | Bank    | Desktop App| 2019-02-28 | False        | True         // First time used this client was same day         
  Sally  | Bank    | Mobile App | 2019-02-28 | True         | True         // First time we saw her in this product and client
  </code></pre>
  
  <p>One way to do this is:</p>
  
  <pre><code>SELECT 
      UA.UserId, 
      UA.Product, 
      UA.Client, 
      CASE FLS.Date = UA.DATE THEN True ELSE FALSE END AS FirstSeenClient
  FROM UserActivity as UA
  LEFT JOIN FirstLastSeen AS FLS 
      ON  UA.UserId=FLS.UserId 
      AND UA.Product=FLS.Product 
      AND UA.Client=FLS.Client;
  </code></pre>
  
  <p>This would give me <code>FirstSeenClient</code> that I want. There is guaranteed to be a row corresponding to their usage. I don't know how to get <code>FirstSeenProduct</code>. I suspect the answer is in a subquery or window functions but I'm unsure how to write it, potentially <code>MIN(Date) OVER (PARTITION BY UserId, Product)</code>. I am very new to Windowing Functions, but this would get me the earliest <code>Date</code> the user was seen in that <code>Product</code>, and then I can do another <code>SELECT</code> to check the <code>Date</code>? Will the window function make sure the <code>UserId, Product</code> are the same as the row it is calculating against?</p>