Scenario

You are a scientist running thermodynamics experiments, and you have two tables containing temperature and pressure readings indexed by experiment ID.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("temperatures") }} with {{ ref("pressures") }} on experiment_id using an INNER JOIN (only experiments present in both tables should appear)
2. Computes a column called result as temperature * pressure
3. Returns experiment_id and result, sorted by experiment_id in ascending order

Schema

temperatures

pressures

Example

temperatures:

pressures:

Expected Output:

Note: Experiments 2 and 4 exist only in temperatures, and experiment 5 exists only in pressures. All three are excluded because they are not present in both tables.

SELECT
    t.experiment_id,
    t.temperature * p.pressure AS result
FROM {{ ref("temperatures") }} t
INNER JOIN {{ ref("pressures") }} p
    ON t.experiment_id = p.experiment_id
ORDER BY t.experiment_id ASC

Explanation

Step 1: Joining the two tables

FROM {{ ref("temperatures") }} t
INNER JOIN {{ ref("pressures") }} p
    ON t.experiment_id = p.experiment_id

An INNER JOIN returns only the rows where experiment_id exists in both tables. Experiments that appear in only one table are automatically excluded. This is the default join type, so writing JOIN without the INNER keyword produces the same result.

Step 2: Computing the derived column

t.temperature * p.pressure AS result

The AS keyword creates an alias for the computed expression. Since both tables have an experiment_id column, you must qualify it with a table alias (e.g., t.experiment_id) to avoid ambiguity. The multiplication itself works on any numeric types.

Step 3: Sorting the output

ORDER BY t.experiment_id ASC

ORDER BY sorts the final result set. ASC is the default direction but including it makes intent explicit. You can also use the column position (ORDER BY 1) as a shorthand.

Step 4: Alternative with a CTE

WITH joined AS (
    SELECT
        t.experiment_id,
        t.temperature * p.pressure AS result
    FROM {{ ref("temperatures") }} t
    INNER JOIN {{ ref("pressures") }} p
        ON t.experiment_id = p.experiment_id
)
SELECT * FROM joined
ORDER BY experiment_id ASC

A CTE separates the join logic from the ordering step. For a simple query like this, the flat version and the CTE version produce identical execution plans. CTEs become more useful when chaining multiple transformation steps.