Fetching busy students and their latest product

I use SQL Server 2014 and I have three tables:

• Student (ID, Name, Age)

• Product (ID, Name)

• StudentProduct (StudentID, ProductID, Date)

TABLE Student(ID, Name, Age) >>
ID Name Age
1 Adam 20
2 Bob 17
3 Ben 18
4 Joseph 18
5 Jon 18

TABLE Product (ID, Name) >>
ID Name
1 SQLServer
2 Visual Studio
3 Idea
4 Eclipse

TABLE StudentProduct (StudentID, ProductID, Date) >>
StudentID ProductID Date
1 1 25.05.2016 0:00:00
1 4 26.06.2016 0:00:00
1 3 27.06.2016 0:00:00
1 2 28.06.2016 0:00:00
2 1 29.07.2016 0:00:00


I've written a query to get all students who meet these criteria

• Are older than 19

• Participate in developing more than 3 products

Both criteria should be checked in a single query.

The desired result should then contain the

• Age of the student

• Name of student

• Name of the last developed product (using StudentProduct.Date)

Result should be:

Age   StudentName      ProductName
20    Adam             Visual Studio

My query is:

select e.age, e.name ,
(select top 1 p.Name from Product p
inner join StudentProduct peOut on p.ID = peOut.ProductID
where peOut.StudentID = e.ID
order by peOut.Date desc)
from Student e where e.age > 19
and exists (select pe.StudentID, count(1) from StudentProduct pe
where pe.StudentID = e.ID group by pe.StudentID
having count(1) > 3)

How can I improve the efficiency of my query?

Without additional info about indexes and row counts it's hard to tell, but the most elegant solution is probably using Analytical Functions (using employees instead of students):

with cte as
 (
    select s.age, s.name as EmployeeName, p.Name as ProductName,
       -- row with the newest date
       row_number() over (partition by s.ID order by sp.Date desc) as rn,
       -- number of Products per employee
       count(*) over (partition by s.ID) as cnt
    from Student as s 
    join StudentProduct as sp
      on sp.EmployeeID = e.ID
    join Product as p
      on p.ID = sp.ProductID
    where s.age > 19
 )
select age, EmployeeName, ProductName
from cte
where cnt > 3  -- more than three products
  and rn = 1   -- the latest product

StackExchange Code Review Q#138758, answer score: 5