Scenario

You are working on a CRM platform that tracks customers, their orders, and the products they purchased.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("orders") }} to {{ ref("customers") }} on customer_id
2. Joins the result to {{ ref("products") }} on product_id
3. Concatenates first_name and last_name (separated by a space) as customer_name
4. Aliases email as customer_email and category as product_category
5. Returns only six columns: order_id, customer_name, customer_email, product_name, product_category, order_date

Schema

customers

orders

products

Example

customers:

orders:

products:

Expected Output:

Note: Each order row is enriched with the customer's full name, email, and the product details by joining all three tables on their respective keys.

SELECT
    o.order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email                             AS customer_email,
    p.product_name,
    p.category                          AS product_category,
    o.order_date
FROM {{ ref("orders") }} o
INNER JOIN {{ ref("customers") }} c ON o.customer_id = c.customer_id
INNER JOIN {{ ref("products") }} p ON o.product_id = p.product_id

Explanation

Step 1: Joining Three Tables

FROM {{ ref("orders") }} o
INNER JOIN {{ ref("customers") }} c ON o.customer_id = c.customer_id
INNER JOIN {{ ref("products") }} p ON o.product_id = p.product_id

Start from orders as the driving table, then join to customers on customer_id and to products on product_id. Using INNER JOIN ensures that only orders with a matching customer and a matching product appear in the result. If you used LEFT JOIN instead, orders referencing nonexistent customers or products would still appear with NULLs.

Step 2: String Concatenation for customer_name

c.first_name || ' ' || c.last_name AS customer_name

The || operator concatenates strings in Snowflake. Place a space literal ' ' between the two name columns to produce a full name like John Doe. Alternatively, you could use CONCAT(c.first_name, ' ', c.last_name), which behaves identically.

Step 3: Column Aliasing

c.email    AS customer_email,
p.category AS product_category

Use AS to rename columns in the output. The original column names (email, category) are generic, so aliasing them to customer_email and product_category makes the output schema self-documenting. Only the six columns listed in the SELECT clause are returned.

Step 4: Alternative with CTE

You can also wrap the query in a CTE for readability:

WITH joined AS (
    SELECT
        o.order_id,
        c.first_name || ' ' || c.last_name AS customer_name,
        c.email                             AS customer_email,
        p.product_name,
        p.category                          AS product_category,
        o.order_date
    FROM {{ ref("orders") }} o
    INNER JOIN {{ ref("customers") }} c ON o.customer_id = c.customer_id
    INNER JOIN {{ ref("products") }} p ON o.product_id = p.product_id
)
SELECT * FROM joined

This becomes more valuable when you need to chain multiple steps (filtering, aggregating, windowing) on top of the joined result.