Snowflake Joins and Aggregations

Real questions rarely live in one table. Customer names sit in one table, orders in another, and products in a third. Joins connect those tables, and aggregations turn thousands of rows into a handful of meaningful numbers. Both skills form the heart of everyday analytics work.

The Sample Data

Two small tables carry every example below.

CUSTOMERS                       ORDERS
+-------------+--------+        +----------+-------------+--------+
| customer_id | name   |        | order_id | customer_id | amount |
+-------------+--------+        +----------+-------------+--------+
| 1           | Asha   |        | 101      | 1           | 250    |
| 2           | Ravi   |        | 102      | 1           | 100    |
| 3           | Meera  |        | 103      | 2           | 400    |
+-------------+--------+        | 104      | 9           | 75     |
                                +----------+-------------+--------+

Meera has no orders. Order 104 belongs to customer 9, who does not exist in the customer table. These two gaps show how each join type behaves.

Join Types in Layman's Terms

Picture two guest lists for a party. A join decides who appears on the combined list.

JOIN TYPE     WHO APPEARS IN THE RESULT
-----------   ----------------------------------------------
INNER JOIN    Only guests present on both lists
LEFT JOIN     Everyone on the left list, plus matches from right
RIGHT JOIN    Everyone on the right list, plus matches from left
FULL JOIN     Everyone from both lists
CROSS JOIN    Every left guest paired with every right guest

Inner Join

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

The result holds Asha twice and Ravi once. Meera and order 104 disappear because neither has a partner.

Left Join

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

Meera now appears with empty order columns. Left joins answer questions such as "which customers never bought anything?" by filtering rows where o.order_id IS NULL.

Aggregate Functions

Aggregate functions collapse many rows into one value.

  • COUNT counts rows.
  • SUM adds numeric values.
  • AVG calculates the mean.
  • MIN and MAX find the smallest and largest values.
  • LISTAGG joins text values into one string.

GROUP BY and HAVING

GROUP BY splits rows into buckets and runs the aggregate once per bucket. HAVING filters those buckets after calculation. WHERE filters individual rows before grouping.

SELECT c.name,
       COUNT(o.order_id) AS total_orders,
       SUM(o.amount)     AS total_spent
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.name
HAVING SUM(o.amount) > 200
ORDER BY total_spent DESC;
HOW GROUP BY WORKS
==================

Rows from the join                Buckets                 Result
+-------+--------+               +--------------+        +-------+-------+
| Asha  | 250    |  ----+        | Asha: 250,100|  -->   | Asha  | 350   |
| Asha  | 100    |  ----+        +--------------+        | Ravi  | 400   |
| Ravi  | 400    |  -------->   | Ravi: 400    |        +-------+-------+
+-------+--------+               +--------------+

QUALIFY: A Snowflake Shortcut

Window functions rank or number rows without collapsing them. Snowflake adds the QUALIFY clause to filter on window results directly, which removes the need for a subquery. The query below returns each customer's most recent order.

SELECT customer_id, order_id, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY customer_id
          ORDER BY order_id DESC) = 1;

Performance Tips

  • Select only the columns you need; Snowflake stores data by column and skips the rest.
  • Filter early with WHERE so fewer rows reach the join.
  • Join on columns with matching data types to avoid hidden conversions.
  • Check row counts after a join; unexpected growth signals a duplicate key problem.

A Practical Scenario

A sales manager asks for a list of customers who spent more than 500 in total last quarter, ranked from highest to lowest. An analyst joins the customer table to the order table, groups by customer name, sums the amounts, and filters the groups with HAVING. A LEFT JOIN variation lists customers with no orders at all, which helps the marketing team plan a win-back campaign. Both questions use the same two tables and the same building blocks.

Frequently Asked Questions

Why does my join return too many rows?

Duplicate keys on either side multiply matches. Two rows with the same key on each side produce four result rows. Count distinct keys on both tables before joining.

Should I write WHERE or HAVING?

Use WHERE for conditions on single rows and HAVING for conditions on aggregate results such as totals and counts.

Key Points

  • INNER JOIN keeps matches only; LEFT JOIN keeps every left row.
  • FULL JOIN keeps everything; CROSS JOIN creates every possible pair.
  • Aggregate functions turn many rows into summary values.
  • WHERE filters rows before grouping; HAVING filters groups after calculation.
  • QUALIFY filters window function results without a subquery.
Previous lesson
Back to course
Next lesson