Get user name and their total amount ordered.

Problem Statement

<p>Get user name and their total amount ordered.</p>

Examples

Input: users table: +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Bob | +----+-------+ orders table: +-----+---------+-------+ | id | user_id | total | +-----+---------+-------+ | 101 | 1 | 50 | | 102 | 1 | 30 | | 103 | 2 | 100 | +-----+---------+-------+

Output: +-------+---------------+ | name | total_ordered | +-------+---------------+ | Alice | 80 | | Bob | 100 | +-------+---------------+

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 INNER 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 ... INNER JOIN ... ON ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Aggregate with Join", we query the relational database engine using declarative SQL. The goal is to get user name and their total amount ordered. 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: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT users.name, SUM(orders.total) AS total_ordered FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.name;

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.

Aggregate with Join

Medium

Get user name and their total amount ordered.

Example Scenarios

1Example 1

Input:

users table

id	name
1	Alice
2	Bob

orders table

id	user_id	total
101	1	50
102	1	30
103	2	100

Output:

name	total_ordered
Alice	80
Bob	100

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.