Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 38 ยท Projects and Practice

Practice: SQL Challenges and Snowflake Interview Questions

Practice analytics SQL with window functions, deduplication, gaps and cohorts, then review Snowflake interview questions on warehouses, caching, clustering, streams and cost.

The best way to remember a platform is to use it under a little pressure. This practice lesson has two parts. First, six SQL challenges that mirror everyday analytics work and that run in your browser on the sample databases. Each has a hint, and the solution sits in a second editor so you can try before you peek. Second, a set of Snowflake questions you can use as a self-test or as interview preparation.

The queries are portable SQL. Snowflake offers extra shortcuts, such as QUALIFY for filtering on window functions, which are shown after the solutions. Everything about warehouses, caching and pipelines is covered in the conceptual part, since those features cannot run here.

Challenge 1: top product per category

For each category, find the product with the most revenue. Hint: aggregate first, then rank with ROW_NUMBER() in a subquery.

SQL Challenge 1: your turn ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Challenge 1: solution ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In Snowflake, the outer query shrinks to a QUALIFY ROW_NUMBER() OVER (...) = 1 on the aggregate query:

SQL
SELECT c.name AS category, p.name AS product, SUM(i.quantity * i.unit_price) AS revenue
FROM order_items i JOIN products p ON p.id = i.product_id JOIN categories c ON c.id = p.category_id
GROUP BY c.id, c.name, p.id, p.name
QUALIFY ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(i.quantity * i.unit_price) DESC) = 1;

Challenge 2: deduplicate keeping the latest

Pretend a source sends the same customer several times. Keep only the newest row per customer. Hint: ROW_NUMBER() partitioned by the key, ordered by the timestamp descending.

SQL Challenge 2: solution ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This pattern, with QUALIFY in Snowflake, sits inside nearly every CLEAN layer.

Challenge 3: month-over-month growth

Compute monthly revenue and the percentage change from the previous month. Hint: LAG().

SQL Challenge 3: solution ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Challenge 4: customers who never ordered

List customers with no orders. Hint: LEFT JOIN and check for NULL, or NOT EXISTS.

SQL Challenge 4: solution ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Challenge 5: salary above the department average

Find employees earning more than their department's average, with the gap. Hint: a window average per department.

SQL Challenge 5: solution ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Challenge 6: sessionize events

Group a user's events into sessions: a new session starts after a gap of more than 30 minutes. This "gaps and islands" idea is a favorite interview problem. Hint: flag a new session with LAG, then take a running sum of the flags.

SQL Challenge 6: solution

Press Run (or Ctrl + Enter) to see the result here.

User 1 gets three sessions (minutes 0 to 12, 90 to 95 and 200), and user 2 gets two.

Part two: Snowflake self-test

Answer each question out loud before you read the model answer.

1. Why are storage and compute separate in Snowflake, and what does that allow? Data lives in cloud object storage, queries run on virtual warehouses. You can scale, pause and isolate compute per workload without moving data, and several warehouses can read the same table at once.

2. A dashboard is slow only at 9 a.m. What do you look at, and what do you change? Queue time in query history. Queued overload means concurrency, so a multi-cluster warehouse (scale out) or a separate warehouse. See multi-cluster warehouses.

3. A single report query takes 20 minutes. What are your first three checks? The Query Profile for partitions scanned versus total, bytes spilled, and exploding joins. Then fix pruning or the join; resize only if the query scales and spills.

4. When do you use a clustering key? Very large tables, queried far more than changed, with selective filters on the key. Measure depth first and watch reclustering credits.

5. What does the result cache need to be reused? Identical text, unchanged data, no non-deterministic functions, same privileges, and within 24 hours.

6. How do streams and tasks work together? A stream exposes changes since its offset; a task with WHEN SYSTEM$STREAM_HAS_DATA runs a MERGE that consumes the stream, moving the offset on commit.

7. Dynamic table or stream plus task? Dynamic table for declarative SQL transformations with a lag target. Stream plus task for custom logic, procedures and side effects.

8. How do you let analysts see emails partially and engineers fully, from one table? A masking policy on the column that checks the current role, ideally attached through a tag.

9. How do you share data with a partner without copying it? Create a share with a secure view; the partner creates a database from it (or uses a reader account).

10. How do you cap spending? Auto-suspend, right-sized warehouses, resource monitors (notify, then suspend), budgets for serverless features, statement timeouts.

Python Warehouse cost drill (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The drill shows two classic exam points: a bigger size is not always costlier, and many tiny separate starts waste the 60-second minimum.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which window function gives the previous row's value?

    LAG looks back at an earlier row; LEAD looks forward.
  2. 2. What does QUALIFY do in Snowflake?

    QUALIFY is like WHERE or HAVING, but for window function results.
  3. 3. Many queries queue at peak time only. Best fix?

    Queueing is a concurrency problem, solved by adding clusters or splitting workloads.
  4. 4. To keep the newest row per key you would use...

    ROW_NUMBER with a descending order puts the newest row at rank 1.

Summary

  • Practice patterns: top-N per group, deduplication, growth with LAG, anti-joins, window averages and sessionization.
  • QUALIFY shortens window-function filters in Snowflake.
  • Be ready to explain warehouses, caching, pruning, clustering, streams, tasks, policies, sharing and cost.
  • Return to any lesson of the course if an answer felt shaky.

Next: Back to the Snowflake course