Common Table Expression With Examples
5 min readOct 6, 2024
A Common Table Expression (CTE) is a temporary result set that you can reference within a SQL query. Think of it as creating a mini table or a shortcut that simplifies complex queries. You define it at the start of your query using the WITH keyword, then use it in the main part of your query just like a regular table. It’s especially useful for breaking down long, complicated queries into smaller, more manageable pieces. Unlike subqueries, CTEs are easier to read and reuse within the same query, making your SQL code clearer.
Common Table Expression Structure
WITH
my_cte (cte_col_1, cte_col_2) AS (
-- body of the CTE
SELECT col_1, col_2
FROM ...
)
-- CTE Query
SELECT ... FROM my_cte;
Create Table Objects
-- Creating Customers Table
CREATE OR REPLACE TABLE Customers (
Customer_ID INT PRIMARY KEY,
Customer_Name VARCHAR(100),
Country VARCHAR(50)
);
-- Creating Products Table
CREATE OR REPLACE TABLE Products (
Product_ID VARCHAR(10) PRIMARY KEY,
Product_Name VARCHAR(100),
Category VARCHAR(50)
);
-- Creating Sales_Transactions Table
CREATE OR REPLACE TABLE Sales_Transactions (
Transaction_ID INT PRIMARY KEY,
Customer_ID INT,
Product_ID VARCHAR(10),
Sales_Amount DECIMAL(10, 2),
Transaction_Date DATE,
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
);Insert Customer Data
-- Inserting Customer Data
INSERT INTO Customers (Customer_ID, Customer_Name, Country) VALUES
(1, 'Alice', 'USA'),
(2, 'Srini', 'India'),
(3, 'Charlie', 'UK'),
(4, 'David', 'Australia'),
(5, 'Eve', 'Germany'),
(6, 'Rahul', 'India'),
(7, 'Grace', 'Germany'),
(8, 'Heidi', 'Japan'),
(9, 'Nishant', 'India'),
(10, 'Judy', 'Australia');Insert Product Data
-- Inserting Customer Data
INSERT INTO Customers (Customer_ID, Customer_Name, Country) VALUES
(1, 'Alice', 'USA'),
(2, 'Srini', 'India'),
(3, 'Charlie', 'UK'),
(4, 'David', 'Australia'),
(5, 'Eve', 'Germany'),
(6, 'Rahul', 'India'),
(7, 'Grace', 'Germany'),
(8, 'Heidi', 'Japan'),
(9, 'Nishant', 'India'),
(10, 'Judy', 'Australia');Insert Sales Transaction Data
-- Inserting Sales Transactions Data
INSERT INTO Sales_Transactions (Transaction_ID, Customer_ID, Product_ID, Sales_Amount, Transaction_Date) VALUES
(1, 1, 'P01', 15.00, '2024-09-01'),
(2, 2, 'P02', 10.00, '2024-09-01'),
(3, 3, 'P03', 25.00, '2024-09-02'),
(4, 4, 'P04', 50.00, '2024-09-02'),
(5, 5, 'P05', 30.00, '2024-09-03'),
(6, 6, 'P06', 20.00, '2024-09-03'),
(7, 7, 'P07', 10.00, '2024-09-04'),
(8, 8, 'P08', 40.00, '2024-09-04'),
(9, 9, 'P09', 100.00, '2024-09-05'),
(10, 10, 'P10', 8.00, '2024-09-05'),
(11, 1, 'P02', 12.00, '2024-09-06'),
(12, 2, 'P03', 22.00, '2024-09-06'),
(13, 3, 'P04', 55.00, '2024-09-07'),
(14, 4, 'P05', 33.00, '2024-09-07'),
(15, 5, 'P06', 18.00, '2024-09-08'),
(16, 6, 'P07', 11.00, '2024-09-08'),
(17, 7, 'P08', 39.00, '2024-09-09'),
(18, 8, 'P09', 95.00, '2024-09-09'),
(19, 9, 'P10', 7.00, '2024-09-10'),
(20, 10, 'P01', 14.00, '2024-09-10'),
(21, 1, 'P03', 26.00, '2024-09-11'),
(22, 2, 'P04', 52.00, '2024-09-11'),
(23, 3, 'P05', 28.00, '2024-09-12'),
(24, 4, 'P06', 19.00, '2024-09-12'),
(25, 5, 'P07', 13.00, '2024-09-13'),
(26, 6, 'P08', 38.00, '2024-09-13'),
(27, 7, 'P09', 90.00, '2024-09-14'),
(28, 8, 'P10', 6.00, '2024-09-14'),
(29, 9, 'P01', 16.00, '2024-09-15'),
(30, 10, 'P02', 11.00, '2024-09-15'),
(31, 1, 'P04', 45.00, '2024-09-15'),
(32, 2, 'P05', 30.00, '2024-09-16'),
(33, 3, 'P06', 20.00, '2024-09-16'),
(34, 4, 'P07', 12.00, '2024-09-17'),
(35, 5, 'P08', 42.00, '2024-09-17'),
(36, 6, 'P09', 105.00, '2024-09-18'),
(37, 7, 'P10', 9.00, '2024-09-18'),
(38, 8, 'P01', 13.00, '2024-09-19'),
(39, 9, 'P02', 9.00, '2024-09-19'),
(40, 10, 'P03', 24.00, '2024-09-20');Simple Example of CTE
Let’s identify all the sales that are higher than the average price across all products. For example, if the average sale amount is 30.55, we need to find all the sales that exceed this amount.
Approach one using sub-query
-- Step1: Find the sales amount
select
avg(Sales_Amount)
from
Sales_Transactions;
-- Step-2 (use sub-query)
select
*
from
Sales_Transactions
where
Sales_Amount > (select avg(Sales_Amount) from Sales_Transactions);Solve Same Using Common Table Expression (CTE)
-- to doMulti Step Problem
Identify all products sold to the rest of the world that are priced between the average prices of Japan and Australia**
- Step-1: Find the average sale price for Country=Japan
- Step-2: Find the average sale price for Country= Australia
- Final-Step: Find all the products that has sale-amount between Japan & Australia average saels value.
Solve It Using CTE
with stp0_customer_sales_tlb as
(
select
st.transaction_id,
st.transaction_date,
st.Sales_Amount,
c.Customer_Name,
c.country
from
Sales_Transactions st join
customers c on st.customer_id = c.customer_id
),
stp1_japan_avg as (
select
avg(sales_amount) as avg_sales
from stp0_customer_sales_tlb
where
country = 'Japan'
),
stp2_aus_avg as (
select
avg(sales_amount) as avg_sales
from stp0_customer_sales_tlb
where
country = 'Australia'
)
select
transaction_id,
transaction_date,
Sales_Amount,
Customer_Name,
country,
a.avg_sales as aus_avg_sales,
j.avg_sales as jap_avg_sales
from
sales_Transactions st, stp1_japan_avg j, stp2_aus_avg a
where
st.sales_amount between a.avg_sales and j.avg_sales;Solve Using SubQuery
select
st.transaction_id,
st.transaction_date,
st.sales_amount,
c.customer_name,
c.country,
(select avg(st1.sales_amount)
from sales_transactions st1
join customers c1 on st1.customer_id = c1.customer_id
where c1.country = 'Australia') as aus_avg_sales,
(select avg(st2.sales_amount)
from sales_transactions st2
join customers c2 on st2.customer_id = c2.customer_id
where c2.country = 'Japan') as jap_avg_sales
from
sales_transactions st
join
customers c on st.customer_id = c.customer_id
where
st.sales_amount between
(select avg(st1.sales_amount)
from sales_transactions st1
join customers c1 on st1.customer_id = c1.customer_id
where c1.country = 'Australia')
and
(select avg(st2.sales_amount)
from sales_transactions st2
join customers c2 on st2.customer_id = c2.customer_id
where c2.country = 'Japan');Business Requirement
Calculate Country-Level Product Sales Contribution to Total Product Sales
Objective:The business requires a report that shows the total sales of each product by country and calculates the percentage contribution of each product’s country-level sales to its overall global sales. This report will help identify key markets and evaluate regional performance for each product.
-- Final Query: Combine country-product sales and total product sales to calculate percentage contribution
with
CountryProductSales as (
-- First CTE: Calculate total sales by country and product
-- for that all 3 tables must be joined using FK.
select
c.country,
p.product_id,
p.product_name,
sum(s.sales_amount) as total_sales_by_country
from
sales_transactions s
join customers c on c.customer_id = s.customer_id
join products p on p.product_id = s.product_id
group by
c.country, p.product_id, p.product_name
order by
c.country, p.product_id, p.product_name
),
ProductSales as (
-- Second CTE: Calculate total sales by product
select
p.product_id,
p.product_name,
sum(s.sales_amount) as total_sales_by_product
from
sales_transactions s
join products p on p.product_id = s.product_id
group by
p.product_id, p.product_name
order by
p.product_id, p.product_name
)
select
cps.country,
cps.product_id,
cps.product_name,
cps.total_sales_by_country,
ps.total_sales_by_product,
round((cps.total_sales_by_country/ps.total_sales_by_product)*100,2) as percentage_contribution
from
CountryProductSales cps join
ProductSales ps on ps.product_id = cps.product_id
order by
cps.country, cps.product_id
