SQL: Querying, Filtering, Joins, and Window Functions

Snowflake is a cloud-native data warehouse that has revolutionized how organizations handle large-scale analytics. While it uses standard SQL, Snowflake introduces unique features that enhance performance, scalability, and ease of use. This tutorial focuses on the core SQL operations you'll use daily: querying, filtering, joins, and window functions. You'll learn not just the syntax, but how to write efficient queries that leverage Snowflake's architecture. We'll start with basic SELECT statements and filtering, then move to joins across multiple tables, and finally dive into window functions for analytical queries. Each section includes production-ready examples and common pitfalls. By the end, you'll be able to write complex analytical queries that run efficiently on terabytes of data.

Basic Querying with SELECT

Snowflake's SELECT statement follows standard SQL but includes powerful extensions. Start with simple queries to explore your data. Use SELECT to see all columns, but in production, always specify columns to reduce I/O. Snowflake's columnar storage makes SELECT efficient only when you need all columns. Use LIMIT to preview data. Snowflake supports TOP and LIMIT, but LIMIT is more common. Example: SELECT order_id, customer_id, order_date FROM orders LIMIT 10;

-- Select specific columns with limit
SELECT order_id, customer_id, order_date
FROM orders
LIMIT 10;

Filtering with WHERE

Filtering in Snowflake uses the standard WHERE clause with predicates. Snowflake's micro-partition pruning automatically skips irrelevant partitions based on the filter conditions. For optimal performance, filter on columns that are used in the clustering key. Use comparison operators, IN, BETWEEN, LIKE, and IS NULL. Snowflake also supports QUALIFY for filtering after window functions. Example: SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

-- Filter by date range
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND total_amount > 100
ORDER BY total_amount DESC;

Joins in Snowflake

Snowflake supports all standard join types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, and NATURAL joins. It also supports LATERAL joins and ASOF joins for time-series data. For large tables, ensure join columns are of the same data type to avoid implicit casts. Snowflake's query optimizer automatically chooses join order and algorithm (hash join, merge join, etc.). Use EXPLAIN to see the join strategy. Example: SELECT c.customer_name, o.order_id, o.total_amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Inner join with aggregation
SELECT c.customer_name, COUNT(o.order_id) AS order_count, SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_name
ORDER BY total_spent DESC;

Window Functions: ROW_NUMBER, RANK, DENSE_RANK

Window functions perform calculations across a set of rows related to the current row. They are essential for analytical queries. Snowflake supports all standard window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LEAD, LAG, FIRST_VALUE, LAST_VALUE, and aggregate functions with OVER clause. The PARTITION BY clause divides the result set into partitions, and ORDER BY defines the order within each partition. Example: SELECT customer_id, order_date, total_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders;

-- Rank customers by total spending per month
SELECT customer_id, DATE_TRUNC('month', order_date) AS month,
       SUM(total_amount) AS monthly_total,
       RANK() OVER (PARTITION BY DATE_TRUNC('month', order_date) ORDER BY SUM(total_amount) DESC) AS rank
FROM orders
GROUP BY customer_id, month
QUALIFY rank <= 3
ORDER BY month, rank;

Aggregate Functions with OVER: Running Totals and Moving Averages

Aggregate functions like SUM, AVG, COUNT can be used with OVER to compute running totals, moving averages, and cumulative statistics. The frame clause (ROWS BETWEEN) defines which rows to include. Snowflake supports ROWS, RANGE, and GROUPS frames. Example: SELECT order_date, total_amount, SUM(total_amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders;

-- 7-day moving average of daily sales
SELECT order_date, daily_total,
       AVG(daily_total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM (
    SELECT order_date, SUM(total_amount) AS daily_total
    FROM orders
    GROUP BY order_date
) AS daily
ORDER BY order_date;

Time Travel and Zero-Copy Cloning

Snowflake's Time Travel allows you to query historical data up to 90 days back (depending on edition). Use AT or BEFORE with a timestamp, offset, or query ID. Zero-copy cloning creates a copy of a table without duplicating data, useful for testing and backups. Example: SELECT * FROM orders AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP); CREATE OR REPLACE TABLE orders_backup CLONE orders;

-- Query data as of 1 hour ago
SELECT * FROM orders AT (OFFSET => -3600);

-- Clone a table for testing
CREATE OR REPLACE TABLE orders_dev CLONE orders;

Best Practices for Performance

To get the best performance from Snowflake SQL: 1) Use clustering keys on large tables that are frequently filtered by certain columns. 2) Avoid SELECT * in production; specify only needed columns. 3) Use EXPLAIN to understand query plans. 4) Leverage materialized views for pre-aggregated data. 5) Use appropriate warehouse size; auto-suspend to save costs. 6) Use QUALIFY instead of subqueries for window function filtering. 7) Use CTEs for readability but be aware they are materialized differently in Snowflake.

-- Use CTE for readability and performance
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month,
           SUM(total_amount) AS total
    FROM orders
    GROUP BY month
)
SELECT month, total,
       LAG(total) OVER (ORDER BY month) AS prev_month_total
FROM monthly_sales
ORDER BY month;