Subqueries in FROM (derived tables) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple derived table query

What is the output of this query?

SELECT dt.category, dt.total_sales FROM (SELECT category, SUM(sales) AS total_sales FROM sales_data GROUP BY category) AS dt ORDER BY dt.total_sales DESC;

PostgreSQL

CREATE TABLE sales_data (category TEXT, sales INT);
INSERT INTO sales_data VALUES
('Books', 100), ('Books', 150), ('Electronics', 200), ('Electronics', 300), ('Clothing', 50);

A

category | total_sales
---------|------------
Books | 250
Electronics | 500
Clothing | 50

B

category | total_sales
---------|------------
Electronics | 500
Books | 250
Clothing | 50

C

category | total_sales
---------|------------
Clothing | 50
Books | 250
Electronics | 500

D

category | total_sales
---------|------------
Electronics | 300
Books | 150
Clothing | 50

💡 Hint

Remember the subquery groups sales by category and sums them, then the outer query orders by total_sales descending.

The subquery sums sales per category: Electronics (200+300=500), Books (100+150=250), Clothing (50). The outer query orders by total_sales descending, so Electronics first, then Books, then Clothing.

📝 Syntax

intermediate

Identify the syntax error in derived table usage

Which option contains a syntax error when using a derived table in the FROM clause?

A SELECT dt.category, dt.total FROM (SELECT category, COUNT(*) AS total FROM products GROUP BY category) dt;

B SELECT dt.category, dt.total FROM (SELECT category, COUNT(*) AS total FROM products GROUP BY category) dt WHERE dt.total > 5;

C SELECT dt.category, dt.total FROM (SELECT category, COUNT(*) AS total FROM products GROUP BY category);

D SELECT dt.category, dt.total FROM (SELECT category, COUNT(*) AS total FROM products GROUP BY category) AS dt;

💡 Hint

Derived tables must have an alias in the FROM clause.

Option C is missing an alias for the derived table, which is required in SQL. Options A, C, and D correctly alias the subquery as dt.

❓ optimization

advanced

Optimizing a query with multiple derived tables

Given two derived tables that calculate sales and returns per product, which query is more efficient and why?

PostgreSQL

SELECT s.product_id, s.total_sales, r.total_returns
FROM (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id) s
JOIN (SELECT product_id, SUM(amount) AS total_returns FROM returns GROUP BY product_id) r ON s.product_id = r.product_id;

A Aggregate sales and returns separately, then join on product_id, but add indexes on product_id columns.

B Replace derived tables with CTEs (WITH clauses) to improve readability but no performance gain.

C Join sales and returns tables first, then aggregate in a single derived table.

D Use the query as is; derived tables are necessary for aggregation before join.

💡 Hint

Think about indexing and aggregation order for performance.

Option A is best because aggregating separately then joining is efficient, and adding indexes on product_id speeds up joins. Option A aggregates after join, which can be slower due to larger intermediate data. Option A improves readability but not performance. Option A is correct but not optimized.

🔧 Debug

advanced

Debugging a query with nested derived tables

This query returns an error. What is the cause?

SELECT dt1.category, dt2.avg_price FROM (SELECT category FROM products) dt1 JOIN (SELECT category, AVG(price) AS avg_price FROM products GROUP BY category) dt2 ON dt1.category = dt2.category WHERE dt1.avg_price > 100;

A The WHERE clause references dt1.avg_price which is not available at that scope.

B The alias dt2 is missing in the second derived table.

C The join condition is invalid because dt1.category is not selected properly.

D The first derived table dt1 lacks aggregation and causes ambiguity.

💡 Hint

Check the columns selected in the derived tables referenced in the WHERE clause.

The first derived table dt1 selects only 'category' and does not have 'avg_price'. The WHERE clause references 'dt1.avg_price', which does not exist, causing a 'column does not exist' error. Option A is incorrect because no aggregation is needed for dt1 here. Option A is wrong as dt2 has an alias. Option A is wrong because dt1.category is properly selected.

🧠 Conceptual

expert

Understanding scope and aliasing in nested derived tables

Consider this query:

SELECT outer_dt.category, outer_dt.total FROM (SELECT inner_dt.category, inner_dt.total FROM (SELECT category, COUNT(*) AS total FROM orders GROUP BY category) AS inner_dt WHERE inner_dt.total > 10) AS outer_dt;

Which statement is true about the scope and aliasing in this query?

A The query will fail because nested derived tables cannot be used in PostgreSQL.

B The alias outer_dt is unnecessary and can be removed without error.

C The WHERE clause filters on a column not available in inner_dt, causing an error.

D The alias inner_dt is only visible inside the outer derived table and cannot be referenced outside it.

💡 Hint

Think about how aliases define scope inside nested queries.

Alias inner_dt is defined inside the outer derived table and is only visible there. The outer derived table alias outer_dt is necessary to reference the subquery in the outer SELECT. The WHERE clause correctly filters on inner_dt.total which exists. Nested derived tables are allowed in PostgreSQL.

Practice

1. What is the main purpose of using a subquery in the FROM clause in PostgreSQL?

easy

A. To create a permanent table in the database

B. To update data in multiple tables at once

C. To delete rows from a table based on a condition

D. To create a temporary table that can be used in the main query

Solution

1. Step 1: Understand the role of subqueries in FROM

   Subqueries in the FROM clause act like temporary tables that exist only during the query execution.

2. Step 2: Compare with other options

   Updating or deleting data and creating permanent tables are not done by subqueries in FROM.

3. Final Answer:

   To create a temporary table that can be used in the main query -> Option D

4. Quick Check:

   Subquery in FROM = temporary table [OK]

Hint: Subquery in FROM = think temporary table [OK]

Common Mistakes:

• Thinking subqueries in FROM create permanent tables
• Confusing subqueries with UPDATE or DELETE statements
• Forgetting subqueries need an alias

2. Which of the following is the correct syntax for using a subquery in the FROM clause in PostgreSQL?

easy

A. SELECT * FROM (SELECT id FROM users) AS sub;

B. SELECT * FROM users WHERE (SELECT id FROM users);

C. SELECT * FROM users JOIN (SELECT id FROM users);

D. SELECT * FROM users (SELECT id FROM users) AS sub;

Solution

1. Step 1: Identify correct subquery syntax in FROM

   The subquery must be enclosed in parentheses and given an alias using AS.

2. Step 2: Check each option

   SELECT * FROM (SELECT id FROM users) AS sub; correctly uses parentheses and alias. Others either misuse WHERE, JOIN without ON, or lack alias.

3. Final Answer:

   SELECT * FROM (SELECT id FROM users) AS sub; -> Option A

4. Quick Check:

   Subquery in FROM needs parentheses + alias [OK]

Hint: Always alias subqueries in FROM with AS [OK]

Common Mistakes:

• Omitting alias after subquery
• Using subquery in WHERE without proper syntax
• Trying to JOIN without ON condition

3. Given the tables:
users(id, name)
orders(id, user_id, amount)
What will the following query return?

SELECT sub.name, sub.total FROM (SELECT u.name, SUM(o.amount) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name) AS sub WHERE sub.total > 100;

medium

A. Syntax error due to missing GROUP BY

B. All users with their total order amount including those with zero

C. Names of users with total order amount greater than 100

D. Empty result because no user has orders

Solution

1. Step 1: Analyze the subquery

   The subquery calculates total order amount per user using LEFT JOIN and GROUP BY user name.

2. Step 2: Apply the outer WHERE filter

   The outer query filters users whose total order amount is greater than 100.

3. Final Answer:

   Names of users with total order amount greater than 100 -> Option C

4. Quick Check:

   Subquery sums orders, outer filters total > 100 [OK]

Hint: Subquery computes totals; outer filters results [OK]

Common Mistakes:

• Ignoring GROUP BY causing syntax error
• Confusing LEFT JOIN with INNER JOIN results
• Assuming all users appear without filtering

4. Identify the error in this query:

SELECT sub.name, sub.order_count FROM (SELECT name, COUNT(*) FROM users GROUP BY name) sub;

medium

A. No error, query is correct

B. Missing alias for COUNT(*) column

C. Incorrect table name 'users'

D. Subquery missing parentheses

Solution

1. Step 1: Check subquery SELECT list

   COUNT(*) needs an alias to be referenced outside the subquery.

2. Step 2: Understand outer query references

   Outer query uses sub.order_count, but COUNT(*) column has no alias, causing error.

3. Final Answer:

   Missing alias for COUNT(*) column -> Option B

4. Quick Check:

   Aggregate columns need aliases in subqueries [OK]

Hint: Always alias aggregate columns in subqueries [OK]

Common Mistakes:

• Forgetting to alias aggregate functions
• Assuming subquery columns auto-named
• Ignoring parentheses around subquery

5. You want to find the average order amount per user but only for users who have placed more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?

hard

A. SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3;

B. SELECT user_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 GROUP BY user_id;

C. SELECT user_id, AVG(amount) FROM (SELECT * FROM orders WHERE COUNT(*) > 3) AS sub GROUP BY user_id;

D. SELECT user_id, AVG(amount) FROM orders GROUP BY user_id HAVING AVG(amount) > 3;

Solution

1. Step 1: Understand the requirement

   We need average order amount per user but only for users with more than 3 orders.

2. Step 2: Analyze each option

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; uses a subquery to calculate average and count per user, then filters users with order_count > 3 outside. Others misuse COUNT in WHERE or HAVING incorrectly.

3. Final Answer:

   SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; -> Option A

4. Quick Check:

   Subquery calculates aggregates; outer filters by count [OK]

Hint: Filter aggregates in outer query after subquery [OK]

Common Mistakes:

• Using COUNT(*) in WHERE clause directly
• Misplacing HAVING clause conditions
• Not aliasing subquery columns properly