Scenario

You work for a real estate company that manages properties for multiple landlords and need to summarize total rental income per landlord.

Task

Write a Snowflake SQL query that:

1. Deduplicates {{ ref("properties_df") }} and {{ ref("landlords_df") }} (the input data may contain duplicate rows)
2. Joins properties to landlords on landlord_id so that only landlords who own at least one property are included
3. Concatenates first_name and last_name (separated by a space) into a column called landlord_name
4. Calculates total_rental_income by summing the rent column per landlord
5. Groups results by landlord_id and landlord_name

Schema

properties_df

landlords_df

Example

properties_df:

landlords_df:

Expected Output:

Note: Landlords 104 and 105 are excluded because they have no properties.

WITH properties AS (
    SELECT DISTINCT *
    FROM {{ ref("properties_df") }}
),
landlords AS (
    SELECT DISTINCT *
    FROM {{ ref("landlords_df") }}
),
joined AS (
    SELECT
        l.landlord_id,
        l.first_name || ' ' || l.last_name AS landlord_name,
        SUM(p.rent)                         AS total_rental_income
    FROM properties p
    INNER JOIN landlords l
        ON p.landlord_id = l.landlord_id
    GROUP BY l.landlord_id, landlord_name
)
SELECT * FROM joined

Explanation

Step 1: Deduplicating with DISTINCT

WITH properties AS (
    SELECT DISTINCT *
    FROM {{ ref("properties_df") }}
),
landlords AS (
    SELECT DISTINCT *
    FROM {{ ref("landlords_df") }}
)

The input tables may contain duplicate rows. Wrapping each source in a CTE with SELECT DISTINCT * removes exact duplicates before any joins or aggregations. Without this step, duplicates would inflate the SUM(rent) result.

Step 2: Joining with INNER JOIN

FROM properties p
INNER JOIN landlords l
    ON p.landlord_id = l.landlord_id

INNER JOIN returns only rows where landlord_id exists in both tables. This naturally excludes landlords who have no properties, which is exactly the required behavior. A LEFT JOIN from landlords would incorrectly include landlords with zero properties.

Step 3: String Concatenation and Aggregation

SELECT
    l.landlord_id,
    l.first_name || ' ' || l.last_name AS landlord_name,
    SUM(p.rent)                         AS total_rental_income

The || operator concatenates strings in Snowflake. You could also use CONCAT(l.first_name, ' ', l.last_name). The SUM aggregate computes total rent across all properties for each landlord. The GROUP BY clause groups on both landlord_id and the derived landlord_name column.

Step 4: Alternative Without CTEs

If you are confident there are no duplicates, you can write this as a single query:

SELECT
    l.landlord_id,
    l.first_name || ' ' || l.last_name AS landlord_name,
    SUM(p.rent)                         AS total_rental_income
FROM {{ ref("properties_df") }} p
INNER JOIN {{ ref("landlords_df") }} l
    ON p.landlord_id = l.landlord_id
GROUP BY l.landlord_id, landlord_name

This is simpler but will produce incorrect totals if the source data contains duplicate rows.