Combine date extraction, subqueries, and window functions for cohort-based analysis
Group active Sales reps by their hire year (exclude bad date employees 25 and 42). For each hire-year cohort, show the hire year, number of reps, total revenue, average revenue per rep, and what percentage of total Sales revenue that cohort generated. I want to know if our newer hires are ramping up or if old-timers carry the load.
Each hint you reveal reduces the XP you can earn. Try the query first.
Extract hire year from hire_date, filtering out employee_id IN (25, 42) for bad dates, and is_active = 1, department = 'Sales'
Join dim_employees to fact_sales to get revenue per rep, then aggregate by hire year
Use a subquery for total Sales revenue to compute each cohort's percentage share