With CTE

 

I've been solving CodeKata questions and have already completed up to question number 76. While solving these questions, I wanted to practice using WITH CTE. Mastering WITH CTE seems like it will allow me to simplify complex code in various ways.

 

---

 What is a CTE?

A CTE (Common Table Expression) is a temporary result set defined within the execution scope of a single SQL statement. It improves the readability and organization of complex queries. CTEs are defined using the `WITH` keyword and can be referenced in the main query that follows.

 

Basic Structure of a CTE

Here is the basic structure of a CTE:

WITH CTE_Name AS (
    -- CTE definition: a single SELECT statement
    SELECT columns
    FROM table
    WHERE conditions
)
-- Main query that uses the CTE
SELECT columns
FROM CTE_Name
WHERE conditions;

 

Advantages of CTEs

1. Improved Readability: By breaking down complex queries into smaller, manageable parts, CTEs make SQL code easier to read and understand.
2. Reusability: CTEs can be referenced multiple times within the same query, avoiding redundancy.
3. Recursive Queries: CTEs can be used to write recursive queries, which are helpful for hierarchical or tree-structured data.

Example Explanation

 

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

 

In the provided example, a CTE is used to calculate users who joined in 2021 and this result set is then used in the main query.

 

 

-- CTE definition
WITH
User AS (
    -- Users who joined in 2021
    SELECT 
        USER_ID,
        -- Calculate the total number of users who joined in 2021
        (SELECT COUNT(DISTINCT USER_ID) 
         FROM USER_INFO 
         WHERE YEAR(JOINED) = 2021) as Total_User
    FROM 
        USER_INFO
    WHERE 
        joined LIKE '2021%'
)
-- Main query
SELECT  
    DATE_FORMAT(os.sales_date, '%Y') as 'YEAR',  -- Convert YEAR to string format
    MONTH(os.sales_date) as 'MONTH',             -- Keep MONTH as number
    COUNT(*) as 'PURCHASED_USERS',
    ROUND(COUNT(*) / Total_User, 1) as 'PURCHASED_RATIO'
FROM 
    User U 
    INNER JOIN ONLINE_SALE os 
    ON u.USER_ID = os.USER_ID
GROUP BY 
    YEAR, MONTH
ORDER BY 
    YEAR, MONTH;

---

Explanation:

1. CTE Definition (`User` CTE):

    - Selects users who joined in 2021.
    - Uses a subquery to calculate the total number of distinct users who joined in 2021 (`Total_User`).
    - Filters records where the `JOINED` field starts with '2021%'.
    - This result set is named `User`.

 

2. Main Query:

    - Retrieves sales data from the `ONLINE_SALE` table.
    - Joins the `User` CTE with the `ONLINE_SALE` table on `USER_ID`.
    - Converts the year part of `sales_date` to a string format and keeps the month part as a number.
    - Counts the number of users who made purchases (`PURCHASED_USERS`).
    - Calculates the purchase ratio (`PURCHASED_RATIO`) by dividing the number of purchased users by the total number of users, rounding to one decimal place.
    - Groups the results by year and month and orders them accordingly.

 

This example demonstrates how a CTE can be used to simplify complex SQL queries by breaking them into more manageable parts, while also reusing calculated results in the main query.