Junior — Middle

Can you give an example of using a temporary table with a Common Table Expression (CTE) in SQL?

sobes.tech AI

Answer from AI

Common Table Expression (CTE) allows you to define a temporary named table that exists only within the scope of a single query. It is useful for simplifying complex queries and reusing subqueries.

Example of using a CTE to create a temporary table that selects users with more than 5 orders:

WITH FrequentCustomers AS (
    SELECT user_id, COUNT(*) AS orders_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > 5
)
SELECT u.user_id, u.name, fc.orders_count
FROM users u
JOIN FrequentCustomers fc ON u.user_id = fc.user_id;

Here, FrequentCustomers is a temporary table containing users with more than 5 orders. It is then used in the main query to retrieve additional information about the users.

Previous Next