title: Filter to date range AFTER grouping by most recent occurrence - SQL
body: <p>I'm trying to write some SQL that will pull out the most recent login of a user on our site, and then filter that to be in the date range of between 60-90 days ago. Accounts are made inactive after 90 days so we send a warning email (just to add flavour to this query).</p>
  <p>I can do both halves of this, getting most recent and getting a range, but am having trouble putting them together.</p>
  <p>The query pulls from two tables, as the table with log in dates only has a user ID, so I inner join the table that has user ID and username (email address), tables below and each has more columns than listed.</p>
  <p><strong>Table 1 = &quot;time.log&quot;</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">log in date</th>
  <th style="text-align: center;">user id</th>
  <th style="text-align: right;">application</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">25/02/2022</td>
  <td style="text-align: center;">123</td>
  <td style="text-align: right;">website</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Table 2 = &quot;users.id&quot;</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">user id</th>
  <th style="text-align: center;">username</th>
  <th style="text-align: right;">name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">123</td>
  <td style="text-align: center;">john@smith.com</td>
  <td style="text-align: right;">John Smith</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Current code</strong></p>
  <pre><code>SELECT MAX(t.log_time) AS 'Date',
  u.username AS 'Email'
  
  FROM time.log t
      INNER JOIN users.id u ON t.user_id = u.user_id   
  
  WHERE t.log_time BETWEEN Convert(DATE,GETDATE()-90) AND Convert(DATE,GETDATE()-60)
  
  GROUP BY u.username
  </code></pre>
  <p>The result I am getting is the top log in date in that range, rather than it being the range of users who last logged in during that period. That makes sense when you read the query through but I can't seem to filter it after the GROUP BY has occurred.</p>
  <p>I am using SQL Server Management Studio incase that is relevant - if you can't tell I am a SQL newbie.</p>
  <p>I also tried using a second SELECT sequence but I can't get the query to execute at all (it seems to want something after the bracket but I don't know what.</p>
  <pre><code>SELECT MAX(t.log_time) AS 'Date',
  u.username AS 'Email'
  
  FROM time.log t
      INNER JOIN users.id u ON t.user_id = u.user_id   
  
  INNER JOIN(   
      SELECT MAX(t2.log_time) 
      FROM time.log t2
      INNER JOIN users.id u2 ON t2.user_id = u2.user_id
      GROUP BY u2.username) ON 
      
      t.user_id = t2.user_id AND t.log_time = t2.log_time
  
  </code></pre>
  <p>Help very much appreciated.</p>