SQL Programming Resources on Telegram: “WITH customer_revenue AS ( SELECT customer

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY customer_id
)

SELECT
    SUM(revenue) AS total_revenue,
    COUNT(*) AS active_customers,
    SUM(revenue) / NULLIF(COUNT(*), 0) AS revenue_per_customer
FROM customer_revenue;

The CTE first creates: 1 row = 1 customer

Then the final query calculates KPIs from that customer-level dataset.

🧱 14. CTE for Multi-Step Analytics

Let's build a slightly more realistic analysis.

• Step 1 — Calculate customer sales

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

• Step 2 — Create customer segments

, segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

• Step 3 — Analyze segments

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

The complete query:

WITH customer_sales AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
),

segmented_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spending,
        CASE
            WHEN total_spending >= 100000 THEN 'VIP'
            WHEN total_spending >= 50000 THEN 'Premium'
            ELSE 'Standard'
        END AS segment
    FROM customer_sales
)

SELECT
    segment,
    COUNT(*) AS customers,
    SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;

This is a good example of structured analytical SQL.

🪟 15. CTE + Window Functions Preview

CTEs become especially powerful when combined with window functions.

For example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,
    RANK() OVER (
        ORDER BY total_spending DESC
    ) AS spending_rank
FROM customer_sales;

• The CTE creates the customer-level metric.

• The window function ranks the customers.

• This pattern is extremely common in analytics.

🔄 16. Recursive CTEs

There is another advanced type of CTE: Recursive CTE

It allows a query to repeatedly reference itself.

Common use cases include:

• organizational hierarchies

• employee-manager structures

• category trees

• folder structures

• graph-like relationships

• generating sequences

Example structure:

WITH RECURSIVE employee_tree AS (
    SELECT
        employee_id,
        employee_name,
        manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id
    FROM employees e
    JOIN employee_tree t
        ON e.manager_id = t.employee_id
)

SELECT *
FROM employee_tree;