title: How to make this complex query more efficient?
body: <p><a href="https://i.stack.imgur.com/TvAP7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TvAP7.png" alt="enter image description here"></a></p>
  
  <p>I want to select employees, having more than 10 products and older than 50. I also want to have their last product selected. I use the following query:</p>
  
  <pre><code>SELECT 
      PE.EmployeeID, E.Name, E.Age, 
      COUNT(*) as ProductCount,
      (SELECT TOP(1) xP.Name 
       FROM ProductEmployee xPE 
       INNER JOIN Product xP ON xPE.ProductID = xP.ID 
       WHERE xPE.EmployeeID = PE.EmployeeID 
         AND xPE.Date = MAX(PE.Date)) as LastProductName
  FROM 
      ProductEmployee PE 
  INNER JOIN 
      Employee E ON PE.EmployeeID = E.ID
  WHERE 
      E.Age &gt; 50
  GROUP BY 
      PE.EmployeeID, E.Name, E.Age
  HAVING 
      COUNT(*) &gt; 10
  </code></pre>
  
  <p>Here is the execution plan link: <a href="https://www.dropbox.com/s/rlp3bx10ty3c1mf/ximExPlan.sqlplan?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/rlp3bx10ty3c1mf/ximExPlan.sqlplan?dl=0</a></p>
  
  <p>However it takes too much time to execute it. What's wrong with it? Is it possible to make a more efficient query? </p>
  
  <p>I have one limitation - I can not use CTE. I believe it will not bring performance here anyway though.</p>