67. Find Hot Keys and Plan Salting

Difficulty: Hard · Topics: Joins, Aggregations, Filtering & Selection

Before a big join, you want to know which values of key in events are skewed. A key is hot when its row count is more than 3 times the median row count per key, where the median is percentile_approx(count, 0.5) over all keys.

For each hot key return key, rows and salt_buckets = the row count divided by the median, rounded up. Sort by rows descending, then key.

Row order matters for this problem. Your code is graded on 4 test cases, including hidden edge cases.

Sample data

events

event_id	key
1	a
2	a
3	b
4	b
5	c
6	c
7	d
8	hot
9	hot
10	hot
11	hot
12	hot
13	hot
14	hot

Expected output

key	rows	salt_buckets
hot	7	4

Hints

Hint 1

First count rows per key.

Hint 2

Compute the median of those counts in a second aggregation, then bring it to every key with a cross join.

Hint 3

F.ceil(F.col("rows") / F.col("median")) rounds up.

Learn the concepts

Data skew and salting · 5 min read. Why one task runs for an hour while the rest finish in seconds, and how salting spreads a hot key.
AQE skew-join handling · 3 min read. How Spark detects oversized partitions in sort-merge joins and splits them automatically.
Adaptive Query Execution · 4 min read. Spark re-plans a running query from real statistics: coalescing, join switching and skew splitting.

PySpark functions you'll practise

groupBy
agg
count
median
percentile_approx
crossJoin
filter
select
orderBy

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems