Scenario

You work as an AI engineer at a technology company and have been given two tables containing information about AI models and their daily usage records.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("models") }} with {{ ref("usage_logs") }} on Model_ID
2. Computes Total_Uses as the sum of Uses for each model
3. Computes Average_Accuracy as the average Accuracy across all models sharing the same Model_Type, rounded to 2 decimal places, using a window function
4. Returns the columns Model_ID, Model_Name, Model_Type, Accuracy, Total_Uses, and Average_Accuracy

Schema

models

usage_logs

Example

models:

usage_logs:

Expected Output:

Note: Total_Uses sums all usage rows per model (e.g., A1 has 80 + 60 = 140). Average_Accuracy averages the accuracy of all models in the same type (e.g., Classification models A1 and A2 share (0.91 + 0.87) / 2 = 0.89).

WITH usage_agg AS (
    SELECT
        Model_ID,
        SUM(Uses) AS Total_Uses
    FROM {{ ref("usage_logs") }}
    GROUP BY Model_ID
)
SELECT
    m.Model_ID,
    m.Model_Name,
    m.Model_Type,
    m.Accuracy,
    u.Total_Uses,
    ROUND(AVG(m.Accuracy) OVER (PARTITION BY m.Model_Type), 2) AS Average_Accuracy
FROM {{ ref("models") }} m
INNER JOIN usage_agg u ON m.Model_ID = u.Model_ID

Explanation

Step 1: Aggregate usage per model

WITH usage_agg AS (
    SELECT
        Model_ID,
        SUM(Uses) AS Total_Uses
    FROM {{ ref("usage_logs") }}
    GROUP BY Model_ID
)

Because a single model can appear on multiple dates in the usage table, you first collapse those rows with SUM(Uses) grouped by Model_ID. Doing this in a CTE before the join prevents row multiplication that would skew the accuracy average in the next step.

Step 2: Join models with aggregated usage

FROM {{ ref("models") }} m
INNER JOIN usage_agg u ON m.Model_ID = u.Model_ID

An INNER JOIN ensures the result only includes models that have at least one usage record. If you needed to keep models with zero usage, you would switch to a LEFT JOIN and handle nulls in Total_Uses.

Step 3: Compute average accuracy with a window function

ROUND(AVG(m.Accuracy) OVER (PARTITION BY m.Model_Type), 2) AS Average_Accuracy

AVG(...) OVER (PARTITION BY ...) is a window function that computes the average within each partition without collapsing rows. Wrapping it in ROUND(..., 2) ensures the result has two decimal places. Every model in the same Model_Type group receives the same Average_Accuracy value. This differs from a GROUP BY approach, which would require you to collapse the result into one row per type and then join back.

Step 4: Alternative using inline subquery

You could avoid the CTE by using a subquery in the JOIN:

SELECT
    m.Model_ID,
    m.Model_Name,
    m.Model_Type,
    m.Accuracy,
    u.Total_Uses,
    ROUND(AVG(m.Accuracy) OVER (PARTITION BY m.Model_Type), 2) AS Average_Accuracy
FROM {{ ref("models") }} m
INNER JOIN (
    SELECT Model_ID, SUM(Uses) AS Total_Uses
    FROM {{ ref("usage_logs") }}
    GROUP BY Model_ID
) u ON m.Model_ID = u.Model_ID

Both produce identical results. The CTE version is more readable when the aggregation logic is complex.