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
)

Need a 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
)

Need a 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

Need a 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

Need a hint?

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