Combine multiple advanced techniques (window functions, subqueries, CASE WHEN, multi-table joins, NULL handling) in a single analytical query
Final boss time. I need one query that gives me, for each active Sales rep: their name, total 2025 revenue, their rank by 2025 revenue, what percentage of total 2025 Sales revenue they represent, their Q3 2025 target attainment (actual/target as a percentage — NULL if no target), and whether they are 'Above Quota' (attainment >= 100%) or 'Below Quota' or 'No Data'. Sort by 2025 revenue descending. This is going to the board as-is.
Each hint you reveal reduces the XP you can earn. Try the query first.
Start with a subquery that gets each active Sales rep's 2025 revenue from fact_sales
Add RANK() OVER and SUM() OVER() for the rank and percentage in an outer query
LEFT JOIN to fact_targets for Q3 2025 (quarter=3, year=2025) to get attainment — use CASE WHEN for the status flag, handling NULL actual_amount