Use uncorrelated subqueries in WHERE and SELECT clauses
I want to identify our above-average performers. Pull every active Sales department employee whose total revenue exceeds the average total revenue across all active Sales reps. Show their name, their total revenue, and the department average for context. Sort by revenue descending.
Each hint you reveal reduces the XP you can earn. Try the query first.
First figure out total revenue per active sales employee by joining fact_sales to dim_employees
The department average is the average of per-rep totals — use a subquery: SELECT AVG(rep_total) FROM (SELECT SUM(revenue) as rep_total ...)
Compare each rep's total to this average in a HAVING clause or wrap the whole thing in a subquery