Top Three Vendors Per Department

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Three Vendors Per Department. Start with the requirements and the three tables on the right.

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

You need to log in / sign up to run or submit.

Problem

Write a query to find the top three most expensive vendors per department, handling ties appropriately.

Calculate each vendor's total invoice amount within each department. Include vendors with tied totals when they fall within the top three spending levels.

Output

One row per qualifying department and vendor
Columns: department_name, vendor_name, total_spend, and spend_rank
Sort by department name, rank ascending, then vendor name
Exclude records without a valid department or vendor

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

vendors

Column	Type	Description
vendor_idPK	INT	Unique vendor identifier
vendor_name	VARCHAR(150)	Vendor name

invoices

Column	Type	Description
invoice_idPK	INT	Unique invoice identifier
vendor_id	INT	Referenced vendor identifier
department_id	INT	Referenced department identifier
amount	DECIMAL(12,2)	Invoice amount

Tablesdepartmentsvendorsinvoices

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results