SQL for Analytics
Beginner 12 min read SQL for Analytics

SQL for Analysts

The one language every analyst must own

SQL is the lingua franca of analytics: a declarative language that selects, filters, joins and aggregates data in the warehouse. You describe what you want, not how to compute it.

SQLSELECTWHEREwarehousedeclarativeBigQueryPostgreSQL
Before we start

What you will learn

  • Write and reason about SELECT statements
  • Filter, order and limit with precision
  • Explain why SQL is declarative and what that means
Introduction

What is this?

SQL (Structured Query Language) is a declarative language for querying relational data. Analysts use it to answer questions directly against the warehouse: SELECT what, FROM where, with filters and joins. Because it is declarative, the engine — not you — decides how to execute; you describe the result shape, and the planner optimizes the path.

Motivation

Why do you need it?

  • The warehouse holds the governed, canonical data — SQL is the only way to reach it at any scale.
  • Declarative queries are short, readable and reviewable; imperative loops would be unmaintainable.
  • One question → one query, and the same query runs against 1M or 1B rows.
  • SQL skills transfer across warehouses: PostgreSQL, BigQuery, Snowflake, DuckDB.
Think about it

Mental model

Imagine

Asking a chef to 'make me dinner from what's in the fridge'

Think of

Writing a SELECT query

You describe the meal (the result); the chef (the query planner) decides chopping order and burners. Unlike a recipe, you never micromanage the steps — you describe the dish.

Mechanics

How it works

  • SELECT lists the columns you need; * is the lazy-but-common wildcard.
  • FROM names the table or subquery; WHERE filters rows BEFORE aggregation.
  • ORDER BY sorts the result; LIMIT truncates it.
  • Query planner reads the whole statement, chooses indexes/joins, and returns rows.
  • Logical order ≠ written order: WHERE runs after FROM but before GROUP BY, even if written first.

The logical execution order of a SELECT.

FROM
WHERE
GROUP BY
HAVING
SELECT/ORDER BY/LIMIT
Hands-on

Examples

A first analytical query

A first analytical query
SELECT
  country,
  COUNT(*)            AS orders,
  SUM(amount)         AS revenue
FROM orders
WHERE date >= '2026-01-01'
GROUP BY country
HAVING SUM(amount) > 10000
ORDER BY revenue DESC
LIMIT 10;

Rows are filtered to this year, bucketed by country, aggregated, filtered again at group level, then sorted and truncated — all in one expression of intent.

Real world

In production

  • A weekly exec report query summarizes sessions by plan and day from an events table.
  • An anomaly check counts rows per day and alerts when today's count falls outside the window.
  • Self-serve dashboards execute filtered, parameterized SQL for hundreds of users.
Guidelines

Best practices

  • Always read, never mutate: analytics queries use SELECT, not INSERT/UPDATE/DELETE.
  • Be explicit with columns instead of SELECT * in shared code.
  • Filter with WHERE (rows) and HAVING (groups) — they are not interchangeable.
  • Format multi-line with clear alignment; the query is a deliverable people review.
Watch out

Common mistakes

  • Writing WHERE on an aggregate (use HAVING instead).
  • Filtering with LIKE '%term%' on a huge table and nuking performance.
  • Mixing column aliases before they're defined in the same scope.
  • Assuming results are in a guaranteed order when no ORDER BY is present.
When things break

Debugging tips

  • Run without the aggregation first — subsets can be 100x faster to inspect.
  • Add LIMIT 10 early to see structure before the full scan.
  • Confirm one known row by hand through the pipeline to sanity-check logic.
  • Diff row counts at each stage (total, filtered, grouped) to catch where rows vanish.
Interviews

Interview checkpoint

Practice

Hands-on lab

Answer Business Questions with SQLite
30 min · beginner
In progress
Objectives
  • Create a small database and import CSV data
  • Run SELECT/FILTER/GROUP BY queries that answer business questions
  • Validate one result by hand

Answer Business Questions with SQLite

30 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which clause filters rows BEFORE aggregation?

2

Analytics queries should mostly use SELECT and leave the warehouse data untouched.

Go deeper

Official documentation

PostgreSQL SELECT reference
docs
BigQuery reference
docs