A data scientist is performing exploratory data analysis on a large dataset of customer feedback stored in a Spark Delta table named `feedback_data` in Microsoft Fabric. They need to find the most frequent keywords mentioned in the feedback for each product. Which Spark SQL function, combined with windowing, is best suited to rank keywords by frequency within each product?
- ANTILE(1)
- BROW_NUMBER()
- CRANK()
- DDENSE_RANK()
Show answer & explanationAnswer & explanation
Correct answer: D. DENSE_RANK()
To find the 'most frequent' keywords, you first need to count their occurrences (frequency) within each product. Then, you need to rank these keywords. `DENSE_RANK()` is ideal because it assigns consecutive ranks to rows within each partition, and if multiple keywords have the same highest frequency, they will all receive the same rank (e.g., rank 1), without gaps in the ranking sequence, which is appropriate for identifying 'most frequent' (top-ranked) items.
Why the other options are wrong
- A. `NTILE(1)` would assign all rows to a single group (or tile), which is not useful for ranking within partitions. `NTILE(N)` divides rows into N groups.
- B. `ROW_NUMBER()` assigns a unique, consecutive rank to each row within a partition, even if values are identical. This would arbitrarily pick one keyword if frequencies are tied.
- C. `RANK()` assigns the same rank to rows with identical values, but it leaves gaps in the ranking sequence (e.g., 1, 1, 3). While it handles ties, `DENSE_RANK()` is often preferred for 'top N' scenarios where consecutive ranks are desired.
Spark SQL DENSE_RANK()
The `DENSE_RANK()` window function assigns a rank to each row within its partition, with identical values receiving the same rank, and subsequent ranks being consecutive (no gaps).
- Requires an `OVER` clause with `PARTITION BY` and `ORDER BY`.
- Assigns ranks starting from 1.
- Does not produce gaps in the ranking sequence when ties occur.
- Useful for identifying top N items where ties should share the same rank and the next rank should be immediately after.
Memory trick: Dense rank is for when you want shared gold, no skipping numbers!