Common Table Expression With Examples

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 do

Multi 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