Why this matters
Most company data lives in databases, and most data science adverts ask for SQL alongside Python. Analysts write queries that filter, join and group. Data scientists go further: they compute features such as running totals and gaps between events inside the database, so only a tidy, manageable table travels to the notebook. This saves time, memory and mobile data.
You should already be comfortable with SELECT, WHERE, JOIN, GROUP BY and HAVING from the Data Analyst track. This lesson builds three tools on top.
Subqueries
A subquery is a query inside another. It answers "compared with what?" questions:
SELECT farmer_id, amount
FROM repayments
WHERE amount > (SELECT AVG(amount) FROM repayments);Common table expressions
A common table expression (CTE) names a step with WITH, so long queries read top to bottom like a recipe instead of nesting inside each other. You can chain several:
WITH recent AS (
SELECT * FROM repayments WHERE paid_on >= '2026-01-01'
),
totals AS (
SELECT farmer_id, SUM(amount) AS total FROM recent
GROUP BY farmer_id
)
SELECT * FROM totals WHERE total > 300;Window functions
GROUP BY collapses rows into one per group. A window function calculates across related rows but keeps every row. The clause says which rows: splits them into groups, sets the order inside each group.