Scenario

You work in the payroll department of a company and have two tables: one with employee details and another with hours worked and hourly rates.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("employees") }} with {{ ref("payroll") }} on employee_id
2. Calculates a pay column using these rules: if hours_worked <= 40, pay is hours_worked * hourly_rate; if hours_worked > 40, pay is (40 * hourly_rate) + ((hours_worked - 40) * hourly_rate * 1.5)
3. Returns only employee_id, name, position, and pay

Schema

employees

payroll

Example

employees:

payroll:

Expected Output:

Note: Sara and Lena worked under 40 hours (regular pay). Mia worked exactly 40 hours (regular pay). Tom and Raj worked over 40 hours, so their overtime hours are paid at 1.5x the hourly rate.

WITH calculated AS (
    SELECT
        e.employee_id,
        e.name,
        e.position,
        CASE
            WHEN p.hours_worked <= 40
                THEN p.hours_worked * p.hourly_rate
            ELSE
                (40 * p.hourly_rate) + ((p.hours_worked - 40) * p.hourly_rate * 1.5)
        END AS pay
    FROM {{ ref("employees") }} e
    INNER JOIN {{ ref("payroll") }} p
        ON e.employee_id = p.employee_id
)
SELECT * FROM calculated

Explanation

Step 1: Joining the Two Tables

FROM {{ ref("employees") }} e
INNER JOIN {{ ref("payroll") }} p
    ON e.employee_id = p.employee_id

An INNER JOIN combines rows from employees and payroll where employee_id matches in both tables. This gives access to employee details (name, position) alongside their hours and rate. Use table aliases (e, p) to keep column references concise and unambiguous.

Step 2: Conditional Pay Calculation with CASE WHEN

CASE
    WHEN p.hours_worked <= 40
        THEN p.hours_worked * p.hourly_rate
    ELSE
        (40 * p.hourly_rate) + ((p.hours_worked - 40) * p.hourly_rate * 1.5)
END AS pay

The CASE WHEN expression evaluates a condition for each row and returns a different value depending on the result. For regular hours (40 or fewer), pay is simply hours * rate. For overtime, the first 40 hours are paid at the normal rate and any additional hours are paid at 1.5x the rate. The AS pay alias names the computed column in the output.

Step 3: Selecting Only Required Columns

SELECT
    e.employee_id,
    e.name,
    e.position,
    CASE ... END AS pay

The query explicitly lists the four required output columns rather than using SELECT *. This excludes age from the employees table and hours_worked / hourly_rate from the payroll table. Wrapping the logic in a CTE and then selecting from it keeps the query organized, though for this case a flat query without a CTE would produce identical results.