14. Find Partitions That Need Compaction

Difficulty: Easy · Topics: Aggregations, Filtering & Selection

files lists the data files of a table: partition, path and size_mb.

A partition needs compaction when it has more than 3 files and their average size is under 32 MB. Return partition, file_count and avg_mb (rounded to 1 decimal) for those partitions, with the most files first (ties by partition name).

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

Sample data

files

partition	path	size_mb
2025-03-01	a	4
2025-03-01	b	6
2025-03-01	c	3
2025-03-01	d	5
2025-03-02	e	300
2025-03-02	f	280
2025-03-03	g	2
2025-03-03	h	2
2025-03-03	i	1
2025-03-03	j	2
2025-03-03	k	1

Expected output

partition	file_count	avg_mb
2025-03-03	5	1.6
2025-03-01	4	4.5

Hints

Hint 1

Group by partition and aggregate a count and an average.

Hint 2

Filter after the aggregation; that is SQL's HAVING.

Hint 3

Sort by file_count descending, then partition ascending.

Learn the concepts

• The small file problem · 6 min read. Why thousands of tiny files slow every read, how Spark jobs create them, and how to prevent and fix them.
• Compaction, OPTIMIZE and Z-order · 3 min read. Rewrite small files into large ones and cluster data so queries skip more of it.
• groupBy + agg · 3 min read. Aggregate rows per group: counts, sums, averages, and how count(*) differs from count(col).

PySpark functions you'll practise

• groupBy
• agg
• avg
• count
• filter
• orderBy

Related problems

• Three-Day Login Streak · Hard · Window Functions
• Find Hot Keys and Plan Salting · Hard · Joins
• Departments with Large Teams · Easy · Aggregations
• Word Count · Medium · Arrays
• How Much Does Each Query Read? · Medium · Joins

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