Scenario

You work for an e-commerce platform and have been given two tables containing product information and order records.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("products_df") }} with {{ ref("orders_df") }} on product_id
2. Calculates the average price per category, rounded to 2 decimal places (as avg_price)
3. Calculates the total number of orders per category (as total_orders_count)
4. Groups results by category

Schema

products_df

orders_df

Example

products_df:

orders_df:

Expected Output:

Note: avg_price is the average of joined product prices per category, rounded to 2 decimal places (product 1 appears twice in Apparel because it has two orders). total_orders_count is the count of order rows, not the sum of quantities.

WITH joined AS (
    SELECT
        p.category,
        p.price,
        o.order_id
    FROM {{ ref("products_df") }} p
    INNER JOIN {{ ref("orders_df") }} o
        ON p.product_id = o.product_id
)

SELECT
    category,
    ROUND(AVG(price), 2) AS avg_price,
    COUNT(order_id) AS total_orders_count
FROM joined
GROUP BY category

Explanation

Step 1: Joining the Two Tables

FROM {{ ref("products_df") }} p
INNER JOIN {{ ref("orders_df") }} o
    ON p.product_id = o.product_id

An INNER JOIN on product_id combines each order row with its matching product row. This means a product that appears in three orders will produce three joined rows, each carrying that product's category and price. Products with no orders are excluded.

Step 2: Aggregating by Category

SELECT
    category,
    ROUND(AVG(price), 2) AS avg_price,
    COUNT(order_id) AS total_orders_count
FROM joined
GROUP BY category

GROUP BY category collapses all joined rows for each category into a single output row. ROUND(AVG(price), 2) computes the mean price across all joined rows (not distinct products) and rounds to two decimal places, so a product ordered multiple times contributes its price once per order. COUNT(order_id) counts the number of order rows per category.

Step 3: Alternative Without a CTE

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

SELECT
    p.category,
    ROUND(AVG(p.price), 2) AS avg_price,
    COUNT(o.order_id) AS total_orders_count
FROM {{ ref("products_df") }} p
INNER JOIN {{ ref("orders_df") }} o
    ON p.product_id = o.product_id
GROUP BY p.category

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