CTE(Common Table Expressions) especially useful when working with complex queries or when you need to reuse a subquery multiple times within a query. It provides provide a way to define temporary result sets that can be referenced within a single SQL statement.

Why do we need CTE ?

1. It helps to improve the readability and maintainability of SQL queries by allowing you to break down complex logic into smaller, more manageable parts.

2. It enable you to reuse the same subquery multiple times within a query without duplicating code.

Rules for using CTEs:

1. CTEs must be defined using the WITH keyword before the main SQL statement.

2. After "WITH," you give a name to each temporary table you're creating. Then, in parentheses, you write a SELECT statement to decide what data goes into that table.

3. You can make more than one temporary table in the same "WITH" section. Just separate them with commas.

4. After you've made your temporary tables, you can use them just like regular tables in the rest of your query.

Examples

1. Basic Example

   
    WITH SalesSummary AS (
    SELECT Region,
    SUM(SalesAmount) AS TotalSales
    FROM Sales 
    GROUP BY Region
    )
    SELECT Region, TotalSales
    FROM SalesSummary;
   

   In this example, we define a CTE named SalesSummary that calculates the total sales amount for each region. Then, we reference this CTE in the main query to retrieve the region-wise sales summary.

2. Customer Who Visited but Did Not Make Any Transactions (Leetcode Question)

   Introduction:

   You have two tables - Visits and Transactions, containing information about customers who visited a mall and their transactions. The goal is to find customers who visited the mall without making any transactions and the frequency of such visits.

   Approach:

   1. Find customers who visited the mall without making any transactions.

   2. Count the number of such visits for each customer

3.   WITH CustomerVisits AS (
         SELECT v.customer_id, v.visit_id
         FROM Visits v
         LEFT JOIN Transactions t ON v.visit_id = t.visit_id
         WHERE t.transaction_id IS NULL
     )
   

   In this step, we define a Common Table Expression (CTE) named CustomerVisits. This CTE retrieves data from the Visits table (v) and attempts to join it with the Transactions table (t) based on the visit_id column. The LEFT JOIN ensures that all rows from the Visits table are included in the result set, regardless of whether there are matching rows in the Transactions table. However, the WHERE clause filters the joined result set to only include rows where there is no corresponding transaction_id, indicating visits without transactions.

    SELECT customer_id, COUNT(visit_id) AS count_no_trans
    FROM CustomerVisits
    GROUP BY customer_id;
   

   this query tells the count of visits without transactions is calculated separately for each customer

   1.   WITH CustomerVisits AS (
            SELECT v.customer_id, v.visit_id
            FROM Visits v
            LEFT JOIN Transactions t ON v.visit_id = t.visit_id
            WHERE t.transaction_id IS NULL
        )
           SELECT customer_id, COUNT(visit_id) AS count_no_trans
           FROM CustomerVisits
           GROUP BY customer_id;