Scenario

You are a web developer analyzing user behavior on your company's website. You have three tables tracking different types of user interactions (visits, likes, and comments), each with a differently named timestamp column.

Task

Write a Snowflake SQL query that:

1. Selects user_id, page_id, and visit_time (aliased as interaction_time) from {{ ref("page_visits") }}, adding a literal 'visit' column as interaction_type
2. Selects user_id, page_id, and like_time (aliased as interaction_time) from {{ ref("page_likes") }}, adding a literal 'like' column as interaction_type
3. Selects user_id, page_id, and comment_time (aliased as interaction_time) from {{ ref("page_comments") }}, adding a literal 'comment' column as interaction_type
4. Combines all three result sets into one unified dataset

Schema

page_visits

page_likes

page_comments

Example

page_visits:

page_likes:

page_comments:

Expected Output:

Note: Each row from the three source tables appears exactly once in the output. The interaction_time column unifies the differently named timestamp columns, and interaction_type identifies which table each row originated from.

SELECT
    user_id,
    page_id,
    visit_time AS interaction_time,
    'visit' AS interaction_type
FROM {{ ref("page_visits") }}

UNION ALL

SELECT
    user_id,
    page_id,
    like_time AS interaction_time,
    'like' AS interaction_type
FROM {{ ref("page_likes") }}

UNION ALL

SELECT
    user_id,
    page_id,
    comment_time AS interaction_time,
    'comment' AS interaction_type
FROM {{ ref("page_comments") }}

Explanation

Step 1: Aliasing columns for a consistent schema

SELECT
    user_id,
    page_id,
    visit_time AS interaction_time,
    'visit' AS interaction_type
FROM {{ ref("page_visits") }}

Each source table has a differently named timestamp column (visit_time, like_time, comment_time). Use AS interaction_time to rename each one to a common column name. The literal string ('visit', 'like', 'comment') creates a new column that identifies the source of each row.

Step 2: Combining with UNION ALL

SELECT ... FROM {{ ref("page_visits") }}
UNION ALL
SELECT ... FROM {{ ref("page_likes") }}
UNION ALL
SELECT ... FROM {{ ref("page_comments") }}

UNION ALL stacks the result sets vertically. All SELECT blocks must return the same number of columns in the same order. Unlike UNION, UNION ALL does not remove duplicates, which is preferred here because every row represents a distinct interaction and deduplication would add unnecessary overhead.

Step 3: Alternative with CTE

WITH all_interactions AS (
    SELECT user_id, page_id, visit_time AS interaction_time, 'visit' AS interaction_type
    FROM {{ ref("page_visits") }}

    UNION ALL

    SELECT user_id, page_id, like_time AS interaction_time, 'like' AS interaction_type
    FROM {{ ref("page_likes") }}

    UNION ALL

    SELECT user_id, page_id, comment_time AS interaction_time, 'comment' AS interaction_type
    FROM {{ ref("page_comments") }}
)
SELECT * FROM all_interactions

Wrapping the UNION ALL in a CTE produces the same result. This pattern is useful when you need to apply further transformations (filtering, sorting, aggregation) on the combined dataset in a subsequent step.