Filter and classify data

TutorialApplies to: Unified engine deployments

Keep the rows you need and label them with clear rules.

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. Compare values

Which orders are worth at least 25?

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 FROM orders WHERE amount >= 25 ORDER BY id;

Expected result

id
2
4
5

WHERE keeps only rows whose condition is true. Change one thing: Use > 25. Expect orders 2 and 4.

2. Combine conditions

Which North orders exceed 15?

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 FROM orders WHERE region = 'North' AND amount > 15 ORDER BY id;

Expected result

id
1
4

AND requires both conditions. Change one thing: Use amount >= 15. Order 3 is also included.

3. Use parentheses

Which North orders or large South orders qualify?

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 FROM orders WHERE region = 'North' OR (region = 'South' AND amount >= 30) ORDER BY id;

Expected result

id
1
2
3
4
6

Parentheses make the intended rule clear. Change one thing: Use amount >= 25. All six orders qualify.

4. Select a range

Which values are between 15 and 25?

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 FROM orders WHERE amount BETWEEN 15 AND 25 ORDER BY id;

Expected result

id
1
3
5

BETWEEN includes both ends. Change one thing: Change 25 to 20. Expect orders 1 and 3.

5. Match lists and text

Which selected categories start with G?

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 FROM orders WHERE category IN ('Games', 'Garden') AND category LIKE 'G%' ORDER BY id;

Expected result

id
2
3
5
6

IN checks a list. LIKE uses % for any sequence of characters. Change one thing: Change G% to Ga%. The result stays the same.

6. Classify orders

Which orders are large?

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, CASE WHEN amount >= 30 THEN 'Large' ELSE 'Small' END AS size FROM orders ORDER BY id;

Expected result

id	size
1	Small
2	Large
3	Small
4	Large
5	Small
6	Small

CASE chooses the first matching branch. Change one thing: Set the threshold to 25. Order 5 becomes Large.

Continue

Continue to Handle missing values, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Filter and classify data

Before you start

1. Compare values

2. Combine conditions

3. Use parentheses

4. Select a range

5. Match lists and text

6. Classify orders

Continue

​Before you start

​1. Compare values

​2. Combine conditions

​3. Use parentheses

​4. Select a range

​5. Match lists and text

​6. Classify orders

​Continue

Before you start

1. Compare values

2. Combine conditions

3. Use parentheses

4. Select a range

5. Match lists and text

6. Classify orders

Continue