Scenario

You work as a data analyst for a large construction company and have been given three tables containing project details, employee assignments, and equipment records.

Task

Write a Snowflake SQL query that:

1. Starts from {{ ref("projects") }} as the base table and uses LEFT JOIN to bring in data from {{ ref("employees") }} and {{ ref("equipment") }} on project_id
2. Calculates duration_days as the number of days between start_date and end_date using DATEDIFF
3. Calculates total_employees as the count of employees per project
4. Calculates unique_roles as the count of distinct roles per project
5. Calculates total_equipment_cost as the sum of equipment costs per project
6. Returns columns: project_id, project_name, start_date, end_date, duration_days, total_employees, unique_roles, total_equipment_cost
7. Uses COALESCE to return 0 instead of NULL for projects with no employees or equipment

Schema

projects

employees

equipment

Example

projects:

employees:

equipment:

Expected Output:

Note: Project 4 (Park Pavilion) has no employees and no equipment, so total_employees, unique_roles, and total_equipment_cost are all 0 (not NULL). Project 5 (Airport Terminal) has equipment but no employees. Project 3 has two Engineers, so unique_roles is 2 (not 3).

WITH employee_agg AS (
    SELECT
        project_id,
        COUNT(employee_id) AS total_employees,
        COUNT(DISTINCT role) AS unique_roles
    FROM {{ ref("employees") }}
    GROUP BY project_id
),
equipment_agg AS (
    SELECT
        project_id,
        SUM(cost) AS total_equipment_cost
    FROM {{ ref("equipment") }}
    GROUP BY project_id
)
SELECT
    p.project_id,
    p.project_name,
    p.start_date,
    p.end_date,
    DATEDIFF('day', p.start_date, p.end_date) AS duration_days,
    COALESCE(e.total_employees, 0) AS total_employees,
    COALESCE(e.unique_roles, 0) AS unique_roles,
    COALESCE(eq.total_equipment_cost, 0) AS total_equipment_cost
FROM {{ ref("projects") }} p
LEFT JOIN employee_agg e
    ON p.project_id = e.project_id
LEFT JOIN equipment_agg eq
    ON p.project_id = eq.project_id

Explanation

Step 1: Why CTEs instead of a direct three-way join

When you LEFT JOIN both employees and equipment directly to projects, each project row gets multiplied by the number of matching employees AND the number of matching equipment records. A project with 3 employees and 2 equipment items produces 6 rows, inflating COUNT(employee_id) from 3 to 6. Pre-aggregating each child table in a CTE eliminates this row multiplication problem.

Step 2: Aggregating employees

WITH employee_agg AS (
    SELECT
        project_id,
        COUNT(employee_id) AS total_employees,
        COUNT(DISTINCT role) AS unique_roles
    FROM {{ ref("employees") }}
    GROUP BY project_id
)

COUNT(employee_id) counts every employee assigned to each project. COUNT(DISTINCT role) counts how many different roles appear, so two Engineers on the same project count as one unique role.

Step 3: Aggregating equipment

equipment_agg AS (
    SELECT
        project_id,
        SUM(cost) AS total_equipment_cost
    FROM {{ ref("equipment") }}
    GROUP BY project_id
)

SUM(cost) totals all equipment costs per project. This CTE produces one row per project, ready to join without duplication.

Step 4: Calculating duration with DATEDIFF

DATEDIFF('day', p.start_date, p.end_date) AS duration_days

Snowflake's DATEDIFF function takes a date part ('day'), a start date, and an end date. It returns the integer number of day boundaries crossed between the two dates.

Step 5: Handling NULLs with COALESCE

COALESCE(e.total_employees, 0) AS total_employees,
COALESCE(e.unique_roles, 0) AS unique_roles,
COALESCE(eq.total_equipment_cost, 0) AS total_equipment_cost

A LEFT JOIN produces NULL when there is no matching row in the right table. COALESCE replaces NULL with 0 so that projects with no employees or equipment show 0 rather than NULL.