Skip to content
Quantoros

What is a CTE?

SQLLevel: IntermediateSubqueries & CTEs

What is a CTE?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A CTE (Common Table Expression) is a named temporary result set defined with WITH. It exists only for the duration of the query. It makes complex queries more readable by breaking them into named steps.

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
prev_month AS (
    SELECT
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY month) AS prev_sales
    FROM monthly_sales
)
SELECT
    month,
    total_sales,
    ROUND((total_sales - prev_sales) / prev_sales * 100, 2) AS growth_pct
FROM prev_month;

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions