Task
At Northstar Analytics, finance tracks department spending in a separate expense table. Write a SQL query to join the department and expense data and return the total cost for each department.
Requirements
- Join
departments and expenses using the department key.
- Return each department's name and the sum of its expense
cost values.
- Group the results by department name.
- Order the output by total cost descending, then department name ascending.
Table Definitions
departments
| column_name | type | description |
|---|
| department_id | INT | Unique department identifier |
| department_name | VARCHAR(100) | Department name |
| region | VARCHAR(50) | Region assigned to the department |
expenses
| column_name | type | description |
|---|
| expense_id | INT | Unique expense identifier |
| department_id | INT | Department linked to the expense |
| expense_type | VARCHAR(50) | Category of expense |
| cost | DECIMAL(10,2) | Expense amount |
Sample Data
departments
| department_id | department_name | region |
|---|
| 30 | Engineering | West |
| 10 | Sales | East |
| 50 | Legal | East |
| 20 | Marketing | Central |
expenses
| expense_id | department_id | expense_type | cost |
|---|
| 1004 | 20 | Events | 1200.00 |
| 1001 | 10 | Travel | 500.00 |
| 1002 | 10 | Software | 300.00 |
| 1006 | 30 | Cloud | 2000.00 |
Expected Output
| department_name | total_cost |
|---|
| Engineering | 3500.00 |
| Marketing | 1200.00 |
| Sales | 800.00 |
| Legal | 0.00 |
