Getting started

SQL for Data Analysts: The Queries Analysts Run

By Owen Middleton · Updated November 2026 · We ran the examples on PostgreSQL 17

In SQL for data analysts, you write SELECT queries to check, combine and summarize data for a report. The four queries below use COUNT, JOIN, GROUP BY, CASE and a window function (SUM with OVER).

Below are four queries, one for each task, with their results. You can change one of the queries and run it on this page.

We make SQLMaxx Learn, a free SQL course, so we have an interest in your answer.

What does data analyst SQL look like on real data?

The four examples are on the data of Streamhub, a made-up B2B SaaS analytics company. The data is in three tables: users (80 rows, one for each user of the product), sessions (180 rows, one for each session of a user) and events (200 rows, one for each action of a user). For the first two queries, you need only lessons from the Foundations and Core SQL tiers of our course, so a beginner can write them.

The Streamhub tables: users, sessions and events

Schema · analytics3 tables? = nullable

users

idinteger

nametext

emailtext

countrytext

plantext

signed_up_attimestamptz

is_activeboolean

sessions

idinteger

user_id?integer

started_attimestamptz

ended_at?timestamptz

event_countinteger

events

idinteger

user_id?integer

session_id?integer

event_typetext

occurred_attimestamptz

properties?jsonb

Check the data: sessions with no end time

In the sessions table, ended_at is the end time of a session. In Streamhub, a NULL ended_at means that the session is still open. Before you calculate the length of the sessions, count the open sessions:

SELECT COUNT(*) AS sessions,
       COUNT(ended_at) AS ended,
       COUNT(*) - COUNT(ended_at) AS still_open
FROM sessions;

sessions	ended	still_open
180	168	12

sessions, ended and still open

With COUNT(*), you count all rows. With COUNT(ended_at), you count only the rows where ended_at is not NULL. So in the Streamhub data, 12 of the 180 sessions are still open. Decide what to do with these 12 sessions before you report an average session length.

Combine two tables: active users per plan

The plan of each user is in users, and the actions are in events. Here, an active user is a user with one or more events. To count the active users on each plan, join the two tables on the user id:

SELECT u.plan,
       COUNT(DISTINCT e.user_id) AS active_users,
       COUNT(*) AS events
FROM users u
JOIN events e ON e.user_id = u.id
GROUP BY u.plan
ORDER BY active_users DESC, u.plan;

plan	active_users	events
pro	14	52
enterprise	10	88
free	10	17
starter	9	43

active users and events on each plan

In the Streamhub data, 43 of the 80 users have one or more events. This is not the is_active column of users. At Streamhub, the enterprise plan has the most events: 88 events from 10 users. With COUNT(DISTINCT e.user_id), you count each user one time, also when a user has many events. To join three or more tables, see Joining Multiple Tables explained in lesson 22.

Change the query here. For example, replace each u.plan with u.country to count the active users in each country:

SELECT u.plan,
       COUNT(DISTINCT e.user_id) AS active_users,
       COUNT(*) AS events
FROM users u
JOIN events e ON e.user_id = u.id
GROUP BY u.plan
ORDER BY active_users DESC, u.plan;

Follow a number over time: users to date

In our view, analysts often report a number for each period and a total to date. With this query, you get the new users in each year of signed_up_at, and the number of all users up to the end of that year:

SELECT EXTRACT(YEAR FROM signed_up_at)::int AS year,
       COUNT(*) AS new_users,
       SUM(COUNT(*)) OVER (ORDER BY EXTRACT(YEAR FROM signed_up_at)::int) AS users_to_date
FROM users
GROUP BY EXTRACT(YEAR FROM signed_up_at)::int
ORDER BY year;

year	new_users	users_to_date
2022	28	28
2023	28	56
2024	24	80

new users and users to date, by year

With COUNT(*), you get the new users of each year. With SUM(COUNT(*)) OVER (ORDER BY …), you add the count of each year to the counts of the years before it. At Streamhub, by the end of 2024, 80 users signed up. See how Running Totals and Cumulative Metrics works in lesson 64. In our view, analysts also often compare each period with the period before it.

Count each step: a product funnel

Each row in events has an event_type, for example page_view or purchase. To see how many users did each step, count the different users for each event type. With CASE in ORDER BY, you sort the rows in the order of the steps:

SELECT event_type,
       COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_type
ORDER BY CASE event_type
           WHEN 'page_view' THEN 1
           WHEN 'feature_used' THEN 2
           WHEN 'upgrade_clicked' THEN 3
           WHEN 'checkout' THEN 4
           WHEN 'purchase' THEN 5
         END;

event_type	users
page_view	43
feature_used	28
upgrade_clicked	28
checkout	20
purchase	19

users who did each step

In the Streamhub data, 43 users have a page view, and 19 users have a purchase. In this result, you see the users who did each step. You do not see whether each user did the steps in order. For a funnel in order, see lesson 65 of our course.

What does a data analyst do with SQL?

A data analyst uses SQL to get the data for a question and to make it ready for a report. Two public job standards give a description of the work of a data analyst. The two standards do not name SQL for a data analyst. ESCO (v1.2.1) lists "query languages", the group that SQL is in, as essential knowledge.

ESCO, the job classification of the European Commission, starts its description with this sentence: "Data analysts import, inspect, clean, transform, validate, model, or interpret collections of data with regard to the business goals of the company" (ESCO v1.2.1, occupation "data analyst").

In the Skills England standard for a data analyst (Level 4), the duties include "Collect, compile and, if needed, cleanse data", "Produce performance dashboards and reports" and "Identify, analyse, and interpret trends or patterns in data sets" (Skills England, Data analyst (Level 4)). The word "SQL" is not in this standard.

To use SQL as a data analyst, you write one query for each question:

Check the data ("inspect", "validate", "cleanse"): count the rows and the NULL values before you use a table.
Combine tables ("compile", "transform"): with a JOIN, you put data from two or more tables in one result.
Follow a number over time ("trends or patterns"): get a total for each period, and a running total.
Count each step: count the users at each step of a product, from the page view to the purchase.

Why use SQL for data analysis? In our view, with SQL you get the data from the database directly, and you can save a query and run it again on new data.

Is SQL for data analysis or only for databases?

SQL is for data analysis and also for the work on databases. Analysts write queries to read data. SQL also has commands to change data, for example INSERT, UPDATE and DELETE, and commands to make and change tables. In our view, database administrators and developers use these commands more than analysts do.

Each query on this page is a SELECT query, so with each query you only read data. To see how much SQL an analyst needs, read how much SQL a data analyst needs.

Where does a data analyst stop using SQL?

In our view, an analyst uses SQL to get the data and to summarize it, and then uses other tools for the next steps. ESCO says that data analysts "might prepare reports in the form of visualisations such as graphs, charts, and dashboards". The ESCO description does not name a tool. ESCO lists "data visualisation software" as essential knowledge, but it names no product.

Charts and dashboards: in our view, analysts usually make these in a BI tool or in a spreadsheet such as Excel, with the result of a query as the data.
Statistics and models: in our view, analysts often do this work in Python or R.

Start SQLMaxx Learn lesson 1

SQLMaxx Learn is the 66-lesson course. Lesson 1 is "SELECT and Column Expressions", and you can start it in your browser with no account.

In 40 of the 66 lessons, some problems use the same Streamhub data as this page, and you press "Check answer" to check each answer, but the course teaches only PostgreSQL and has no case-study project.

Start lesson 1

Questions about SQL for data analysts

Is SQL a coding language?

Yes. SQL is a language that you write as code, for one job: to work with the data in a database. ESCO describes SQL as "a query language for retrieval of information from a database and of documents containing the needed information". In our view, most of the SQL that a data analyst writes is SELECT queries. Source: ESCO v1.2.1, skill "SQL".

Is SQL for data analysis different from SQL for developers?

The language is the same. In our view, the commands that you use most are different: analysts mostly read data with SELECT, and developers also change data with INSERT, UPDATE and DELETE.

What does a data analyst use besides SQL?

ESCO says that data analysts "use different algorithms and IT tools as demanded by the situation and the current data". The ESCO description does not name the tools. In our view, the usual tools besides SQL are a spreadsheet, a BI tool for dashboards and, for some jobs, Python. Source: ESCO v1.2.1, occupation "data analyst".