48. How Much Does Each Query Read?
Difficulty: Medium · Topics: Joins, Aggregations, Null Handling, Filtering & Selection
partitions describes a date-partitioned table: partition_date and size_gb. queries lists filters run against it: query_id, start_date and end_date (inclusive).
With partition pruning, a query only reads partitions inside its date range. For every query return query_id, partitions_read, gb_read and pct_of_table (share of the table's total size, rounded to 1 decimal). Queries that match no partition read 0 partitions and 0 GB. Sort by query_id.
Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
partitions
| partition_date | size_gb |
|---|---|
| 2025-03-01 | 40 |
| 2025-03-02 | 35 |
| 2025-03-03 | 50 |
| 2025-03-04 | 45 |
| 2025-03-05 | 30 |
queries
| query_id | start_date | end_date |
|---|---|---|
| 1 | 2025-03-02 | 2025-03-03 |
| 2 | 2025-03-05 | 2025-03-05 |
| 3 | 2025-01-01 | 2025-12-31 |
| 4 | 2025-04-01 | 2025-04-30 |
Expected output
| query_id | partitions_read | gb_read | pct_of_table |
|---|---|---|---|
| 1 | 2 | 85 | 42.5 |
| 2 | 1 | 30 | 15 |
| 3 | 5 | 200 | 100 |
| 4 | 0 | 0 | 0 |
Hints
Hint 1
Join each query to the partitions in its range with a condition:partition_date.between(start_date, end_date). ISO date strings compare correctly.Hint 2
Use a left join so queries matching nothing are kept, then fill their counts with 0.Hint 3
The table total is one more aggregation, cross-joined in.Learn the concepts
- Partitioning done right · 4 min read. When to partition a table, how to choose the column, and how over-partitioning backfires.
- Dynamic partition pruning · 3 min read. Skip whole partitions of a fact table at runtime using a filter on the dimension it joins to.
- join · 4 min read. Combine DataFrames on a key: inner, left, right and full joins, and the duplicate-key fan-out trap.
PySpark functions you'll practise
- groupBy
- agg
- sum
- count
- join
- crossJoin
- coalesce
- select
- orderBy
- between
Related problems
- Monthly Retention by Cohort · Hard · Joins
- Fill Missing Dates (Calendar Spine) · Hard · Joins
- Find Hot Keys and Plan Salting · Hard · Joins
- Customers Who Bought Every Product · Hard · Joins
- Rebuild a Table from Its Transaction Log · 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