Nested Subquery for Percentile Filtering

Write a query to find employees whose salary is above the average salary of their own department. Return name, department, and salary, ordered by department, then salary DESC.

Expected Output: Above-average earners within each department.