SQL for Data Analysis: The Queries Analysts Use Daily

At 4:40pm on a Thursday somebody types into Slack: "quick one, how many customers who signed up in June are still paying?" Twenty minutes later the analyst replies: 412. The next morning finance says the number is closer to 380. Both queries were syntactically perfect. One of them joined customers to invoices and counted the same customer once for every invoice they had paid. That single mistake, a join that quietly multiplied rows, is the most expensive bug in SQL for data analysis, and it never throws an error.

That is why a list of syntax keywords is a poor curriculum. The parts of SQL that decide whether your number is right are narrow and specific: how joins change row counts, how grouping defines the grain of your result, how window functions let you rank and accumulate without leaving the query, how dates get truncated and bucketed, and how a common table expression keeps the whole thing legible six months later when someone asks you to explain it. Call it the eighty percent in the Pareto sense: a small slice of the language that carries almost all of the daily work.

This guide walks through that slice, with every pattern framed as a question a business actually asks and the query written underneath it.

The eighty percent of SQL for data analysis that pays the rent

If you watched a working analyst for a week, you would see the same shapes over and over. Filter a table down to a period. Join it to one or two other tables. Aggregate to a grain someone cares about, usually per week, per plan, per rep, or per cohort. Rank or accumulate inside groups. Fill in the empty buckets so a quiet week shows a zero instead of vanishing. Wrap the whole thing in named steps so it can be read.

Everything else, recursive queries, pivot syntax, lateral joins, stored procedures, is real and occasionally necessary, but it is not what makes or breaks a Tuesday. The most common failures in data analysis using SQL are not exotic. They are a duplicated row, a filter placed in the wrong clause, a timezone that shifts a day boundary, and a query nobody can reread.

Before you write any of it, know what your data looks like. A query written against a table you have never profiled produces a confident wrong answer faster than a spreadsheet does. The habits in Exploratory Data Analysis: Steps, Methods, and Examples come first: check row counts, check nulls, check whether the key you are about to join on is actually unique.

Joins are where SQL for data analysis goes wrong first

A join does not just attach columns. It changes how many rows you have. If the right hand table has more than one row per key, your left hand rows get duplicated, and every aggregate downstream is now inflated. Nothing warns you.

Here is the Thursday afternoon bug in its natural habitat.

-- Wrong: a customer with three paid invoices is counted three times
select count(*) as customers
from customers c
join invoices i on i.customer_id = c.id
where i.paid_at >= date '2026-06-01'
  and i.paid_at <  date '2026-07-01';

Two fixes. The lazy one is count(distinct c.id), which works but hides the fan out from anyone reading the query later. The honest one is to collapse the right hand table to one row per key before you join, which also makes the intent obvious.

-- Right: reduce to one row per customer first, then count
with june_payers as (
  select distinct customer_id
  from invoices
  where paid_at >= date '2026-06-01'
    and paid_at <  date '2026-07-01'
    and status = 'paid'
)
select count(*) as customers
from june_payers;

The second classic is the left join that silently becomes an inner join. If you filter a left joined table in the where clause, rows where the join produced nulls get thrown away, and your "all customers, with their June orders" result quietly becomes "only customers who ordered in June".

-- Silently an inner join: customers with zero June orders disappear
select c.id, count(o.id) as orders
from customers c
left join orders o on o.customer_id = c.id
where o.created_at >= date '2026-06-01'
group by c.id;

-- Correct: the period filter belongs in the join condition
select c.id, count(o.id) as orders
from customers c
left join orders o
  on o.customer_id = c.id
 and o.created_at >= date '2026-06-01'
group by c.id;

A useful habit: before joining, run select key, count(*) from t group by key having count(*) > 1 limit 10 on both sides. Five seconds, and it tells you which of the four cardinalities you are actually in.

This is also where system boundaries hurt. Joining a CRM export to a billing table means reconciling two different ideas of what a customer is, which is less a SQL problem than a data modelling one. CRM Database: What It Stores and Where It Falls Short explains why the identifiers rarely line up cleanly.

Aggregation and the grain of your answer

Every result set has a grain: the thing one row represents. Getting this wrong is the second most common source of arguments about numbers. "Revenue by plan" is one row per plan. "Revenue by plan by month" is one row per plan per month. If your group by and your mental model disagree, your chart will look plausible and be wrong.

Three counting functions that people use interchangeably and should not:

• count(*) counts rows, including rows where every column is null.
• count(column) counts rows where that column is not null, which makes it a stealth null check.
• count(distinct column) counts unique values, and is the patch people reach for after a fan out.

Conditional aggregation is the workhorse that replaces most of the subqueries beginners write.

select
  plan,
  count(*) as subscriptions,
  count(*) filter (where status = 'active') as active_subs,
  sum(mrr_cents) / 100.0 as mrr,
  round(avg(mrr_cents) / 100.0, 2) as avg_mrr
from subscriptions
group by plan
having count(*) >= 10
order by mrr desc;

The filter clause is Postgres and DuckDB syntax. Everywhere else, write sum(case when status = 'active' then 1 else 0 end), which does the same job and is portable across every engine you will meet.

Two more things worth internalising. where filters rows before grouping, having filters groups after, and using having for a row level condition forces the engine to aggregate data you were going to discard. And avg ignores nulls rather than treating them as zero, so an average over a column that is null for half your rows is an average over the other half, which is sometimes what you want and often not.

Window functions: cohorts, running totals, and ranking

The moment SQL for data analytics stops feeling like a reporting language and starts feeling like an analysis language is when window functions click. A window function computes across a set of rows related to the current row without collapsing them, so you keep detail and get the aggregate side by side.

Running totals are the gateway drug. Note the aggregate wrapped in a window function, which looks strange the first time and is completely legal.

select
  date_trunc('week', paid_at)::date as week,
  sum(amount_cents) / 100.0 as revenue,
  sum(sum(amount_cents)) over (order by date_trunc('week', paid_at)) / 100.0 as cumulative_revenue
from payments
where status = 'succeeded'
group by 1
order by 1;

Deduplication is the pattern you will use most. Pick the latest row per entity, and only the latest.

select customer_id, plan, updated_at
from (
  select
    s.*,
    row_number() over (partition by s.customer_id order by s.updated_at desc) as rn
  from subscriptions s
) ranked
where rn = 1;

Use row_number when you want exactly one row per group even under ties. Use rank when ties should share a position and leave a gap, and dense_rank when ties share a position without a gap. Choosing rank for deduplication is a quiet way to reintroduce the duplicates you were trying to remove.

Period over period comparison stops needing a self join once you know lag.

with monthly as (
  select date_trunc('month', paid_at)::date as month,
         sum(amount_cents) / 100.0 as revenue
  from payments
  where status = 'succeeded'
  group by 1
)
select
  month,
  revenue,
  lag(revenue) over (order by month) as prior_month,
  round(100.0 * (revenue - lag(revenue) over (order by month))
        / nullif(lag(revenue) over (order by month), 0), 1) as pct_change
from monthly
order by month;

nullif in the denominator is not decoration. It is the difference between a null in one cell and a division by zero error that kills the whole query.

Cohort retention, the question every subscription business asks, is two aggregations and a join. First month of activity per customer, then all active months per customer, then the offset between them.

with first_month as (
  select customer_id, min(date_trunc('month', created_at)) as cohort_month
  from orders
  group by 1
),
activity as (
  select distinct customer_id, date_trunc('month', created_at) as active_month
  from orders
)
select
  f.cohort_month::date as cohort,
  (extract(year  from a.active_month) - extract(year  from f.cohort_month)) * 12
  + (extract(month from a.active_month) - extract(month from f.cohort_month)) as month_offset,
  count(distinct a.customer_id) as customers
from first_month f
join activity a on a.customer_id = f.customer_id
group by 1, 2
order by 1, 2;

That result is long and thin, one row per cohort per offset. Pivoting it into the familiar triangle is a job for whatever renders the output, not for SQL.

Dates, truncation, and the buckets that lie to you

More wrong numbers come from dates than from any other data type, and almost all of them are avoidable with three habits.

Use half open intervals. Write where created_at >= date '2026-06-01' and created_at < date '2026-07-01'. The between operator is inclusive on both ends, so on a timestamp column it either misses everything after midnight on the last day or double counts a boundary row depending on how the data is stored.

Truncate deliberately, and know your timezone. date_trunc('week', ts) gives you Monday in Postgres and Sunday in some other engines. If your timestamps are stored in UTC and your business runs in another timezone, convert before truncating, or your Monday will start at the wrong hour and every weekly comparison will be slightly off.

Fill your gaps. A group by week only returns weeks that had rows. A week with zero signups does not appear, the line chart connects straight through it, and the drop becomes invisible. Generate the calendar and left join to it.

with weeks as (
  select generate_series(date '2026-01-06', date '2026-07-27', interval '1 week')::date as week_start
)
select
  w.week_start,
  count(s.id) as signups
from weeks w
left join signups s
  on s.created_at >= w.week_start
 and s.created_at <  w.week_start + interval '1 week'
group by w.week_start
order by w.week_start;

count(s.id) rather than count(*) matters here. With a left join and no matching rows, count(*) returns 1 for the phantom row and your empty week reports a signup that never happened.

CTEs and the query you can still read in six months

A common table expression is a named intermediate result declared with with. Its value is not performance. Its value is that a stranger, including future you, can read the query top to bottom and understand what each step produces.

Three rules keep CTEs useful rather than decorative.

One idea per CTE, named for its output. paid_invoices_june, first_touch_per_deal, weekly_revenue. Never t1, t2, final. The name is documentation that cannot drift out of date.

Comment the grain. A single line above each CTE saying "one row per customer per month" prevents most join mistakes before they happen, because the moment two CTEs at different grains meet you will notice.

Do not chain forever. Past roughly six or seven steps, a CTE stack becomes as hard to follow as the nested subqueries it replaced. That is the signal to persist an intermediate result as a table or a view, which is a modelling decision, not a query decision. If several people keep rebuilding the same intermediate step in their own queries, the step belongs in the warehouse. Enterprise Data Warehouse: Concept, Examples, and Cost covers what that move costs and when it is worth it.

One performance caveat worth carrying: in older Postgres versions a CTE was an optimisation fence, materialised whether or not that helped. Modern versions inline them unless you write materialized. In most other engines they are inlined and behave like subqueries. If a CTE heavy query is slow, read the execution plan before rewriting it out of superstition.

The question, the pattern, and the trap

Most requests an analyst receives map to one of a small number of shapes. Recognising the shape is most of the job.

Every one of those is a few lines of SQL. The skill in sql and data analysis is not typing them, it is knowing which one the question is really asking, and asking the requester before you write anything.

Where a query is the right answer, and where it is not

Now the honest part, because it changes how an analyst spends a week.

Look at the requests arriving in your queue. A portion of them genuinely need SQL: anything with a definition that must be consistent across the company, anything joining several tables at volume, anything that will be repeated, audited, or argued about. Those belong in a governed model, written once, reviewed, and reused. When the definitions themselves start to drift, that is a discovery and documentation problem, and Data Catalog Tools: Do You Need One at Your Data Size? is a fair test of whether you have hit that threshold yet.

But a large share of the requests analysts get are not analysis at all. They are lookups. Did that invoice actually get paid. Which deals slipped out of this month. How many support tickets came from that account since April. What did we spend on ads last week. Those questions often do not touch the warehouse, because the answer lives in Stripe, HubSpot, Zendesk, or an ad platform, and the person asking is only routed to the analyst because they do not have a fast way to ask the system directly.

That is the gap Skopx sits in. Skopx is an AI workspace that connects nearly 1,000 tools a company already uses, and answers questions in chat with cited data pulled from those tools. Someone asks about an invoice, a deal, or a ticket, and gets the answer with the source attached, without an analyst writing anything. Alongside that there is a morning brief, an insights engine that surfaces risks and anomalies, and workflows you build by describing them in chat rather than scheduling scripts. Solo runs on your own key for any major model at zero markup, Team includes 2.3 million AI tokens per seat every month with no key needed, and pricing is $5 per month for Solo and $16 per seat per month for Team.

Now the part that matters more, the boundaries. Skopx does not teach SQL and does not replace it. It is not a data warehouse, not an ETL tool, not a dashboard builder, and not a CRM. It will not model your metrics, it will not give you a governed definition of active customer, and it will not run a cohort query across a hundred million rows. If your question needs a join across modelled tables with a definition finance will sign off on, write the SQL. Nothing here changes that.

What it changes is the mix. The lookups leave the analyst's queue, and the queries that survive are the ones that deserved a query in the first place. Teams that live inside a CRM tend to feel this first, which is the argument made in Sales Analytics CRM: Get Answers Without Building Reports, and finance teams feel it around reconciliation, where the mechanics are laid out in Stripe QuickBooks Integration: Reconciling Fees and Payouts.

Weekly revenue check before the query queue opens

Monday 07:00

Runs before the week's requests arrive

Pull payments

Last week and the week before from billing

Pull closed deals

Same two periods from the CRM

Compare periods

Week over week change per plan

Moved more than the threshold?

Quiet weeks produce no message

Post to Slack with sources

Numbers plus links to the records behind them

A checklist before you send the number

Five checks, under two minutes, and they catch nearly everything an analyst gets embarrassed by.

1. Row count sanity. Run the query without aggregation and check the row count against a known total. If joining a table doubled your rows, stop.
2. Null audit. For every column in a filter or a join, check how many rows are null. Nulls do not match anything, including other nulls, and they silently shrink results.
3. Boundary check. Confirm the period is half open and the timezone is the one the business thinks in.
4. Spot check one record. Take a single customer from the result and verify their number by hand in the source system. One record, every time.
5. Reread the request. Did they ask for customers or accounts, orders or revenue, signed up or activated? Most disputed numbers are correct answers to a question nobody asked.

Then write down the definition you used in one sentence and send it with the number. It costs you a line and saves the follow up thread. If the definition keeps changing between requests, the problem is organisational rather than technical, which is the territory covered in CRM Strategy: Making the System Stick After You Roll Out.

Frequently asked questions

Do I still need SQL if the company already has a BI tool?

Yes, and for a specific reason. A BI tool answers questions someone already anticipated when they built the model. The interesting questions are the unanticipated ones, and those need direct access. In practice the strongest analysts use both: the governed model for anything reported publicly, and ad hoc SQL for anything being investigated. Learning SQL for a data analyst role is less about escaping the BI tool and more about being able to check it.

Which SQL dialect should I learn first?

Postgres. It is the closest thing to a lingua franca, its window function and date handling syntax is standard enough to transfer, and it is what most warehouses resemble. Once you are comfortable, moving to BigQuery, Snowflake, Redshift, or DuckDB is a matter of learning that engine's date functions and a handful of quirks. The differences that actually bite are date truncation, string concatenation, and how each engine handles nulls in sorts.

Which SQL data analysis tools do analysts use day to day?

Most working setups are some combination of a query editor with autocomplete and saved queries, a notebook environment when the analysis needs charting or Python alongside the query, and a version controlled repository for anything that gets reused. The tool matters far less than two habits: saving queries somewhere searchable, and writing down the metric definition next to the query. Every serious SQL data science workflow eventually converges on those two, whatever software is in the middle.

Are window functions worth learning if I can do the same thing with a self join?

Almost always. Self joins to compute a previous value or a running total are slower, longer, and much easier to get subtly wrong when there are ties or gaps in the sequence. Window functions also keep detail rows intact, which means you can inspect the underlying records instead of trusting a collapsed aggregate. The learning curve is roughly an afternoon, and it pays back immediately in cohorts, rankings, and period comparisons.

How do I know my number is right before I send it?

Verify one record by hand in the source system. It is unglamorous and it catches fan out, timezone shifts, status filters, and wrong grain all at once. Then check whether your result reconciles to a total someone else already publishes, and if it does not, find out why before you send it rather than after. Being able to explain a difference is more valuable than matching by accident.

Where does SQL stop being the right tool?

When the question is a one off lookup against a system that is not in the warehouse, when the answer needs to reach a non technical person immediately, or when the same question gets asked every week by different people. The first two are better served by asking the connected system directly. The third is an automation, and it should run on a schedule without anyone opening an editor. Reserve SQL in data analytics for what only SQL can do: consistent, joined, defensible numbers across modelled data.