CTE vs subquery vs view decision in SQL - Hands-On Comparison

Choosing Between CTE, Subquery, and View in SQL

📖 Scenario: You work as a data analyst for a retail company. You need to analyze sales data to find the top-selling products in each category for the last month. You want to practice writing SQL queries using different methods: Common Table Expressions (CTEs), subqueries, and views.

🎯 Goal: Build three SQL queries that find the top-selling product in each category for the last month using a CTE, a subquery, and a view. This will help you understand when to use each method.

📋 What You'll Learn

Use a CTE named MonthlySales to calculate total sales per product for the last month.

Use a subquery to find the top-selling product per category from the MonthlySales data.

Create a view named TopProductsView that shows the top-selling product per category.

Write a final query that selects all data from the TopProductsView.

💡 Why This Matters

🌍 Real World

Retail analysts often need to find top products per category to make inventory and marketing decisions. Using CTEs, subqueries, and views helps organize and reuse complex queries.

💼 Career

Understanding when to use CTEs, subqueries, or views is essential for writing clear, maintainable, and efficient SQL queries in data analyst and database developer roles.

1

Create a CTE to calculate monthly sales

Write a SQL query that creates a CTE named MonthlySales. It should select product_id, category_id, and the sum of sales_amount as total_sales from the sales table, filtering for sales in the last month (assume the column sale_date exists). Group the results by product_id and category_id.

SQL

-- Write the CTE MonthlySales here
WITH MonthlySales AS (
    -- Your code here
)

Hint

Use WITH MonthlySales AS (SELECT ...) to define the CTE. Use DATE_TRUNC and intervals to filter last month's sales.

2

Use a subquery to find top-selling products per category

Using the MonthlySales CTE from Step 1, write a SQL query that selects category_id, product_id, and total_sales where total_sales is the maximum per category. Use a subquery in the WHERE clause to find the maximum total_sales per category_id.

SQL

WITH MonthlySales AS (
    SELECT product_id, category_id, SUM(sales_amount) AS total_sales
    FROM sales
    WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
      AND sale_date < DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY product_id, category_id
)
SELECT category_id, product_id, total_sales
FROM MonthlySales
WHERE total_sales = (
    -- Your subquery here
)

Hint

Use a subquery in the WHERE clause to compare each product's sales to the max sales in its category.

3

Create a view for top-selling products per category

Create a SQL view named TopProductsView that contains the query from Step 2. The view should select category_id, product_id, and total_sales for the top-selling products per category for the last month.

SQL

-- Create the view TopProductsView here
CREATE VIEW TopProductsView AS
-- Your code here

Hint

Use CREATE VIEW TopProductsView AS followed by the query from Step 2.

4

Query the view to get top-selling products

Write a SQL query that selects all columns from the view TopProductsView.

SQL

-- Select all from TopProductsView
-- Your code here

Hint

Use SELECT * FROM TopProductsView; to get all data from the view.

Practice

1. Which SQL feature is best used to temporarily break down a complex query into simpler parts for easier reading and maintenance?

easy

A. Common Table Expression (CTE)

B. View

C. Subquery

D. Index

Solution

1. Step 1: Understand the purpose of CTEs

   CTEs allow you to define temporary named result sets that can be referenced within a query, making complex queries easier to read and maintain.

2. Step 2: Compare with other options

   Views are permanent saved queries, subqueries are nested queries but can be harder to read, and indexes are for performance, not query structure.

3. Final Answer:

   Common Table Expression (CTE) -> Option A

4. Quick Check:

   Temporary query breakdown = CTE [OK]

Hint: Temporary query parts? Use CTEs [OK]

Common Mistakes:

• Confusing views as temporary structures
• Thinking subqueries improve readability as much as CTEs
• Assuming indexes help with query structure

2. Which of the following is the correct syntax to define a Common Table Expression (CTE) in SQL?

easy

A. CREATE VIEW cte_name AS SELECT * FROM table_name;

B. WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;

C. SELECT * FROM (SELECT * FROM table_name) AS cte_name;

D. DEFINE cte_name SELECT * FROM table_name;

Solution

1. Step 1: Recall CTE syntax

   CTEs start with the keyword WITH, followed by the CTE name, AS, and a query in parentheses.

2. Step 2: Check each option

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; matches the correct syntax. CREATE VIEW cte_name AS SELECT * FROM table_name; is for views, C is a subquery, and D is invalid syntax.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; -> Option B

4. Quick Check:

   CTE syntax starts with WITH [OK]

Hint: CTE always starts with WITH keyword [OK]

Common Mistakes:

• Using CREATE VIEW syntax for CTE
• Confusing subquery parentheses with CTE
• Using undefined keywords like DEFINE

3. Given the following SQL code, what will be the output?

WITH SalesCTE AS (
  SELECT ProductID, SUM(Quantity) AS TotalSold
  FROM Sales
  GROUP BY ProductID
)
SELECT * FROM SalesCTE WHERE TotalSold > 100;

medium

A. Empty result set because SalesCTE is not materialized

B. All products with total quantity sold less than 100

C. Syntax error due to missing GROUP BY

D. All products with total quantity sold greater than 100

Solution

1. Step 1: Analyze the CTE query

   The CTE calculates total quantity sold per product by grouping Sales data by ProductID and summing Quantity.

2. Step 2: Understand the main query filter

   The main query selects rows from SalesCTE where TotalSold is greater than 100, so it returns products with sales over 100 units.

3. Final Answer:

   All products with total quantity sold greater than 100 -> Option D

4. Quick Check:

   CTE filters totals > 100 = correct output [OK]

Hint: CTE output filters rows after aggregation [OK]

Common Mistakes:

• Thinking CTE is not executed or materialized
• Confusing GROUP BY usage
• Assuming syntax error without checking carefully

4. You wrote this SQL query but it throws an error:

SELECT * FROM (
  SELECT id, name FROM users
) AS user_sub WHERE user_sub.age > 30;

What is the most likely cause of the error?

medium

A. The subquery alias 'user_sub' is missing

B. The WHERE clause should be inside the subquery

C. The subquery does not include the 'age' column needed in WHERE

D. Subqueries cannot be used in FROM clause

Solution

1. Step 1: Check subquery columns

   The subquery selects only 'id' and 'name' columns, but the outer query filters on 'age' which is not selected.

2. Step 2: Understand SQL column visibility

   Since 'age' is not in the subquery result, the outer WHERE clause referencing 'age' causes an error.

3. Final Answer:

   The subquery does not include the 'age' column needed in WHERE -> Option C

4. Quick Check:

   Missing column in subquery causes error [OK]

Hint: Ensure subquery selects all columns used outside [OK]

Common Mistakes:

• Forgetting to alias subquery
• Thinking subqueries can't be in FROM
• Moving WHERE inside subquery unnecessarily

5. You want to reuse a complex query frequently in different reports without rewriting it each time. Which SQL feature should you use and why?

hard

A. Create a View because it saves the query in the database for easy reuse.

B. Use a CTE because it is permanent and can be called anytime.

C. Use a subquery because it is stored and optimized by the database.

D. Create an index because it stores the query results for reuse.

Solution

1. Step 1: Understand the need for reuse

   Reusing a complex query multiple times means you want a saved, reusable object in the database.

2. Step 2: Compare features for reuse

   Views are saved queries stored in the database and can be used repeatedly in different reports. CTEs are temporary and exist only during query execution. Subqueries are inline and not stored. Indexes improve performance but do not store queries.

3. Final Answer:

   Create a View because it saves the query in the database for easy reuse. -> Option A

4. Quick Check:

   Persistent reusable query = View [OK]

Hint: Persistent reusable query? Use a View [OK]

Common Mistakes:

• Confusing CTEs as permanent
• Thinking subqueries are stored
• Misunderstanding indexes as query storage