Skip to content
All writing ‹ Part 09 of 09 · One Operation, Many Engines
Engineering · 4 min read

Cross-Engine Query Exercises: Test Yourself (With Answers)

Test yourself: identify the operation first, then write it in each engine. Worked ClickHouse, Spark, and MongoDB exercises with answers: counting NULLs, array membership, segmentation, and more.

Test yourself: read the scenario, figure out “which operation is this” first, then “how does this engine write it,” and only then reveal the answer. Example schema is analytics.events throughout. Fields vary by scenario.

ClickHouse

1. Count only the sign-ups that filled in an email (excluding NULL)

Scenario: events has a signup_email field, some of it NULL. Get both the total row count and the count with email.

Show answer 
SELECT count(*) AS total, count(signup_email) AS with_email
FROM analytics.events;

count(*) includes NULL, count(col) only counts non-NULL. The difference is the number of missing emails. ↳ count(*) vs count(col)

2. Find campaigns whose name contains “summer” (case-insensitive)

Scenario: search for the substring summer inside field_name.

Show answer 
SELECT * FROM analytics.events
WHERE field_name ILIKE '%summer%';

ILIKE = case-insensitive LIKE. % matches any characters. ↳ LIKE / ILIKE substring search

3. Filter rows where the tags array contains ‘vip’

Scenario: tags is Array(String), and you want to know whether vip is one of the elements.

Show answer 
SELECT * FROM analytics.events
WHERE has(tags, 'vip');

has(arr, val) is ClickHouse’s array membership check. ↳ ClickHouse array functions

4. Find requests where at least one latency exceeds 500ms

Scenario: latencies is a numeric array. You want it if any element is > 500.

Show answer 
SELECT request_id FROM analytics.events
WHERE arrayExists(x -> x > 500, latencies);

arrayExists is equivalent to Python’s any(). ↳ ClickHouse array functions

5. Segment users by spend and activity

Scenario: classify users into segments using layered conditions.

Filter fields: total_spent and days_active

  1. VIP_Active: total_spent >= 5000 & days_active <= 14
  2. VIP_At_Risk: total_spent >= 5000
  3. Loyal_Regular: total_spent >= 1000
  4. everything else falls through to Standard
Show answer 
SELECT user_id,
    multiIf(
        total_spent >= 5000 AND days_active <= 14, 'VIP_Active',
        total_spent >= 5000, 'VIP_At_Risk',
        total_spent >= 1000, 'Loyal_Regular',
        'Standard'
    ) AS segment
FROM analytics.events;

multiIf is a compact version of CASE WHEN. ↳ ClickHouse array functions

6. Default to unknown when email is NULL

Scenario: signup_email is partly NULL. Replace NULL with the string unknown on output.

Show answer 
SELECT ifNull(signup_email, 'unknown') AS email
FROM analytics.events;

ClickHouse uses ifNull to default a null (Spark uses NVL). ↳ ClickHouse array functions

Spark

1. Per-customer list of items bought (with duplicates vs. deduplicated)

Scenario: roll multiple rows of item_bought into an array → one column keeps duplicates, the other does not. So you get three columns: id, all_items, unique_items.

Show answer 
SELECT customer_id,
    collect_list(item_bought) AS all_items,
    collect_set(item_bought)  AS unique_items
FROM store_sales
GROUP BY customer_id;

collect_list keeps duplicates, collect_set de-dupes. ↳ Spark array functions and collect

2. Filter rows where tags contains ‘vip’

Show answer 
SELECT * FROM events
WHERE array_contains(tags, 'vip');

Spark’s array membership check is array_contains. ↳ Spark array functions and collect

3. Multiply local prices by an exchange rate

Scenario: prices is a numeric array, multiply each element by rate.

Show answer 
SELECT transform(prices, x -> x * rate) AS usd_prices
FROM orders;

transform(arr, x -> ...) applies an expression to every element. ↳ Spark array functions and collect

4. Keep only latencies over 500ms

Show answer 
SELECT filter(latencies, x -> x > 500) AS slow
FROM events;

WHERE filters whole rows, filter filters elements inside a single row’s array. Different levels: to keep “rows that have any latency > 500” use WHERE exists(latencies, x -> x > 500) (but the array stays intact). To drop the ≤ 500 elements inside the array, you need filter.

filter(arr, x -> ...) keeps elements matching a predicate. ↳ Spark array functions and collect

5. Default to ‘unknown’ when email is null

Show answer 
SELECT NVL(signup_email, 'unknown') AS email FROM events;

Spark uses NVL to default a null (ClickHouse uses ifNull). ↳ Spark array functions and collect

MongoDB

1. Age between 20 and 40 (exclusive)

Show answer 
db.users.find({ age: { $gt: 20, $lt: 40 } });

You can stack multiple comparison operators on the same field. ↳ MongoDB operators and vocabulary

2. name contains “abc” (case-insensitive): the LIKE equivalent

Show answer 
db.users.find({ name: { $regex: "abc", $options: "i" } });

$regex ≈ SQL LIKE, $options: "i" ≈ ILIKE. ↳ MongoDB operators and vocabulary

3. Documents where email is present

Show answer 
db.users.find({ email: { $exists: true } });

In Mongo, Absent ≠ null: $exists asks “does this key exist at all.” ↳ MongoDB operators and vocabulary

4. type is one of a / b / c: the IN equivalent

Show answer 
db.users.find({ type: { $in: ["a", "b", "c"] } });

$in ≈ SQL IN. ↳ MongoDB operators and vocabulary

5. Minors or VIPs

Show answer 
db.users.find({ $or: [{ age: { $lt: 18 } }, { vip: true }] });

$or / $and / $nor take an array. ↳ MongoDB operators and vocabulary

References

Related: back to the Cross-Engine DB Query overview. The deep dives behind these answers: count(*) vs count(col), LIKE / ILIKE, ClickHouse array functions, Spark array functions, and MongoDB operators.

Tags #query-engines #sql #databases
↑↓ navigate ↵ open esc close