Hours logged on each project
Medium · Company database · LEFT JOIN, GROUP BY, NULL values
Add up the hours logged on each project in assignments. A project nobody has worked on shows 0 hours.
Return the project name and the total hours, in that order, most hours first; equal totals by project name A to Z.
Expected result
| name | total_hours |
|---|---|
| Battery Research | 1200 |
| Payments Platform | 1110 |
| Data Warehouse | 680 |
| Spring Campaign | 540 |
| Help Centre Revamp | 530 |
| Mobile App Redesign | 530 |
| Partner Portal | 465 |
| Budget Planning Tool | 420 |
| Hiring Drive | 160 |
| Contract Review System | 0 |
The company tables
assignments: 30 rows, first 5 shown
| id | employee_id | project_id | hours |
|---|---|---|---|
| 1 | 8 | 1 | 320 |
| 2 | 9 | 1 | 410 |
| 3 | 11 | 1 | 380 |
| 4 | 12 | 2 | 290 |
| 5 | 10 | 2 | 150 |
departments: 8 rows, first 5 shown
| id | name | location |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | Sales | London |
| 3 | Marketing | London |
| 4 | Finance | Singapore |
| 5 | Human Resources | Bengaluru |
employees: 27 rows, first 5 shown
| id | name | department_id | manager_id | job_title | salary | hire_date |
|---|---|---|---|---|---|---|
| 1 | Meera Krishnan | NULL | NULL | Chief Executive Officer | 250000 | 2015-03-01 |
| 2 | Daniel Okoye | 1 | 1 | Chief Technology Officer | 250000 | 2015-03-01 |
| 3 | Sophie Laurent | 2 | 1 | Head of Sales | 150000 | 2017-01-09 |
| 4 | Rahul Bansal | 4 | 1 | Finance Director | 166000 | 2016-11-21 |
| 5 | Hannah Schmidt | 5 | 1 | Human Resources Manager | 110000 | 2018-04-02 |
projects: 10 rows, first 5 shown
| id | name | department_id | budget | start_date | end_date |
|---|---|---|---|---|---|
| 1 | Payments Platform | 1 | 450000 | 2025-01-06 | 2025-09-30 |
| 2 | Mobile App Redesign | 1 | 300000 | 2025-03-03 | NULL |
| 3 | Data Warehouse | 1 | 380000 | 2025-06-02 | 2026-03-31 |
| 4 | Spring Campaign | 3 | 90000 | 2025-02-10 | 2025-05-30 |
| 5 | Partner Portal | 2 | 150000 | 2025-04-14 | 2025-12-19 |
-- Write your SQL here