The interviewer asks: "The orders table is partitioned by day, yet a query for one day still reads every file. Why, and how do you fix it?" A partition is a folder that holds the rows of one value of a column. The starting code writes the 78 orders into stg_orders/ with PARTITION_BY (day): one folder per day, named like day=2026-05-04, where day is the day the order was placed. This column=value naming is called hive partitioning, and read_parquet('stg_orders/*/*.parquet', hive_partitioning = true) turns each folder name back into a day column.
Partition pruning means skipping folders: when the WHERE filters on the partition column itself, DuckDB picks the folders by their names and never opens the others. A filter on a column stored inside the files can't do that, because DuckDB has to open a file to see its values. An example on another table, events, partitioned by country:
events/country=DE/ events/country=FR/ events/country=RO/ WHERE country = 'FR' → opens 1 folder: country is in the folder names WHERE city = 'Lyon' → opens all 3: city is inside the files
The trap: most people say "it's partitioned by day, so a date filter is cheap". But slow_query filters on CAST(ordered_at AS DATE), and ordered_at sits inside the files, so it reads all 7 files for one day's answer. At work, a table holds years of days: a query that opens every file is slow, and on a cloud warehouse that bills by the data scanned, it is expensive too.
To count files, files_read(query) runs the query under EXPLAIN ANALYZE, which runs it and reports what each step of the plan really did, and returns the plan's Total Files Read. A file count stays the same on every run; a time doesn't. Your code and the check print:
slow_query files: 7 query files: 1 Same answer as slow_query: True status | orders cancelled | 2 paid | 7 (2 rows)
Your task: print how many files slow_query reads, then how many query reads, and give query a WHERE that keeps 2026-05-04 by filtering on the partition column day. Your program prints only the two file-count lines. After your program, the check prints the Same answer as slow_query line and the result table from query. Do not print those yourself.
Assignment
Follow the Coding Tutorial to measure the files both queries read and to make query read one folder instead of seven.
Hint
A filter can skip a folder only if it uses the column the folders are named by: day, not ordered_at. A date goes in quotes, like '2026-05-04'.
Free plan: 30 tutor messages. PRO is unlimited.
