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.