Scenario

A wildlife research team maintains separate tables for animal records and the climate classification of each region.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("animals") }} with {{ ref("regions") }} on the region column
2. Groups the results by species and climate
3. Returns the species, climate, average age (rounded to 2 decimal places), average weight (rounded down to the nearest integer), and total count of animals for each group

Schema

animals

regions

Example

animals:

regions:

Expected Output:

Note: Each species belongs to a single region in this dataset, so each group contains one species paired with one climate. The average weight is rounded down to the nearest integer.

WITH joined AS (
    SELECT a.*, r.climate
    FROM {{ ref("animals") }} AS a
    INNER JOIN {{ ref("regions") }} AS r
        ON a.region = r.region
)
SELECT
    species,
    climate,
    ROUND(AVG(age), 2) AS avg_age,
    FLOOR(AVG(weight)) AS avg_weight,
    COUNT(*) AS total_animals
FROM joined
GROUP BY species, climate

Explanation

Step 1: Joining the tables

FROM {{ ref("animals") }} AS a
INNER JOIN {{ ref("regions") }} AS r
    ON a.region = r.region

An INNER JOIN matches each animal row to its corresponding region row based on the region column. Animals in regions that do not appear in the regions table (and vice versa) are excluded from the result.

Step 2: Grouping and aggregating

GROUP BY species, climate

GROUP BY collapses all rows that share the same species and climate into a single output row. Every non-aggregated column in the SELECT list must appear in the GROUP BY clause.

Step 3: Computing the aggregates

ROUND(AVG(age), 2) AS avg_age,
FLOOR(AVG(weight)) AS avg_weight,
COUNT(*) AS total_animals

ROUND(AVG(age), 2) computes the arithmetic mean within each group and rounds to two decimal places. FLOOR rounds the average weight down to the nearest integer (for example, 84.47 becomes 84). Note that CAST(... AS INTEGER) rounds to the nearest integer rather than always rounding down, so FLOOR is the correct choice here. COUNT(*) counts the number of rows in each group.

Step 4: Alternative without a CTE

You can write the same query without a CTE by placing the join directly in the FROM clause:

SELECT
    a.species,
    r.climate,
    ROUND(AVG(a.age), 2) AS avg_age,
    FLOOR(AVG(a.weight)) AS avg_weight,
    COUNT(*) AS total_animals
FROM {{ ref("animals") }} AS a
INNER JOIN {{ ref("regions") }} AS r
    ON a.region = r.region
GROUP BY a.species, r.climate

Both approaches produce identical results. The CTE version separates the join logic from the aggregation, which can improve readability for more complex queries.