Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Spark Delta table `customer_activity` in Microsoft Fabric. They need to count the number of unique customers who performed an 'add_to_cart' action within the last 7 days. Which Spark SQL query should they use?
- ASELECT COUNT(DISTINCT customer_id) FROM customer_activity WHERE action = 'add_to_cart' AND activity_date >= DATE_SUB(CURRENT_DATE(), 7);
- BSELECT DISTINCT customer_id FROM customer_activity WHERE action = 'add_to_cart' AND activity_date > CURRENT_DATE() - 7;
- CSELECT SUM(CASE WHEN action = 'add_to_cart' THEN 1 ELSE 0 END) FROM customer_activity WHERE activity_date >= DATE_SUB(CURRENT_DATE(), 7) GROUP BY customer_id HAVING COUNT(DISTINCT customer_id) > 0;
- DSELECT COUNT(customer_id) FROM customer_activity WHERE action = 'add_to_cart' AND activity_date BETWEEN CURRENT_DATE() - INTERVAL 7 DAY AND CURRENT_DATE();
Show answer & explanationAnswer & explanation
Correct answer: A. SELECT COUNT(DISTINCT customer_id) FROM customer_activity WHERE action = 'add_to_cart' AND activity_date >= DATE_SUB(CURRENT_DATE(), 7);
To count *unique* customers, `COUNT(DISTINCT customer_id)` is the correct aggregate function. The `WHERE` clause accurately filters for the specific action (`action = 'add_to_cart'`) and the time period (`activity_date >= DATE_SUB(CURRENT_DATE(), 7)`), which returns dates within the last 7 days including today.
Why the other options are wrong
- B. This query only returns the `DISTINCT customer_id` list, it does not *count* them. The date filter `activity_date > CURRENT_DATE() - 7` might exclude today's activities if `activity_date` has a time component and `CURRENT_DATE()` is only date.
- C. This query is overly complex and incorrect. `SUM(CASE WHEN ...)` counts all 'add_to_cart' actions, not unique customers, and the `HAVING` clause logic is redundant and misapplied for the unique customer count.
- D. `COUNT(customer_id)` would count all matching instances, not just unique customers. While `BETWEEN CURRENT_DATE() - INTERVAL 7 DAY AND CURRENT_DATE()` is a valid date range, the aggregate function is wrong.
Spark SQL COUNT(DISTINCT)
An aggregate function in Spark SQL that counts the number of unique, non-NULL values in a specified column within a group or the entire result set.
- Counts only unique values.
- Ignores NULL values by default.
- Often used with `GROUP BY` or on the entire result set.
- Essential for measuring unique entities (e.g., unique users, distinct products).
Memory trick: Count Distinct, Filtered by Action and Recent Date.