Analyzing Data From a Spreadsheet | Dataford Interview Questions

How would you analyze data from a sample spreadsheet?

Assume the spreadsheet data has been loaded into the provided PostgreSQL tables. Write a query that summarizes January 2025 approved expenses by department and compares activity with each department's monthly budget.

Output

One row per department, including departments with no qualifying expenses.
Return department_name, transaction_count, total_expense, average_expense, monthly_budget, variance_amount, and budget_status.
Include only approved January expenses with non-NULL amounts. Sort by total expense descending, then department name ascending.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Department name
monthly_budget	NUMERIC(12,2)	Monthly expense budget

spreadsheet_expenses

Column	Type	Description
expense_idPK	INTEGER	Unique expense identifier
department_id	INTEGER	Department associated with the expense
expense_date	DATE	Date recorded in the spreadsheet
amount	NUMERIC(12,2)	Expense amount
expense_status	VARCHAR(40)	Approval status
expense_type	VARCHAR(60)	Expense classification

Your question is Analyzing Data From a Spreadsheet. 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