Subqueries, pivoting and ranking functions