Scenario

You work at a video streaming company and have been given a table containing information about videos on the platform.

Task

Write a Snowflake SQL query that:

1. Selects all columns from {{ ref("videos") }}
2. Filters to only include videos with more than 1,000,000 views
3. Filters to only include videos released in 2019 or later

Schema

videos

Example

videos:

Expected Output:

Note: Video 2 is excluded (under 1,000,000 views), Video 4 is excluded (released before 2019), Video 5 is excluded (under 1,000,000 views).

SELECT *
FROM {{ ref("videos") }}
WHERE view_count > 1000000
  AND release_year >= 2019

Explanation

Step 1: Filtering with WHERE

WHERE view_count > 1000000
  AND release_year >= 2019

The WHERE clause filters rows before they appear in the result set. Use comparison operators (>, >=, <, <=, =, !=) to test column values against thresholds. Combine multiple conditions with AND (both must be true) or OR (either can be true). Here we need both conditions to hold, so AND is the right choice.

Step 2: Referencing the Source Table

FROM {{ ref("videos") }}

The {{ ref("videos") }} syntax references the videos table. Always use ref() to reference your source tables so the platform can resolve them correctly.

Step 3: Alternative Approach with CTE

You can wrap your query in a CTE (Common Table Expression) for readability, especially when the logic gets more complex:

WITH filtered AS (
    SELECT *
    FROM {{ ref("videos") }}
    WHERE view_count > 1000000
      AND release_year >= 2019
)
SELECT * FROM filtered

For a simple filter like this, the flat query and the CTE produce identical results and execution plans. CTEs become more useful when you need to chain multiple transformation steps.