title: How to join multiple times on the same table with the max value for each row
body: <p>I have a table activity that has 5 fields:</p>
  <pre><code>id - numeric
  user_id - numeric 
  type_id - numeric
  content_id - numeric
  date_of_activity date
  </code></pre>
  <p>I need to make a query, the result of which will be 7 columns:</p>
  <pre><code>id(type1 activity),content_id(type 1) at max(date_of_activity for type 1),max(date_of_activity for type 1)content_id(type 2) at max(date_of_activity for type 2),max(date_of_activity for type 2)content_id(type 3) at max(date_of_activity for type 3),max(date_of_activity for type 3)
  </code></pre>
  <p>If there is no corresponding value for type_id for an user  then NULL value should be shown in corresponding column.</p>
  <p>Example:</p>
  <pre><code>users
      user_id: 1
      user_id: 2
      user_id: 3
  </code></pre>
  <pre><code>activities
  
      id: 1
      user_id: 1
      type_id: 2
      date_of_activity: 2021/05/01
      content_id: 12
  
      id: 2
      user_id: 1
      type_id: 3
      date_of_activity: 2021/05/01
      content_id: 102
  
      id: 3
      user_id: 2
      type_id: 3
      date_of_activity: 2021/05/01
      content_id: 213
  
      id: 4
      user_id: 1
      type_id: 2
      date_of_activity: 2021/05/02
      content_id: 13
  
      id: 5
      user_id: 1
      type_id: 3
      date_of_activity: 2021/05/02
      content_id: 103
  
      id: 6
      user_id: 1
      type_id: 1
      date_of_activity: 2021/05/03
      content_id: 1
  
      id: 7
      user_id: 2
      type_id: 1
      date_of_activity: 2021/05/03
      content_id: 212
      
      id: 8
      user_id: 1
      type_id: 3
      date_of_activity: 2021/05/03
      content_id: 104
  
      id: 8
      user_id: 1
      type_id: 3
      date_of_activity: 2021/05/04
      content_id: 105
  
      id: 10
      user_id: 1
      type_id: 1
      date_of_activity: 2021/05/05
      content_id: 2
  
      id: 11
      user_id: 1
      type_id: 3
      date_of_activity: 2021/05/05
      content_id: 106
  </code></pre>
  <p>the result should be:</p>
  <pre><code>id:10,
  user_id:1,
  type_id:1,
  date_of_activity: 2021/05/05,
  activity2.date_of_activity:2021/05/02,
  activity2.content_id:13,
  activity3.date_of_activity: 2021/05/05,
  activity3.content_id:106
  
  id:7,
  user_id: 2,
  type_id: 1,
  date_of_activity: 2021/05/03,
  content_id: 212,
  activity2.date_of_activity: NULL,
  activity2.content_id: NULL,
  activity3.date_of_activity:2021/05/01
  activity3.content_id:213
  </code></pre>
  <p>As you can see i retreived one activity , per activity type for each user, each activity being the most recent one, and so on for all the users...</p>
  <p>I have a query than can retrieve the most recent activity for each user for ONE activity type :</p>
  <pre><code>SELECT
  activities.*,
  dDates.max
  FROM activities
  JOIN users on users.id = activities.user_id
  INNER JOIN (SELECT user_id,max(activities.date_of_activity) FROM activities WHERE activities.type_id = 6 AND activities.deleted_at IS NULL group by user_id) as dDates on activities.user_id = maxDates.user_id
  WHERE activities.type_id = 6 AND activities.deleted_at IS NULL AND activities.date_of_activity = dDates.max;
  </code></pre>
  <p>But sometimes i need to join on multiples activities as part of the same query so i'm trying to grab everything at once but i can't find a way that isn't very slow.</p>
  <p>i've tried created a view / use &quot;With&quot;  for each type :</p>
  <pre><code>CREATE VIEW MostRecentType1 AS
  SELECT
  activities.*,
  dDates.max
  FROM activities
  JOIN users on users.id = activities.user_id
  INNER JOIN (SELECT user_id,max(activities.date_of_activity) FROM activities WHERE activities.type_id = 1 AND activities.deleted_at IS NULL group by user_id) as dDates on activities.user_id = maxDates.user_id
  WHERE activities.type_id = 1 AND activities.deleted_at IS NULL AND activities.date_of_activity = dDates.max;
  </code></pre>
  <pre><code>CREATE VIEW MostRecentType2 AS
  SELECT
  activities.*,
  dDates.max
  FROM activities
  JOIN users on users.id = activities.user_id
  INNER JOIN (SELECT user_id,max(activities.date_of_activity) FROM activities WHERE activities.type_id = 1 AND activities.deleted_at IS NULL group by user_id) as dDates on activities.user_id = maxDates.user_id
  WHERE activities.type_id = 1 AND activities.deleted_at IS NULL AND activities.date_of_activity = dDates.max;
  </code></pre>
  <p>and then a finally a left join on the views</p>
  <pre><code>SELECT
  activities.*,
  dDates.max,
  type2.date_of_activity as &quot;type2.date_of_activity as&quot;,
  type2.content_id as &quot;type2.content_id&quot;,
  type3.date_of_activity as &quot;type3.date_of_activity&quot; ,
  type3.content_id as &quot;type3.content_id&quot; ,
  FROM activities
  JOIN users on users.id = activities.user_id
  INNER JOIN (SELECT user_id,max(activities.date_of_activity) FROM activities WHERE activities.type_id = 1 AND activities.deleted_at IS NULL group by user_id) as dDates on activities.user_id = maxDates.user_id
  LEFT JOIN MostRecentType2 as type2 on activities.user_id = type2.user_id
  LEFT JOIN MostRecentType3 as type3 on activities.user_id = type3.user_id
  WHERE activities.type_id = 1 AND activities.deleted_at IS NULL AND activities.date_of_activity = dDates.max;
  </code></pre>
  <p>But this doesn't scale well at all.</p>
  <p>On my mostly empty activities table if i just perform the 1st query alone it will take 4ms, but once i add the first &quot;MostRecentType2&quot; join it jumps
  to 40ms, then MostRecentType3 go to 80 ms and keeps on incrementing this way and in some case i need lots of joins.</p>
  <p>Any ideas on how to (better) make that query  ?</p>