Paths/Data Engineer/SQL & Query Craft / Query Foundations
Join and Filter Like a Pro
The first query you ever write is a join. Build one that lists the active users and the number of orders each of them placed.
Validator
IDLE — checks run live as you type commands; submit when done
01Requirements
live checks- Directory ~/warehouse/queries exists○
- File ~/warehouse/queries/active_users.sql exists○
- The query selects from two tables and joins them○
- The query filters on a status and orders the result○
02Walkthrough
5 steps- 01
Create the queries directory
Every query lives in version control - one folder per warehouse.
mkdir -p ~/warehouse/queries - 02
Write the select list
Start with what you want to see. u.id, u.name and the order count - the rest of the query exists to produce these three things.
echo SELECT u.id, u.name, count(o.id) AS order_count\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE u.status = 'active'\nGROUP BY u.id, u.name\nORDER BY order_count DESC\nLIMIT 100; > ~/warehouse/queries/active_users.sql - 03
Read the query back
cat prints the file. Read it top to bottom - it should say what it does in plain words.
cat ~/warehouse/queries/active_users.sql - 04
Check the pieces
Four clauses carry the whole query - SELECT, FROM, WHERE and GROUP BY. If you cannot name the clause a line belongs to, the line is in the wrong place.
grep SELECT ~/warehouse/queries/active_users.sql - 05
Submit for validation
The validator checks the file and the four clauses your query needs.
03Stuck?
0 of 3 revealedTake a hint without losing the lab — each one points at the thing to look at, not at the answer.
04AI Tutor
guides, never solvesTell me what you're stuck on — I'll nudge, not solve. Which requirement are you looking at?
04Notebook
saved locally in this browserlearner@lab — ubuntu:24.04
Session is disposable
↑/↓ recalls commands · Ctrl+Enter submits · type help for the command list