For each department, calculate the total actual spending, budgeted amount, and variance (actual - budget). Show only departments where actual spending exceeds budget by more than 10%.
This financial analysis problem is typical of Oracle's enterprise resource planning (ERP) systems. Budget variance analysis is fundamental to financial management, helping organizations identify overspending and take corrective action. This question tests your ability to perform calculations across related tables, apply percentage-based filtering, and present financial metrics clearly.
Concepts tested: JOIN operations between departments and expenses, SUM() aggregation for totals, calculated columns for variance, percentage calculations, and HAVING clause with complex conditions. Understanding when to use HAVING vs WHERE is critical—variance can only be calculated after aggregation.
Oracle's ERP customers use similar queries to: generate monthly budget variance reports for CFOs, identify departments requiring budget adjustments, trigger approval workflows when spending thresholds are exceeded, calculate year-over-year spending trends, power financial dashboards showing real-time budget health, and feed predictive models for budget forecasting.
When tackling this Oracle problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT
d.name AS department,
SUM(e.amount) AS actual,
d.budget,
SUM(e.amount) - d.budget AS variance
FROM departments d
JOIN expenses e ON d.id = e.department_id
GROUP BY d.id, d.name, d.budget
HAVING SUM(e.amount) > d.budget * 1.1
ORDER BY variance DESC;This solution joins departments to expenses and aggregates spending per department in a single GROUP BY query. HAVING filters groups post-aggregation, keeping only departments where total spending exceeds 110% of budget. Including d.budget in GROUP BY is required since it is a non-aggregated column in the SELECT list, and it is functionally dependent on d.id (the primary key) making this valid. The INNER JOIN means departments with zero expenses are excluded — a useful implicit behavior. This is the most readable and universally compatible approach.
WITH dept_spending AS (
SELECT department_id, SUM(amount) AS actual
FROM expenses
GROUP BY department_id
)
SELECT
d.name AS department,
ds.actual,
d.budget,
ds.actual - d.budget AS variance
FROM departments d
JOIN dept_spending ds ON d.id = ds.department_id
WHERE ds.actual > d.budget * 1.1
ORDER BY variance DESC;Pre-aggregating expenses in a CTE separates concerns: first reduce expenses to one row per department, then join to departments for names and budgets. The filter moves to WHERE since actual is now a plain column. This pattern is preferred when departments has additional filters (e.g., region) or when the query is extended with more joins — keeping aggregation isolated avoids complex GROUP BY lists. On large expenses tables the optimizer can apply the aggregation before the join, reducing data volume earlier in the plan.
Both solutions require scanning the expenses table and grouping by department_id. An index on expenses(department_id, amount) enables an index-only scan, avoiding heap lookups entirely. The departments table is typically small so a nested loop join is efficient. The HAVING condition in Solution 1 and WHERE in Solution 2 are evaluated after aggregation; neither can be pushed into the expenses scan because budget is only available post-join. At scale, if expenses has hundreds of millions of rows, pre-aggregating in a CTE (Solution 2) gives the optimizer a smaller intermediate dataset to join against departments, which can reduce sort-merge or hash-join memory requirements. Partial indexes on high-spending departments or materialized views of monthly spending are common production optimizations for budget dashboards.
Using WHERE instead of HAVING for the budget threshold is the most common mistake: WHERE runs before GROUP BY so SUM(e.amount) is unavailable, causing a syntax error. Forgetting d.budget in the GROUP BY clause (when not using d.id as a functional dependency anchor) causes an error in strict SQL modes. Using LEFT JOIN instead of INNER JOIN includes departments with no expenses, making SUM(e.amount) return NULL — which is never > budget * 1.1, so those rows are silently dropped, but NULL arithmetic can cause unexpected behavior. Candidates sometimes compare variance > budget * 0.1 instead of actual > budget * 1.1, which is equivalent but easier to misread. Also watch for departments with negative budget values.
Remove the HAVING clause and add a computed column: CASE WHEN SUM(e.amount) > d.budget * 1.1 THEN 'Over Budget' ELSE 'Within Budget' END AS status. Switch to a LEFT JOIN so departments with no expenses appear with actual = 0. COALESCE(SUM(e.amount), 0) handles NULLs from the left join correctly.
SQL requires all non-aggregated SELECT columns to appear in GROUP BY. Since d.budget is not an aggregate, it must be listed. Because budget is functionally dependent on d.id (primary key), the optimizer may not require it in strict mode, but including it is portable and explicit. Some databases like PostgreSQL allow omitting it when grouping by the PK.
Switch to a LEFT JOIN and use COALESCE(SUM(e.amount), 0) AS actual. Departments with no matching expenses get actual = 0. If budget > 0, variance would be 0 - budget (negative), and they would never appear in the over-budget filter — which is correct business logic. This ensures no department is accidentally omitted from a full audit report.