Example: Common Table Expression - Teradata Vantage

A common table expression (CTE) in a WITH modifier can reference either preceding or subsequent CTEs defined in the WITH modifier, if the CTE does not indirectly reference itself. That is, circular references are not allowed. CTEs are also called named queries.

Following is the table definition for this example.

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);

These statements insert rows of data into the table.

INSERT INTO orders (43563, 734.12);
INSERT INTO orders (65758, 211.15);
INSERT INTO orders (23235, 1264.98);
INSERT INTO orders (43563, 583.23);
INSERT INTO orders (89786, 278.66);
INSERT INTO orders (13253, 401.97);
INSERT INTO orders (98765, 1042.23);
INSERT INTO orders (23235, 699.23);
INSERT INTO orders (43563, 935.35);
INSERT INTO orders (88354, 375.09);

This example of a WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is previously defined in the WITH clause.

WITH multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
),
multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set:

This example of a WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is subsequently defined in the WITH clause.

WITH multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
),
multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set: