Scenario

You work in the aerospace industry and have two tables: one with equipment records and another with company details.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("equipment") }} with {{ ref("companies") }} on equipment.company_id = companies.id
2. Renames equipment.name to equipment_name, equipment.type to equipment_type, equipment.status to equipment_status, and companies.name to company_name
3. Derives a status_label column using the rules below
4. Returns seven columns: id, equipment_name, equipment_type, equipment_status, company_name, country, status_label

Status label rules:

Schema

equipment

companies

Example

equipment:

companies:

Expected Output:

Note: E1 and E4 are active with USA companies (Domestic Active). E2 is active with a non-USA company (Foreign Active). E3 and E5 have non-active statuses (Inactive regardless of country).

WITH joined AS (
    SELECT
        e.id,
        e.name    AS equipment_name,
        e.type    AS equipment_type,
        e.status  AS equipment_status,
        c.name    AS company_name,
        c.country,
        CASE
            WHEN e.status = 'active' AND c.country = 'USA' THEN 'Domestic Active'
            WHEN e.status = 'active'                        THEN 'Foreign Active'
            ELSE 'Inactive'
        END AS status_label
    FROM {{ ref("equipment") }} e
    INNER JOIN {{ ref("companies") }} c
        ON e.company_id = c.id
)
SELECT * FROM joined

Explanation

Step 1: Joining the two tables

FROM {{ ref("equipment") }} e
INNER JOIN {{ ref("companies") }} c
    ON e.company_id = c.id

An INNER JOIN connects each equipment record to its company using the foreign key company_id. Both tables have id and name columns, so table aliases (e and c) are essential to avoid ambiguity.

Step 2: Renaming columns with AS

e.name    AS equipment_name,
e.type    AS equipment_type,
e.status  AS equipment_status,
c.name    AS company_name

Since both tables share a name column, aliasing them to equipment_name and company_name makes the output schema clear. The AS keyword assigns a new column name in the result set.

Step 3: Deriving status_label with CASE WHEN

CASE
    WHEN e.status = 'active' AND c.country = 'USA' THEN 'Domestic Active'
    WHEN e.status = 'active'                        THEN 'Foreign Active'
    ELSE 'Inactive'
END AS status_label

CASE WHEN evaluates conditions top to bottom and returns the first matching result. The order matters here: the most specific condition (active + USA) must come before the broader one (active + any country). Any status other than active (such as inactive, retired, or maintenance) falls through to the ELSE branch.

Step 4: Wrapping in a CTE

The CTE (WITH joined AS ...) is optional for this query but keeps the logic organized. You could write the same query without a CTE by placing the SELECT and JOIN directly as the main statement. CTEs become more valuable when chaining multiple transformation steps.