Scenario

You work at a social media platform and have a table recording interactions between users, where occasionally a user interacts with their own post.

Task

Write a Snowflake SQL query that:

1. Reads from {{ ref("interactions") }}
2. Filters to only include rows where a user interacted with themselves (user1_id equals user2_id)
3. Groups by user and counts the number of self-interactions per user

The output should contain two columns: user_id and self_interaction_count.

Schema

interactions

Example

interactions:

Expected Output:

Note: Only users 202 and 204 interacted with themselves. Users 201, 203, and 205 interacted with different users and are excluded.

WITH self_interactions AS (
    SELECT *
    FROM {{ ref("interactions") }}
    WHERE user1_id = user2_id
)

SELECT
    user1_id AS user_id,
    COUNT(*) AS self_interaction_count
FROM self_interactions
GROUP BY user1_id

Explanation

Step 1: Filter for self-interactions

WHERE user1_id = user2_id

The WHERE clause keeps only the rows where both user columns match, meaning the user interacted with their own post. All other rows (where user1_id and user2_id differ) are discarded.

Step 2: Aggregate by user

SELECT
    user1_id AS user_id,
    COUNT(*) AS self_interaction_count
FROM self_interactions
GROUP BY user1_id

GROUP BY user1_id groups the filtered rows by user. COUNT(*) counts how many self-interactions each user has. The alias AS user_id renames the column to match the expected output schema.

Step 3: Alternative without CTE

You can write this as a single query without a CTE:

SELECT
    user1_id AS user_id,
    COUNT(*) AS self_interaction_count
FROM {{ ref("interactions") }}
WHERE user1_id = user2_id
GROUP BY user1_id

Both versions produce identical results. The CTE version is more readable when additional transformation steps are needed.