Use correlated subqueries to compare each row against its own group's aggregate
For each sale by an active sales rep, I want to know if that individual sale's revenue was above that rep's own average sale revenue. Show the sale_id, employee first and last name, revenue, that rep's average revenue, and a flag column: 'Above Average' or 'Below Average'. Only include active Sales reps.
Each hint you reveal reduces the XP you can earn. Try the query first.
Use a correlated subquery in SELECT: (SELECT AVG(s2.revenue) FROM fact_sales s2 WHERE s2.employee_id = s.employee_id) to get each rep's average
Use CASE WHEN to compare sale revenue to the correlated average and produce the flag
Join fact_sales to dim_employees, filter to active Sales department