SQL
Beginner 12 min read SQL

SQL for Analytics

The language you actually live in

Analytical SQL reads modeled data with confidence: selecting clean columns, filtering, aggregating and joining facts and dimensions. It is the primary language BI developers speak to their warehouse.

SQLSELECTaggregatesanalyticswarehousequery
Prerequisites: SQL for Analysts
Before we start

What you will learn

  • Write correct SELECT/FROM/WHERE/GROUP BY/ORDER BY for analytics
  • Explain the difference between logical and physical query order
  • Build a query that a BI tool can consume as a dataset
Introduction

What is this?

Analytical SQL is the subset of SQL used for reading and shaping data for reports — selecting relevant columns, filtering to the right scope, grouping and aggregating, and joining facts to dimensions. Because warehouses are columnar and SQL is declarative, the engine decides how to execute; your job is to say exactly what you need.

Motivation

Why do you need it?

  • Nearly every BI tool compiles down to SQL somewhere — mastering SQL makes the tool transparent.
  • SQL is portable across warehouses; models and reports built in SQL survive tool migrations.
  • Correctness in analytics is mostly a question of scope and grain, and SQL expresses both.
  • Performance starts with the query shape, not the tool's cache.
Think about it

Mental model

Imagine

Ordering precisely at a restaurant kitchen

Think of

Writing analytical SQL

You describe the dish (columns), dietary limits (WHERE), how many plates (GROUP BY) and the sequence (ORDER BY). The kitchen picks the equipment and order of cooking — you don't micromanage the flame, you specify the outcome.

Mechanics

How it works

  • SELECT lists the columns or expressions you want; the engine scans only those (columnar win).
  • WHERE filters rows before any grouping, trimming the data used downstream.
  • GROUP BY collapses rows per the specified dimensions; aggregations (SUM, COUNT, AVG) compute over each group.
  • HAVING filters groups after aggregation (unlike WHERE).
  • ORDER BY sorts the final result — often cheap at the end and expensive if overused.
  • Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Physical execution may differ.

Logical processing order of a query.

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Hands-on

Examples

Read a modeled fact table

Read a modeled fact table
SELECT
  d.month,
  c.region,
  COUNT(DISTINCT f.order_id)  AS orders,
  SUM(f.gmv)                  AS gmv
FROM fact_orders f
JOIN dim_date     d ON f.date_key  = d.date_key
JOIN dim_customer c ON f.cust_key  = c.cust_key
WHERE f.gmv > 0
  AND d.month >= '2026-01'
GROUP BY d.month, c.region
ORDER BY d.month, c.region;

This reads one fact and its dimensions, filters to positive GMV and a date window, aggregates to monthly-regional grain, and orders it for a table visual. The BI tool consumes the result set directly.

Real world

In production

  • A weekly region sales report is a single GROUP BY query over a fact table.
  • Data checks in BI pipelines are SQL assertions: expected counts, no duplicates, no null keys.
  • BI tools import models built in SQL (views, dbt models) so reports never parse raw production tables.
Guidelines

Best practices

  • Filter early and at the right level — WHERE before GROUP BY where possible.
  • Use COUNT(DISTINCT ...) deliberately; it is expensive but correct on high-cardinality keys.
  • SELECT only needed columns; never SELECT * on a warehouse.
  • Write the expected grain into the query so reviewers can verify it at a glance.
Watch out

Common mistakes

  • Using HAVING to filter rows that WHERE could filter — slower and confusing.
  • Grouping and then re-aggregating in the BI tool, creating nested duplication.
  • Joining facts to facts without a clear grain, silently multiplying rows.
  • Forgetting NULL handling in WHERE (NULL never equals anything).
Root cause analysis

Common issues

Error
Query returns far more rows than expected
Root cause

A join to a many-sided table multiplied the grain

Fix

Verify the grain target and join at the right level (dedupe dimensions first).

Error
COUNT(DISTINCT ...) is extremely slow
Root cause

High-cardinality column scanned over a huge window

Fix

Pre-aggregate in a model or restrict the date window early.

When things break

Debugging tips

  • Run the inner scope first: SELECT .. FROM fact WHERE .. to verify row counts before grouping.
  • Check grain: if orders > expected, a dimension join multiplied rows.
  • Test filters on a small date window before scanning history.
  • COUNT(*) vs COUNT(column) — the latter skips NULLs and surprises people.
Interviews

Interview checkpoint

Practice

Hands-on lab

Query a Star Schema Like a BI Developer
35 min · beginner
In progress
Objectives
  • Write a grouped aggregation over facts and dimensions
  • Verify grain before trusting the numbers
  • Use WHERE vs HAVING correctly

Query a Star Schema Like a BI Developer

35 min · beginner

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

Quick quiz

0/2
1

Which clause removes rows from a GROUP BY result set?

2

COUNT(column) and COUNT(*) always return the same value.

Go deeper

Official documentation

SQL Fiddle basics
docs
PostgreSQL SELECT documentation
docs