Scenario

You have a dataset of architectural structures with their heights and floor counts, and you need to compute a per-floor height metric.

Task

Write a Snowflake SQL query that:

1. Selects id, name, city, country from {{ ref("buildings") }}
2. Calculates avg_height_per_floor as height_m / floors, rounded to 2 decimal places
3. Returns 0 for avg_height_per_floor when floors is zero (to avoid division by zero)

Schema

buildings

Example

buildings:

Expected Output:

Note: Central Hub has 0 floors, so avg_height_per_floor is set to 0 instead of causing a division error. All other values are height_m / floors rounded to 2 decimal places.

SELECT
    id,
    name,
    city,
    country,
    CASE
        WHEN floors != 0
        THEN ROUND(height_m / floors, 2)
        ELSE 0
    END AS avg_height_per_floor
FROM {{ ref("buildings") }}

Explanation

Step 1: Safe division with CASE

CASE
    WHEN floors != 0
    THEN ROUND(height_m / floors, 2)
    ELSE 0
END AS avg_height_per_floor

Dividing by zero in SQL produces an error, so the CASE expression checks whether floors is non-zero before performing the division. When floors is zero, the result is set to 0 instead. This is a common defensive pattern for any computed ratio where the denominator could be zero.

Step 2: Rounding with ROUND

ROUND(height_m / floors, 2)

ROUND(value, 2) rounds the result to 2 decimal places. The rounding is applied to the division result as a whole, not to the numerator or denominator individually.

Step 3: Alternative using IFF

Snowflake provides the IFF function as a shorthand for simple two-branch conditionals:

SELECT
    id,
    name,
    city,
    country,
    IFF(floors != 0, ROUND(height_m / floors, 2), 0) AS avg_height_per_floor
FROM {{ ref("buildings") }}

IFF(condition, true_value, false_value) is equivalent to CASE WHEN condition THEN true_value ELSE false_value END. Both produce identical results; CASE is more portable across SQL dialects while IFF is more concise.