Scenario

You work for a food and beverage company and have three tables containing product details, sales records, and inventory stock levels across warehouses.

Task

Write a Snowflake SQL query that:

1. Aggregates {{ ref("sales") }} to compute total_quantity (sum of quantity) and total_revenue (sum of revenue) per product
2. Aggregates {{ ref("inventory") }} to compute total_stock (sum of stock) per product
3. Joins both aggregations to {{ ref("products") }} using LEFT JOIN so all products appear even if they have no sales or inventory records
4. Replaces any NULL values in total_quantity, total_revenue, and total_stock with 0
5. Returns columns: product_id, name, category, total_quantity, total_revenue, total_stock

Schema

products

sales

inventory

Example

products:

sales:

inventory:

Expected Output:

Note: Product 5 (Mango Smoothie) has no sales records, so total_quantity and total_revenue are 0 instead of NULL. All products appear in the output regardless of whether they have sales or inventory records.

WITH sales_agg AS (
    SELECT
        product_id,
        SUM(quantity) AS total_quantity,
        SUM(revenue) AS total_revenue
    FROM {{ ref("sales") }}
    GROUP BY product_id
),
inventory_agg AS (
    SELECT
        product_id,
        SUM(stock) AS total_stock
    FROM {{ ref("inventory") }}
    GROUP BY product_id
)
SELECT
    p.product_id,
    p.name,
    p.category,
    COALESCE(s.total_quantity, 0) AS total_quantity,
    COALESCE(s.total_revenue, 0) AS total_revenue,
    COALESCE(i.total_stock, 0) AS total_stock
FROM {{ ref("products") }} p
LEFT JOIN sales_agg s
    ON p.product_id = s.product_id
LEFT JOIN inventory_agg i
    ON p.product_id = i.product_id

Explanation

Step 1: Aggregate sales per product

WITH sales_agg AS (
    SELECT
        product_id,
        SUM(quantity) AS total_quantity,
        SUM(revenue) AS total_revenue
    FROM {{ ref("sales") }}
    GROUP BY product_id
)

A CTE groups the sales table by product_id and computes SUM(quantity) and SUM(revenue). This collapses multiple sale rows for the same product into a single row with totals.

Step 2: Aggregate inventory per product

inventory_agg AS (
    SELECT
        product_id,
        SUM(stock) AS total_stock
    FROM {{ ref("inventory") }}
    GROUP BY product_id
)

A second CTE does the same for inventory. Since a product can appear in multiple warehouses, SUM(stock) gives the total stock across all locations.

Step 3: LEFT JOIN to preserve all products

FROM {{ ref("products") }} p
LEFT JOIN sales_agg s
    ON p.product_id = s.product_id
LEFT JOIN inventory_agg i
    ON p.product_id = i.product_id

LEFT JOIN ensures every product from the products table appears in the result, even if it has no matching rows in sales_agg or inventory_agg. An INNER JOIN would silently drop products with no sales or no inventory.

Step 4: Replace NULLs with COALESCE

COALESCE(s.total_quantity, 0) AS total_quantity,
COALESCE(s.total_revenue, 0) AS total_revenue,
COALESCE(i.total_stock, 0) AS total_stock

When a LEFT JOIN finds no matching row, the joined columns come back as NULL. COALESCE returns the first non-NULL argument, so COALESCE(NULL, 0) returns 0. This is the standard pattern for replacing join-produced NULLs with a default value.