SQL for data analysis: a practical guide to the queries that do the work

SQL for data analysis is the practice of using a small, stable set of query patterns to answer questions from a database: filtering rows, grouping and aggregating, joining tables, and computing rates and trends over time. You do not need the whole language. Roughly ten constructs cover the large majority of analysis work, and much of an analyst’s skill is knowing which pattern fits the question in front of them.

If you can read a little SQL, you have enough to start. The sections below cover how analysis SQL differs from the SQL developers write, the constructs worth learning in order, the query patterns you will reuse constantly, the mistakes that produce wrong numbers, and when SQL stops being the right tool.

Analysis SQL is not application SQL

Most SQL tutorials teach the language an application developer uses: inserting a user, fetching one order by ID, updating a row inside a transaction. Analysis SQL is a different dialect of the same language, and confusing the two is why a lot of people find analytics queries harder than they should be.

Application SQL touches a few rows at a time, cares about concurrency and indexes, and gets embedded in code that runs thousands of times a second. Analysis SQL scans many rows at once, collapses them into aggregates, and gets read and rewritten by a person who is iterating toward an answer. The priorities are almost inverted.

If you learned SQL to build software, you already know the syntax, but analysis calls on different skills. You will lean on aggregation, date handling, and window functions far more than on transactions or single-row lookups.

The SQL you actually need for analysis

You can do serious analysis with a short list of constructs. Learn them roughly in this order, because each one lets you answer questions the previous ones could not.

1. SELECT and WHERE to pull and filter rows. This is how you scope a question to the right slice of data.
2. GROUP BY with aggregates (COUNT, SUM, AVG, MIN, MAX). This is the heart of analysis: turning many rows into one number per group.
3. ORDER BY and LIMIT to rank and to look at the top or bottom of a result.
4. JOIN to combine facts with the dimensions that describe them, like joining orders to customers.
5. CASE to bucket values, build flags, and compute conditional aggregates.
6. Date functions, especially DATE_TRUNC, to group events into days, weeks, and months for trends.
7. CTEs (WITH) to name intermediate steps so a multi-step query stays readable.
8. Window functions (ROW_NUMBER, LAG, SUM() OVER (...)) for running totals, rankings, and period-over-period change.

Those eight cover most analysis work. Subqueries, HAVING, UNION, and set operations fill in the edges.

The query patterns you will reuse constantly

Analysis is less about writing novel SQL and more about recognizing which known pattern answers the question. The patterns below are worth memorizing. They are written in standard SQL that works with minor changes across PostgreSQL, BigQuery, Snowflake, and Redshift.

Count and rate by group

The most common analysis question is “how many, broken down by something.” Add a ratio and it becomes far more useful than a raw count.

SELECT
  plan,
  COUNT(*) AS accounts,
  COUNT(*) FILTER (WHERE status = 'active') AS active_accounts,
  ROUND(
    COUNT(*) FILTER (WHERE status = 'active') * 100.0 / COUNT(*),
    1
  ) AS active_pct
FROM accounts
GROUP BY plan
ORDER BY accounts DESC;

Trend over time

Bucket a timestamp into a period and aggregate. DATE_TRUNC is the workhorse here.

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS signups
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Scope to a cohort, then aggregate

When the question involves “for the users who did X, what is Y,” name the cohort in a CTE first. It keeps the logic readable and lets you reuse the cohort in later steps.

WITH q1_signups AS (
  SELECT id
  FROM users
  WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
  COUNT(DISTINCT o.user_id) AS purchasers,
  SUM(o.amount) AS revenue
FROM orders o
JOIN q1_signups s ON s.id = o.user_id;

Join facts to dimensions

Facts (orders, events, sessions) rarely carry the labels you want to group by. Join them to dimension tables (customers, products) to get readable breakdowns.

SELECT
  c.country,
  SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country
ORDER BY revenue DESC;

Running totals and rankings with window functions

Window functions compute a value across a set of rows related to the current row, without collapsing them. Use them for cumulative sums and for ranking within a group. For a deeper treatment, see our guide to SQL window functions.

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_revenue
ORDER BY month;

Period-over-period change

LAG pulls the previous row’s value onto the current row, which is how you compute growth without a self-join.

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue
ORDER BY month;

Latest row per group (deduplication)

To get the most recent record for each entity, number the rows per group and keep the first. This is the standard approach for tables that store history.

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY subscription_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM subscription_events
)
SELECT *
FROM ranked
WHERE rn = 1;

Bucketing with CASE

CASE turns a continuous value into readable categories, which makes distributions far easier to interpret than raw numbers.

SELECT
  CASE
    WHEN amount < 50 THEN 'under_50'
    WHEN amount < 200 THEN '50_to_200'
    ELSE 'over_200'
  END AS order_size,
  COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

Common mistakes that produce wrong numbers

Analysis SQL rarely throws an error when it is wrong. It returns a confident number that happens to be incorrect. These mistakes cause most of those wrong numbers.

• Fan-out from joins. Joining a table with a one-to-many relationship duplicates rows before you aggregate, so SUM and COUNT come out inflated. Aggregate to the right grain first, or use COUNT(DISTINCT ...).
• Averaging averages. Taking AVG of an already-averaged column does not give the true average. Recompute from the underlying rows with SUM(...) / SUM(...).
• COUNT(*) versus COUNT(column). COUNT(*) counts rows, while COUNT(column) skips nulls. Mixing them up changes denominators and breaks rates without raising an error.
• Timezone drift in date buckets. DATE_TRUNC operates in the column’s timezone. If your timestamps are UTC but your business runs in another timezone, daily counts land in the wrong day. Convert before truncating.
• Filtering out nulls by accident. WHERE status != 'churned' excludes rows where status is null, because comparisons with null are never true. Use status IS DISTINCT FROM 'churned' or handle nulls explicitly.
• WHERE versus HAVING. WHERE filters rows before aggregation, and HAVING filters groups after. Putting an aggregate condition in WHERE is an error, and putting a row condition in HAVING is slow and confusing.

A consistent style makes these easier to catch in review. Our SQL style guide covers conventions that keep team queries readable enough to audit.

A checklist for writing an analysis query

Before you trust a result, run through this. It catches most of the mistakes above.

1. Define the grain. What does one row represent in your final result? One customer, one day, one order?
2. Filter first. Scope to the right time range and segment before you aggregate.
3. Pick the right aggregate. Decide between COUNT, COUNT(DISTINCT), SUM, and AVG, and know what each denominator is.
4. Check joins for fan-out. Confirm each join is one-to-one at the grain you are aggregating, or count distinct.
5. Handle nulls on purpose. Decide what happens to missing values in filters and averages.
6. Bucket dates in the right timezone. Convert timestamps before truncating.
7. Sanity-check the totals. Do the row count and the grand total match a number you already trust from another source?
8. Name your columns. Aliased, readable output makes a query reusable and citable later.

When SQL stops being the right tool

SQL is excellent at defining what a number means. It gives you a precise, portable way to express “monthly active users” or “net revenue” once. It is a poor tool for everything that happens after the number is defined.

Rerunning a query by hand every Monday, pasting results into a spreadsheet, sharing a screenshot in Slack, and answering a stakeholder’s follow-up by rewriting the WHERE clause is where SQL alone breaks down. That work wants a layer on top of the query: saved dashboards that refresh on their own, permissions so people see only their data, and a way for non-technical teammates to ask follow-ups without waiting on you.

A BI tool handles that layer. It builds durable, shareable analytics on top of the queries you already wrote rather than replacing them. For going from queries to a dashboard a team trusts, see our guide to building a SQL dashboard.

Basedash fits this pattern for teams that live in SQL but do not want analysis to stay locked in a query editor. You write and save SQL, turn it into dashboards, and let teammates ask follow-up questions in plain language, where an AI assistant translates the question into SQL against the same database. The SQL you write stays the source of truth for what each metric means, and the tool handles the sharing, refreshing, and follow-ups that raw queries cannot.

FAQ

Do I still need to learn SQL if I have an AI tool that writes it?

Yes, but for a different reason than before. AI can draft a query from a plain-language question, which lowers the barrier to getting a first result. But you still need enough SQL to read what it produced, catch the fan-out and null mistakes above, and confirm the number means what you think. Reading and verifying SQL now matters more than writing it from scratch.

What SQL functions are most important for data analysis?

Aggregates (COUNT, SUM, AVG), GROUP BY, DATE_TRUNC for time buckets, CASE for bucketing and conditional counts, and window functions (ROW_NUMBER, LAG, and SUM() OVER). Those cover counting, trending, ranking, and period-over-period comparison, which is most of analysis.

What is the difference between SQL for analysis and SQL for developers?

Developer SQL usually reads or writes a few rows quickly inside an application and cares about indexes and transactions. Analysis SQL scans many rows, collapses them into aggregates, and is rewritten repeatedly as the question changes. The language is the same, but the patterns and priorities differ.

Which SQL dialect should I learn for analysis?

Start with standard SQL and PostgreSQL conventions. The analysis constructs (aggregates, GROUP BY, CTEs, window functions) are nearly identical across PostgreSQL, BigQuery, Snowflake, and Redshift. Dialect differences mostly show up in date functions and a few string operations, which are easy to look up when you hit them.

How much SQL do I need to be useful?

Less than most people expect. If you can filter, group and aggregate, join a fact table to a dimension, bucket dates, and use one or two window functions, you can answer the large majority of business questions. Depth in a few patterns beats broad, shallow syntax knowledge.

Is learning SQL still worth it in 2026?

Yes. SQL is the common interface to nearly every warehouse and database, and it is the layer where a metric’s definition lives. AI assistants generate SQL, dashboards run on SQL, and semantic layers compile to SQL. Understanding it lets you trust and correct everything built on top.