Show each department name, number of employees, and total order value for employees in that department.

Problem Statement

<p>Show each department name, number of <code>employees</code>, and total order value for <code>employees</code> in that department.</p>

Examples

Input: departments table: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | HR | | 3 | Sales | +----+-------+ employees table: +----+---------+---------+ | id | name | dept_id | +----+---------+---------+ | 1 | Alice | 1 | | 2 | Bob | 1 | | 3 | Charlie | 2 | | 4 | Dana | 3 | +----+---------+---------+ orders table: +-----+-------------+--------+ | id | employee_id | amount | +-----+-------------+--------+ | 101 | 1 | 500 | | 102 | 1 | 300 | | 103 | 2 | 400 | | 104 | 3 | 200 | | 105 | 4 | 600 | +-----+-------------+--------+

Output: +------------+-----------+--------------+ | department | emp_count | total_orders | +------------+-----------+--------------+ | IT | 2 | 1200 | | Sales | 1 | 600 | | HR | 1 | 200 | +------------+-----------+--------------+

Explanation: The query joins the matching records on the related keys and projects the requested fields.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Determine which tables contain the required columns and relate them using LEFT JOIN on the corresponding foreign/primary keys. 💡 Hint 2: Write out the ON condition matching key fields (e.g. ON a.key = b.key) and apply any filtering in the WHERE clause. 💡 Hint 3: Select only the requested columns in the SELECT clause with clear aliases if needed: SELECT ... FROM ... LEFT JOIN ... ON ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Complex Multi-Join Aggregate Report", we query the relational database engine using declarative SQL. The goal is to show each department name, number of employees, and total order value for employees in that department. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply table joins to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

SELECT d.name AS department, COUNT(DISTINCT e.id) AS emp_count, COALESCE(SUM(o.amount), 0) AS total_orders FROM departments d LEFT JOIN employees e ON d.id = e.dept_id LEFT JOIN orders o ON e.id = o.employee_id GROUP BY d.name ORDER BY total_orders DESC;

Complexity Analysis

Time Complexity O(N * M) worst-case, O(N + M) with hash/merge join on indexed keys.

Space Complexity O(N) for join buffer and result set.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Complex Multi-Join Aggregate Report

Hard

Show each department name, number of employees, and total order value for employees in that department.

Example Scenarios

1Example 1

Input:

departments table

id	name
1	IT
2	HR
3	Sales

employees table

id	name	dept_id
1	Alice	1
2	Bob	1
3	Charlie	2
4	Dana	3

orders table

id	employee_id	amount
101	1	500
102	1	300
103	2	400
104	3	200
105	4	600

Output:

department	emp_count	total_orders
IT	2	1200
Sales	1	600
HR	1	200

Explanation:

The query joins the matching records on the related keys and projects the requested fields.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.