SQL for Revenue by Department | Dataford Interview Questions

Write a SQL query to calculate monthly patient billing totals by department and rank the departments by revenue.

Use the departments and patient_billing tables. Include departments with billing records, rank departments independently within each month, and assign the same rank to tied totals.

Output

One row per month and department with billing activity.
Columns: billing_month, department_name, total_billing, and revenue_rank.
Sort by month, rank ascending, and department name ascending.

Schema

departments

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

patient_billing

Column	Type	Description
billing_idPK	INT	Unique patient billing record identifier
department_id	INT	Department responsible for the billing record
billing_date	DATE	Date the patient billing activity was recorded
billed_amount	NUMERIC(12,2)	Amount billed for the patient service

Your question is SQL for Revenue by Department. 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