Top Services by Region Using Window Functions | Dataford Interview Questions

Write a query to find the top three most-used cloud services by region, utilizing window functions on Google Cloud usage data. Treat usage_units as the usage measure and sum it for each service within each non-null region. Include only services that match the service catalog.

Output

One row per region and selected service, with region, service_name, total_usage, and usage_rank.
Include the three highest-ranked services per region, ordered by region, rank, and service name. Break usage ties alphabetically by service name.

Schema

cloud_services

Column	Type	Description
service_idPK	INT	Unique identifier for a Google Cloud service
service_name	VARCHAR(100)	Google Cloud service name

cloud_usage

Column	Type	Description
usage_idPK	INT	Unique usage record identifier
region	VARCHAR(50)	Google Cloud region where usage occurred
service_id	INT	Referenced Google Cloud service identifier
usage_units	BIGINT	Measured usage for the record

Your question is Top Services by Region Using Window Functions. 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