Top Partitions by Resource Use | Dataford Interview Questions

Write a query to identify top-performing partitions based on resource consumption.

Use the partition catalog and resource usage data. Treat higher total credits consumed as better performance for this task. Return the highest-ranked partitions, including ties at the third rank.

Output

One row per qualifying partition with partition_id, table_name, total_credits_used, total_bytes_scanned, and resource_rank
Include partitions ranked 1 through 3, including ties
Order by resource_rank, descending total_credits_used, then ascending partition_id

Schema

partition_catalog

Column	Type	Description
partition_idPK	INT	Unique partition identifier
database_name	VARCHAR(100)	Snowflake database containing the table
schema_name	VARCHAR(100)	Snowflake schema containing the table
table_name	VARCHAR(100)	Table containing the partition
partition_label	VARCHAR(100)	Human-readable partition label

partition_resource_usage

Column	Type	Description
usage_idPK	INT	Unique resource usage record
partition_id	INT	Referenced partition identifier
query_id	VARCHAR(50)	Query associated with the usage record
credits_used	DECIMAL(12,2)	Credits consumed by the query
bytes_scanned	BIGINT	Bytes scanned for the partition
execution_ms	INT	Query execution time in milliseconds

Your question is Top Partitions by Resource Use. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem