Understanding SQL’s Subqueries & Common Table Expressions (CTEs)

Sometimes we need to extract data from different tables, then, we may need to extract another set of results then compare the first results with the second result.

As such this becomes a challenge to only use the standard SQL syntax. To counter this challenge, we can either use simple subqueries for faster results of the complex yet reusable quries refereed to as CTEs (Common Table Expression)

In this article, I will refer to a project that has 3 tables on sales.

• Customer table;
• Sales table; and
• Inventory table.

We shall explore how subquery is used to extract information from a table, then a main query is written on top of the query.

Subqueries.

Below is a simple subquery that finds the average spending of customers then a main query is written and wraps the subqury inside for a comparison.

--Which customers have spent more than the average spending of all customers?

SELECT 
 concat(c.first_name , ' ', c.last_name ) AS full_name,
 s.total_amount 
FROM ASSIGNMENT.sales s 
JOIN ASSIGNMENT.customers c 
  ON s.customer_id = c.customer_id 
WHERE s.total_amount > (SELECT AVG(s.total_amount )
      FROM ASSIGNMENT.sales s );

Advantages of Subqueries.

1. Compatibility-can be used by old databases;
2. Dynamic Filtering; and
3. Easy calculations.

Common Table Expression CTEs

Get Brian Igana’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

These are used to create temporal tables that can later be used buy other queries to compare a certain result or extract specific information.

Below is a sample CTE that creates one reusable table.

-- Create an intermediate result showing total sales per product category,
--     then determine which category generates the highest revenue.

WITH total_sales_per_category AS (
 SELECT 
  p.category, 
  SUM(s.total_amount ) AS total_revenue 
 FROM 
  ASSIGNMENT.products p 
 JOIN
  ASSIGNMENT.sales s
   ON p.product_id = s.product_id
 GROUP BY 
  p.category 
)
SELECT 
 category,
 total_revenue,  
 rank() over(ORDER BY total_revenue DESC ) AS category_rank
FROM total_sales_per_category 
ORDER BY total_sales_per_category DESC 
;

Below is a sample of a CTE that creates two temporal tables that can be used to extract relevant results.

-- Create an intermediate result that calculates the total quantity sold per product,
--     then determine which products sold more than the average quantity sold.

--WITH total_product_sold AS (
  SELECT 
    p.product_id ,
    p.product_name ,
    SUM (S.quantity_sold ) AS total_per_product_sold
  FROM ASSIGNMENT.products p
  JOIN ASSIGNMENT.sales s 
   ON p.product_id = s.product_id 
  GROUP BY p.product_id ,
     p.product_name;
),
 avg_quantity_sold AS (
  SELECT AVG(s.quantity_sold ) AS avg_qnty_sold
  FROM ASSIGNMENT.sales s
)
SELECT  
  product_id,
  product_name 
FROM total_product_sold
CROSS JOIN avg_quantity_sold 
WHERE total_product_sold > avg_qnty_sold 
ORDER BY total_product_sold
;

Advantages of CTEs

1. Easy to understand codes;
2. Reusable; and
3. Recursive.

This has been a challenge that I walked on.

I had to research, unlearn and relearn as such, I ended up uncovering new tricks that were interesting.

This is still an ongoing project that am still working on the unanswered questions.

Below is a link to the full bootcamp project that covered over 100 SQL queries from beginner level to intermediate. Have a look

GitHub - iganabrian/Assignment-4-139-sql-Queries-Subqueries-CTEs-Rank-Window-Functions: I worked on…