Scenario

You work with data about venture capital investors and the startups they fund. Each investor has a personal funding limit, and you need to find investors whose average funding across their startups strictly exceeds that limit.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("investors") }} with {{ ref("startups") }} on investor_id
2. Computes avg_funding as the average of funding grouped by investor (rounded to 2 decimal places)
3. Filters to only include investors where avg_funding is strictly greater than funding_limit
4. Returns investor_id, investor_name, and avg_funding

Schema

investors

startups

Example

investors:

startups:

Expected Output:

Note: INV1 is excluded (avg 3.0 equals limit 3.0), INV3 is excluded (avg 4.0 equals limit 4.0). The filter requires strictly greater than, so equal values do not qualify.

SELECT
    i.investor_id,
    i.investor_name,
    ROUND(AVG(s.funding), 2) AS avg_funding
FROM {{ ref("investors") }} i
INNER JOIN {{ ref("startups") }} s
    ON i.investor_id = s.investor_id
GROUP BY i.investor_id, i.investor_name, i.funding_limit
HAVING AVG(s.funding) > i.funding_limit

Explanation

Step 1: Joining the Two Tables

FROM {{ ref("investors") }} i
INNER JOIN {{ ref("startups") }} s
    ON i.investor_id = s.investor_id

An INNER JOIN combines each investor with their funded startups. Investors who have no startups in the startups table are automatically excluded since there is no matching row.

Step 2: Grouping and Aggregating

GROUP BY i.investor_id, i.investor_name, i.funding_limit

Grouping by investor_id, investor_name, and funding_limit collapses all startup rows for each investor into a single group. Including funding_limit in the GROUP BY makes it available in the HAVING clause without needing an aggregate function. Since funding_limit is unique per investor, this does not change the grouping behavior.

Step 3: Filtering with HAVING

HAVING AVG(s.funding) > i.funding_limit

HAVING filters groups after aggregation (unlike WHERE, which filters individual rows before aggregation). Here it keeps only investors whose average funding strictly exceeds their personal limit. The strict greater-than operator (>) means investors whose average exactly equals the limit are excluded.

Step 4: Alternative Approach with CTE

WITH avg_per_investor AS (
    SELECT
        s.investor_id,
        AVG(s.funding) AS avg_funding
    FROM {{ ref("startups") }} s
    GROUP BY s.investor_id
)
SELECT
    i.investor_id,
    i.investor_name,
    a.avg_funding
FROM {{ ref("investors") }} i
INNER JOIN avg_per_investor a
    ON i.investor_id = a.investor_id
WHERE a.avg_funding > i.funding_limit

This approach computes the average in a CTE first, then joins it back to the investors table and filters with a regular WHERE clause. Both approaches produce identical results.