Scenario

You work for a mortgage company that tracks mortgage products and which product each user has selected.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("mortgage_details") }} with {{ ref("user_mortgages") }} on mortgage_id
2. Groups the joined data by mortgage_type
3. Calculates rate_of_mortgage as ROUND(SUM(interest_rate) / COUNT(user_id), 2) for each group
4. Returns mortgage_type and rate_of_mortgage

Schema

mortgage_details

user_mortgages

Example

mortgage_details:

user_mortgages:

Expected Output:

Note: Fixed has three joined rows (U1 and U2 on mortgage 101 at 3.75, U4 on mortgage 103 at 4.50), so the rate is (3.75 + 3.75 + 4.50) / 3 = 4.0. Variable and Adjustable each have one user, so the rate equals the original interest rate.

WITH aggregated AS (
    SELECT
        md.mortgage_type,
        ROUND(SUM(md.interest_rate) / COUNT(um.user_id), 2) AS rate_of_mortgage
    FROM {{ ref("mortgage_details") }} md
    INNER JOIN {{ ref("user_mortgages") }} um
        ON md.mortgage_id = um.mortgage_id
    GROUP BY md.mortgage_type
)
SELECT * FROM aggregated

Explanation

Step 1: Join the two tables

FROM {{ ref("mortgage_details") }} md
INNER JOIN {{ ref("user_mortgages") }} um
    ON md.mortgage_id = um.mortgage_id

The INNER JOIN matches each user to their selected mortgage product. If a mortgage has two users, the join produces two rows for that mortgage, each carrying the same interest_rate. This row duplication is what makes the SUM / COUNT formula work correctly.

Step 2: Aggregate by mortgage type

GROUP BY md.mortgage_type

Grouping by mortgage_type collapses all joined rows that share the same type. A single type can span multiple mortgage IDs (for example, two different Fixed products), so the aggregation combines them all.

Step 3: Calculate the rate

ROUND(SUM(md.interest_rate) / COUNT(um.user_id), 2) AS rate_of_mortgage

For each mortgage type, SUM(interest_rate) adds up the rate from every joined row (one per user), and COUNT(user_id) counts the number of users. Dividing gives the average rate weighted by user count, and ROUND(..., 2) ensures two decimal places. When all users in a type share the same rate, this simplifies to the original rate.

Step 4: CTE wrapper

Wrapping the query in a CTE (WITH aggregated AS ...) followed by SELECT * FROM aggregated is a common dbt pattern that keeps the model clean and composable. You could also write this as a flat query without the CTE and get the same result.