Google Financial Planning

Department Budget Analysis

Business Context

At Google, monitoring departmental salary budgets is essential for financial planning, resource allocation, and ensuring equitable compensation across the organization. This analysis calculates the total salary allocation for each department and identifies departments exceeding the company average, providing valuable insights for budget forecasting, headcount planning, and organizational design decisions. The results help finance teams and department leaders make data-driven decisions about hiring, compensation strategies, and resource distribution.

Technical Requirements

The primary objective is to develop a PostgreSQL query for the following:

Task: Calculate the total salary budget for each department and identify departments that exceed the average department budget.

Analytical Components:

Calculate the total salary for each department.
Determine the average salary budget across all departments.
Flag departments whose total budget exceeds this average budget with 'Yes', otherwise 'No'.
The output should include department name, its total budget, and the 'exceeds_average' flag.

Definitions:

"Total salary budget" for a department is the sum of all employee salaries within that department.
"Average budget" refers to the average of these calculated total departmental budgets.
A department "exceeds average" if its total budget is strictly greater than the overall average department budget.

Database Schema

The database consists of two tables: Employees and Departments.

-- Table: Departments
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL
);

-- Table: Employees
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department_id INT,
    salary INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

Sample Data

Assume the following sample data for the Employees and Departments tables (salary is INTEGER, hire_date is DATE type but not directly used in this specific problem's calculation):

Table: `Employees`

employee_id	name	department_id	salary
1	John Smith	1	60000
2	Jane Doe	1	70000
3	Bob Johnson	2	65000
4	Alice Brown	2	55000
5	Mike Wilson	2	60000
6	Sarah Garcia	3	80000
7	Tom Rodriguez	3	75000

Table: `Departments`

department_id	department_name
1	Engineering
2	Marketing
3	Finance

Expected Output (based on sample data):

Calculation Walkthrough:
- Engineering Total Budget: 60000 + 70000 = 130000
- Marketing Total Budget: 65000 + 55000 + 60000 = 180000
- Finance Total Budget: 80000 + 75000 = 155000
- Average Department Budget: (130000 + 180000 + 155000) / 3 = 465000 / 3 = 155000.00

department_name	total_budget	exceeds_average
Engineering	130000	No
Marketing	180000	Yes
Finance	155000	No

Note: Marketing's budget of 180,000 exceeds the average of 155,000. Finance's budget equals the average, so it does not "strictly exceed".

Department Budgets vs. Average

MODERATE

Write a PostgreSQL query to calculate the total salary budget for each department. Then, determine the average budget across all departments and identify which departments have a total budget strictly greater than this average. Display the department name, its total budget, and a flag ('Yes'/'No') indicating if it exceeds the average.

Solution (PostgreSQL)

PostgreSQL Query (using CTEs)

-- Calculate total salary budget for each department and identify those exceeding the average
WITH DepartmentBudgets AS (
    -- Step 1: Calculate total salary for each department
    SELECT
        d.department_id,
        d.department_name,
        SUM(e.salary) AS total_budget
    FROM
        Employees e
    JOIN
        Departments d ON e.department_id = d.department_id
    GROUP BY
        d.department_id,
        d.department_name
),
AverageBudget AS (
    -- Step 2: Calculate the average budget across all departments
    SELECT
        AVG(total_budget) AS avg_dept_budget
    FROM
        DepartmentBudgets
)
-- Step 3: Compare department budgets to the average and flag
SELECT
    db.department_name,
    db.total_budget,
    CASE
        WHEN db.total_budget > ab.avg_dept_budget THEN 'Yes'
        ELSE 'No'
    END AS exceeds_average
FROM
    DepartmentBudgets db,
    AverageBudget ab -- Cross join is fine here as AverageBudget has only one row
ORDER BY
    db.department_name;

Explanation of the PostgreSQL Query:

1. DepartmentBudgets CTE:

Purpose: Calculates the sum of salaries for each department.
SELECT d.department_id, d.department_name, SUM(e.salary) AS total_budget: Selects department identifiers and the sum of salaries.
FROM Employees e JOIN Departments d ON e.department_id = d.department_id: Joins employees with their respective departments.
GROUP BY d.department_id, d.department_name: Groups employees by department so SUM(e.salary) calculates the total for each one.

2. AverageBudget CTE:

Purpose: Calculates the overall average of all the departmental total budgets computed in the previous CTE.
SELECT AVG(total_budget) AS avg_dept_budget FROM DepartmentBudgets: Takes all the total_budget values from the DepartmentBudgets CTE and calculates their average. This CTE will produce a single row with a single column (avg_dept_budget).

3. Final SELECT Statement:

Purpose: Joins the individual department budgets with the overall average budget and determines if each department exceeds this average.
SELECT db.department_name, db.total_budget, ...: Selects the required output columns.
FROM DepartmentBudgets db, AverageBudget ab: This performs a CROSS JOIN (implicitly, since there's no ON clause and AverageBudget has one row). This effectively makes the single avg_dept_budget value available to compare against each department's budget from db.
CASE WHEN db.total_budget > ab.avg_dept_budget THEN 'Yes' ELSE 'No' END AS exceeds_average: This creates the flag. If a department's total_budget is strictly greater than the avg_dept_budget, it's flagged 'Yes'; otherwise, 'No'.
ORDER BY db.department_name: Orders the final result alphabetically by department name for consistent output.

Alternative with Window Function for Average: Instead of a separate AverageBudget CTE, you could calculate the average using a window function directly after calculating department budgets if you prefer fewer CTEs (though the CTE approach is often clearer for multi-step calculations).

WITH DepartmentBudgets AS (
    SELECT
        d.department_id,
        d.department_name,
        SUM(e.salary) AS total_budget
    FROM Employees e
    JOIN Departments d ON e.department_id = d.department_id
    GROUP BY d.department_id, d.department_name
)
SELECT
    department_name,
    total_budget,
    CASE
        WHEN total_budget > AVG(total_budget) OVER () THEN 'Yes'
        ELSE 'No'
    END AS exceeds_average
FROM DepartmentBudgets
ORDER BY department_name;

In this alternative, AVG(total_budget) OVER () calculates the average of total_budget across all rows produced by the DepartmentBudgets CTE, making it available on each row for direct comparison.

Back to SQL