What You Should Already Know

By the end of this lesson you will know exactly which skills this course expects, you will have tested your SQL with one self-check query, and you will know how the examples in this course work without a Snowflake account.

Snowflake is a cloud data platform. You talk to it in SQL, so this is not a first SQL course. Think of it like learning to drive a bus: you should already be comfortable driving a car. If some items below feel new, take the linked lessons first and come back. You will learn much faster that way.

The SQL you need

Most of your time in Snowflake is spent writing SELECT queries. You should be able to read and write these without looking things up:

A CTE (common table expression) is a named, temporary result you define with WITH at the top of a query. A window function calculates a value across related rows while keeping every row. Both appear in almost every lesson here.

The analytics ideas you need

Snowflake is mostly used by data analysts and data engineers. You should know what a business metric is (revenue, average order value, repeat customers), why we clean data before reporting, and how a report answers a business question. The Data Analytics course covers this path, especially Asking Good Business Questions and Key Business Metrics.

You do not need to know Python, but it helps for the later lessons on pipelines. You also do not need deep cloud knowledge. It is enough to know that a cloud provider (AWS, Microsoft Azure or Google Cloud) rents out computers and storage over the internet, and you pay for what you use.

A self-check query

Run this query. It uses a join, GROUP BY, a CTE and a window function together. If you can explain every line, you are ready.

WITH category_sales AS (
  SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  JOIN orders o ON o.order_id = oi.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category
)
SELECT category, revenue,
       RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM category_sales
ORDER BY revenue_rank;

category    | revenue | revenue_rank
------------+---------+-------------
Displays    | 47997   | 1
Audio       | 22996   | 2
Accessories | 21987   | 3
Storage     | 9244    | 4
Books       | 2250    | 5
(5 rows)

Step by step, this is what happens:

1. The CTE category_sales joins each order line to its product and its order.
2. Cancelled orders are removed by the WHERE clause.
3. GROUP BY p.category adds up quantity * unit_price for each category.
4. The outer query ranks the categories with RANK(), highest revenue first.

You should see Displays at the top, because two orders bought the 27-inch Monitor. If any step confused you, revisit the matching lesson in the table above.

A second check: top customer per city

This one filters on a window function, a pattern you will meet again with Snowflake's QUALIFY clause:

SELECT city, first_name, orders_placed
FROM (
  SELECT c.city, c.first_name, COUNT(*) AS orders_placed,
         ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY COUNT(*) DESC, c.first_name) AS pos
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.city, c.first_name
) ranked
WHERE pos = 1 AND city IN ('Rohtak', 'Jaipur', 'Mumbai')
ORDER BY city;

city   | first_name | orders_placed
-------+------------+--------------
Jaipur | Priya      | 2
Mumbai | Rohan      | 2
Rohtak | Aarav      | 3
(3 rows)

Rohtak has two customers with orders, Aarav and Vikram. Aarav placed more orders, so he is first. If you understood why the filter sits in the outer query and not in WHERE of the inner query, your window function basics are solid.

How this course runs examples

You do not need a Snowflake account for this course, and no lesson asks for credentials. Examples come in two kinds:

• Runnable examples (with a Run button) use portable SQL: joins, CTEs, window functions and CASE. They run in your browser on a small SQLite copy of the practice shop database (customers, products, orders, order_items, employees). The same SQL also works in Snowflake.
• Snowflake-only examples (warehouses, stages, COPY INTO, Time Travel, streams, tasks, VARIANT) are shown as plain code. Their output follows the official Snowflake documentation, so you know what to expect when you try them in a real account.

If you want hands-on practice later, Snowflake offers a free trial account. Check the current trial terms on the Snowflake website; this course never depends on them.

What this course covers

You will move from the big picture to hands-on detail:

1. Architecture: how storage, compute and cloud services are separated.
2. Virtual warehouses, databases, schemas and tables.
3. Loading files with stages, file formats, COPY INTO and Snowpipe.
4. Snowflake SQL extras, MERGE, JSON in VARIANT columns and FLATTEN.
5. Time Travel, cloning, streams, tasks and dynamic tables.
6. Performance, caching and cost awareness with credits.
7. Security and governance, then one complete project.

Key points

• This course expects confident SQL: joins, GROUP BY, CTEs and window functions.
• Basic analytics ideas (metrics, clean data, business questions) come from the Data Analytics course.
• No Snowflake account is needed; runnable examples use portable SQL on a practice database.
• Snowflake-only commands are shown as plain code with output traced from the official docs.
• If the self-check queries felt hard, revise the linked SQL lessons first.

SELECT category,
       COUNT(*) AS products,
       SUM(stock) AS total_stock,
       ROUND(100.0 * SUM(stock) / SUM(SUM(stock)) OVER (), 1) AS pct_of_stock
FROM products
GROUP BY category
ORDER BY total_stock DESC;