Window Functions with CTE | Dataford Interview Questions

Write a SQL query using a CTE and a window function.

Asked in the Technical round 2 stage. Use the customers and orders tables.

Output

Return one row per customer with region, customer_id, customer_name, total_spend, and region_rank.
Include customers without orders with total_spend equal to 0.
Rank customers within each region by descending total spend. Customers with equal totals share the same rank.
Order by region, region_rank, and customer_id.

customers

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
amount	NUMERIC(12,2)	Order amount

Your question is Window Functions with CTE. 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.

Write a SQL query using a CTE and a window function.

Asked in the Technical round 2 stage. Use the customers and orders tables.

Return one row per customer with region, customer_id, customer_name, total_spend, and region_rank.
Include customers without orders with total_spend equal to 0.
Rank customers within each region by descending total spend. Customers with equal totals share the same rank.
Order by region, region_rank, and customer_id.

customers

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
amount	NUMERIC(12,2)	Order amount

Your question is Window Functions with CTE. 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.