Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data engineering team is working with a large dataset in a Spark Delta Lake table. They need to calculate the average `transaction_amount` for each `product_category` and then filter out categories where the average amount is less than $100. Which Spark SQL clause should be used to filter the grouped results?
- AGROUP BY
- BHAVING
- CFILTER
- DWHERE
Show answer & explanationAnswer & explanation
Correct answer: B. HAVING
In Spark SQL, similar to standard SQL, the HAVING clause is used to filter results after a GROUP BY clause has aggregated the data. It applies conditions to the grouped rows.
Why the other options are wrong
- A. GROUP BY is used to group rows that have the same values into summary rows, not to filter them.
- C. FILTER is a function often used with window functions or array operations, not directly for filtering grouped results.
- D. WHERE filters individual rows before grouping and aggregation.
Spark SQL HAVING Clause
The Spark SQL HAVING clause is used to filter the groups of rows returned by a GROUP BY clause, based on aggregate conditions.
- Applied after GROUP BY and aggregate functions.
- Filters groups, not individual rows.
- Often includes aggregate functions in its condition.
Memory trick: Group and then Have a condition.