Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Easy
A data analyst needs to count the number of unique customers from a `customer_orders` table in a Fabric Lakehouse SQL endpoint. The table has a `customer_id` column. Which SQL aggregate function should they use?
- ACOUNT(customer_id)
- BCOUNT(DISTINCT customer_id)
- CCOUNT(*)
- DSUM(customer_id)
Show answer & explanationAnswer & explanation
Correct answer: B. COUNT(DISTINCT customer_id)
The `COUNT(DISTINCT column_name)` aggregate function is used to count only the unique, non-null values in a specified column. This is exactly what's needed to find the number of unique customers.
Why the other options are wrong
- A. `COUNT(customer_id)` counts all non-null values in the `customer_id` column, but it will include duplicates.
- C. `COUNT(*)` counts all rows, including duplicates and rows with NULLs in other columns.
- D. `SUM(customer_id)` would attempt to sum the customer IDs, which is not what's required for counting unique customers.
SQL COUNT DISTINCT
The `COUNT(DISTINCT column_name)` aggregate function in SQL returns the number of unique, non-null values in a specified column.
- Counts only unique values.
- Ignores NULL values.
- Essential for unique item counts (e.g., unique users, products).
Memory trick: Count all, count not null, count distinct.