Aprende CTEs with Aggregate Functions | Advanced CTE Applications and Performance

Desliza para mostrar el menú

Understanding how to use aggregate functions within Common Table Expressions (CTEs) allows you to perform advanced analytics and reporting with clarity and efficiency. Aggregate functions such as SUM, COUNT, and AVG are powerful tools for summarizing large datasets, and when combined with CTEs, they let you break down complex queries into logical, manageable steps. This approach is especially useful when you need to analyze sales, customer activity, or inventory trends in a structured and readable way.


              12345678910111213141516171819202122
            
-- Calculate total sales per customer using SUM within a CTE
WITH customer_sales AS (
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        SUM(st.total_amount) AS total_sales
    FROM
        customers c
        JOIN sales_transactions st ON c.customer_id = st.customer_id
    GROUP BY
        c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_id,
    first_name,
    last_name,
    total_sales
FROM
    customer_sales
ORDER BY
    total_sales DESC;

Using a CTE for aggregation, as in the previous example, helps you organize your SQL code by separating each logical step. Instead of writing a long, nested query, you define a CTE to handle the aggregation, then reference the summarized data in your final SELECT statement. This makes your queries easier to read, debug, and maintain, especially when working with multiple levels of grouping or filtering.


              12345678910111213141516171819202122232425
            
-- Find departments (product categories) with above-average sales using a CTE
WITH category_sales AS (
    SELECT
        p.category,
        SUM(st.total_amount) AS total_category_sales
    FROM
        products p
        JOIN sales_transactions st ON p.product_id = st.product_id
    GROUP BY
        p.category
),
average_sales AS (
    SELECT AVG(total_category_sales) AS avg_sales
    FROM category_sales
)
SELECT
    cs.category,
    cs.total_category_sales
FROM
    category_sales cs,
    average_sales a
WHERE
    cs.total_category_sales > a.avg_sales
ORDER BY
    cs.total_category_sales DESC;

1. What is the benefit of using a CTE with aggregate functions?

2. Fill in the blanks to complete a CTE that calculates the average sales per product.

3. Which aggregate function would you use to count the number of orders per customer?

¿Todo estuvo claro?

¡Gracias por tus comentarios!

Sección 3. Capítulo 1

Pregunte a AI

Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla

Sección 3. Capítulo 1