Databricks-Spark-Assoc Using Spark SQL Practice Question
A developer writes a Spark SQL query that groups orders by region and computes the total revenue per region, but also needs to return the number of distinct customers per region in the same result set. Which TWO expressions correctly compute the distinct customer count per region in a single GROUP BY region query? (Choose two.)
⚠ Common exam trap
The trap here is treating COUNT with a FILTER clause as equivalent to a distinct count, when filtering nulls does not remove duplicate customer_id values within a region.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
COUNT(DISTINCT customer_id)
To return a distinct customer count per region within a single GROUP BY region query, the developer can use COUNT(DISTINCT customer_id), which gives an exact deduplicated count per group, or APPROX_COUNT_DISTINCT(customer_id), which returns an approximate count using a sketch and is preferable when cardinality is high. Both are valid aggregates that operate per group and produce one value per region.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
COUNT(customer_id) FILTER (WHERE customer_id IS NOT NULL)
Why it's wrong here
This expression counts all non-null customer_id rows per region, including duplicates. It does not deduplicate customers who appear in multiple orders, so a region with repeated purchases by the same customer is overcounted. The FILTER clause only removes nulls; it does not make the count distinct, so it fails the requirement of counting unique customers.
- ✓
COUNT(DISTINCT customer_id)
Why this is correct
COUNT(DISTINCT customer_id) is a native aggregate that counts unique non-null customer_id values within each region group. Spark SQL supports this directly inside a GROUP BY region query, so it returns the distinct customer count per region without a subquery. It handles nulls by ignoring them, which matches typical distinct-count semantics in SQL.
- ✗
SUM(DISTINCT customer_id)
Why it's wrong here
SUM(DISTINCT customer_id) adds up unique customer_id values rather than counting them, producing a numeric total that has no meaning for a count of customers. It is also only valid for numeric columns and would fail or mislead if customer_id were a string. It does not answer how many distinct customers exist per region.
- ✗
COLLECT_SET(customer_id)
Why it's wrong here
COLLECT_SET returns an array of distinct customer_id values per region, not a count. While the array size could be computed with SIZE, COLLECT_SET alone yields a collection that is expensive to materialize for high-cardinality groups and is not a count expression. It does not directly produce the numeric distinct customer count required in the result set.
- ✓
APPROX_COUNT_DISTINCT(customer_id)
Why this is correct
APPROX_COUNT_DISTINCT uses a HyperLogLog-style sketch to estimate the number of distinct customer_id values per region. It is a valid aggregate in a GROUP BY query and returns an approximate count with a small, bounded relative error. It is preferable when the distinct cardinality is very large and exact counting would be expensive, while still producing one value per region.
About these practice questions
One of 295 original Databricks-Spark-Assoc practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.