> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Build queries in steps

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/compare-lists.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.