Build queries in steps

TutorialApplies to: Unified engine deployments

Use named steps and subqueries to make an analysis easier to read.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Chain named steps

What are the category totals for North?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
),
north AS (SELECT * FROM orders WHERE region = 'North'),
totals AS (SELECT category, SUM(amount) AS sales FROM north GROUP BY category)
SELECT * FROM totals ORDER BY category;

Expected result

category	sales
Books	60
Games	15
Garden	10

Later CTEs can read earlier CTEs. A CTE is a common table expression: a named query step. Change one thing: Change North to South. Expect Games 30 and Garden 25.

2. Reuse a named step

Can one query compare region totals?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
),
totals AS (SELECT region, SUM(amount) AS sales FROM orders GROUP BY region)
SELECT n.sales AS north_sales, s.sales AS south_sales FROM totals n CROSS JOIN totals s WHERE n.region = 'North' AND s.region = 'South';

Expected result

north_sales	south_sales
85	55

The same CTE can appear more than once with different aliases. Change one thing: Swap the region filters. Expect 55 and 85.

3. Use a derived table

Which categories have at least 45 sales?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT category, sales FROM (SELECT category, SUM(amount) AS sales FROM orders GROUP BY category) t WHERE sales >= 45 ORDER BY category;

Expected result

category	sales
Books	60
Games	45

A query inside FROM is a derived table. Give it an alias. Change one thing: Use sales >= 60. Only Books remains.

4. Use a scalar subquery

Which orders equal the largest amount?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT id, amount FROM orders WHERE amount = (SELECT MAX(amount) FROM orders) ORDER BY id;

Expected result

id	amount
4	40

This subquery supplies one value. Change one thing: Replace MAX with MIN. Expect order 6 with amount 10.

5. Test existence

Which customers have any sales?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM sales s WHERE s.customer_id = c.id) ORDER BY name;

Expected result

name
Ada
Ben

EXISTS checks whether the inner query returns any rows. Change one thing: Use NOT EXISTS. Only Cy remains.

6. Filter with a subquery

Which orders belong to known customers?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT order_id FROM sales WHERE customer_id IN (SELECT id FROM customers) ORDER BY order_id;

Expected result

order_id
10
11
12

IN compares against values returned by a query. Change one thing: Limit the inner query with WHERE id = 1. Expect orders 10 and 11.

Continue

Continue to Compare lists, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Build queries in steps

Before you start

1. Chain named steps

2. Reuse a named step

3. Use a derived table

4. Use a scalar subquery

5. Test existence

6. Filter with a subquery

Continue

​Before you start

​1. Chain named steps

​2. Reuse a named step

​3. Use a derived table

​4. Use a scalar subquery

​5. Test existence

​6. Filter with a subquery

​Continue

Before you start

1. Chain named steps

2. Reuse a named step

3. Use a derived table

4. Use a scalar subquery

5. Test existence

6. Filter with a subquery

Continue