Scenario

You work at an online bookstore and have been given two tables containing daily sales transactions and product details.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("sales") }} with {{ ref("products") }} on product_id
2. Groups by sale_date and category
3. Calculates total_quantity as the sum of quantity_sold
4. Orders the result by sale_date ascending, then category ascending

Schema

sales

products

Example

sales:

products:

Expected Output:

Note: On 2024-03-02, two sales belong to Books (B1 with qty=7 and B3 with qty=3), so their quantities are summed to 10. Each date and category combination produces one row.

SELECT
    s.sale_date,
    p.category,
    SUM(s.quantity_sold) AS total_quantity
FROM {{ ref("sales") }} s
INNER JOIN {{ ref("products") }} p
    ON s.product_id = p.product_id
GROUP BY s.sale_date, p.category
ORDER BY s.sale_date ASC, p.category ASC

Explanation

Step 1: Joining the two tables

FROM {{ ref("sales") }} s
INNER JOIN {{ ref("products") }} p
    ON s.product_id = p.product_id

An INNER JOIN combines rows from sales and products where product_id matches in both tables. This attaches each sale to its product's category. Only sales with a matching product are included.

Step 2: Grouping and aggregating

GROUP BY s.sale_date, p.category

GROUP BY collapses all rows that share the same date and category into a single row. SUM(s.quantity_sold) then adds up the quantities within each group, producing one total per date and category combination.

Step 3: Ordering the results

ORDER BY s.sale_date ASC, p.category ASC

ORDER BY sorts the output chronologically by date first, then alphabetically by category within each date. This ensures a consistent, readable result set.

Step 4: Alternative with CTE

You can wrap the logic in a CTE for a cleaner structure, which is common in dbt models:

WITH daily_totals AS (
    SELECT
        s.sale_date,
        p.category,
        SUM(s.quantity_sold) AS total_quantity
    FROM {{ ref("sales") }} s
    INNER JOIN {{ ref("products") }} p
        ON s.product_id = p.product_id
    GROUP BY s.sale_date, p.category
)
SELECT *
FROM daily_totals
ORDER BY sale_date ASC, category ASC

For this query the flat version and the CTE version produce identical results. CTEs become more valuable when chaining multiple transformation steps.