Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Spark Delta table containing customer order data. They need to find the top 5 customers by total order value. The table has `customer_id` and `order_value` columns. Which combination of Spark SQL clauses will achieve this?
- AGROUP BY customer_id, ORDER BY SUM(order_value) DESC, LIMIT 5
- BSELECT TOP 5 customer_id, SUM(order_value) FROM orders GROUP BY customer_id ORDER BY SUM(order_value) DESC
- CGROUP BY customer_id, HAVING SUM(order_value) DESC, LIMIT 5
- DWHERE customer_id IN (SELECT DISTINCT customer_id FROM orders LIMIT 5), SUM(order_value)
Show answer & explanationAnswer & explanation
Correct answer: A. GROUP BY customer_id, ORDER BY SUM(order_value) DESC, LIMIT 5
To find the top N values, the standard SQL approach (fully supported in Spark SQL) is to first group the data to calculate the aggregate (SUM), then order the results in descending order, and finally limit the output to the top N rows.
Why the other options are wrong
- B. `SELECT TOP 5` is Transact-SQL syntax, not standard Spark SQL. Spark SQL uses `LIMIT` for this purpose.
- C. `HAVING` is for filtering groups, not for ordering. The `ORDER BY` clause is missing to sort the aggregate value correctly before applying `LIMIT`.
- D. This approach is incorrect; `WHERE` is for filtering individual rows, and `LIMIT` within a subquery on `DISTINCT customer_id` would not guarantee the top 5 by *total order value*.
Spark SQL Top N Query
To find the top N records based on an aggregate value in Spark SQL, you typically group the data, calculate the aggregate, order the results in descending order of the aggregate, and then use the `LIMIT` clause to restrict the number of rows.
- Sequence: GROUP BY -> Aggregate -> ORDER BY DESC -> LIMIT N.
- `LIMIT` is standard in Spark SQL for top N.
- Crucial for ranking and identifying leading entities.
Memory trick: Group, Sum, Order, Limit to find the best.