Cross-Engine Query Exercises (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-insensitiveLIKE;%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);
arrayExistsis equivalent to Python’sany(). ↳ 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
VIP_Active: total_spent >= 5000 & days_active <= 14VIP_At_Risk: total_spent >= 5000Loyal_Regular: total_spent >= 1000- 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;
multiIfis a compact version ofCASE 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
ifNullto default a null (Spark usesNVL). ↳ 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 doesn’t. 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_listkeeps duplicates,collect_setde-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;
WHEREfilters whole rows,filterfilters elements inside a single row’s array — different levels: to keep “rows that have any latency > 500” useWHERE exists(latencies, x -> x > 500)(but the array stays intact); to drop the ≤ 500 elements inside the array, you needfilter.
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
NVLto default a null (ClickHouse usesifNull). ↳ 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≈ SQLLIKE,$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:
$existsasks “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≈ SQLIN. ↳ MongoDB operators and vocabulary
5. Minors or VIPs
Show answer
db.users.find({ $or: [{ age: { $lt: 18 } }, { vip: true }] });
$or/$and/$nortake an array. ↳ MongoDB operators and vocabulary
References
- ClickHouse SQL Reference — https://clickhouse.com/docs/en/sql-reference
- Spark SQL Built-in Functions — https://spark.apache.org/docs/latest/api/sql/
- MongoDB Query Operators — https://www.mongodb.com/docs/manual/reference/operator/query/
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.