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.