49. Department Payroll (Aggregate Over Join)
Aggregates often run over a join: join the tables first, then GROUP BY a column from one side and aggregate a column from the other. Group by the department name (from departments), not just the id, and remember that GROUP BY happens after the join.
Task: for each department, return the head count and total salary, but only for departments whose total salary exceeds 100.
Return columns department, headcount, total_salary where headcount = COUNT(employee rows) and total_salary = SUM(salary). Keep only departments with total_salary > 100. Order by total_salary descending, then department ascending.
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.