Short answer
- A data analyst uses SQL to answer a business question from database tables: filter to the right rows, join tables that share a key, aggregate rows into summaries, and check the result before sharing it.
- Start with SELECT and FROM to choose columns and a table, use WHERE to filter individual rows, and use ORDER BY and LIMIT to inspect a manageable slice while you are still exploring.
- Use joins to combine tables on a shared key. An inner join keeps only matching rows, while a left join keeps every row from the first table and fills unmatched columns with NULL.
- Use aggregate functions such as COUNT, SUM, and AVG with GROUP BY to summarize rows by category, and use HAVING, not WHERE, to filter on an aggregated result.
- Use a subquery or a common table expression (CTE) to break a multi step question into named, readable stages, and use window functions such as ROW_NUMBER and RANK when you need a calculation that keeps every row instead of collapsing rows into groups.
- Treat checking as part of the work: count rows, reconcile totals, look for NULL values and duplicates, and compare a new result against a figure you already trust before you share it.
A data analyst rarely opens SQL to admire the language. The analyst opens SQL because someone has asked a question that a spreadsheet export cannot answer cleanly: how many customers came back a second time, which region grew fastest last quarter, or why two reports of the same total disagree. SQL is the tool for reaching into the database where the records already live, pulling out exactly the rows that matter, combining tables that belong together, summarizing the result, and checking it before anyone acts on it.
This article explains that workflow. It is not a course and it does not try to teach every SQL command. It covers the skills analysts use most often, in the order they usually use them: finding and filtering rows, joining tables, aggregating and grouping, shaping results with subqueries and common table expressions, adding row level calculations with window functions, and cleaning and validating data along the way. Where a topic has its own detailed explainer on this site, this article points to it rather than repeating it.
The examples use one small, invented dataset so every result can be checked by hand. There are two tables. The customers table holds one row per customer, with a customer id, a name, a region, and a signup date. The orders table holds one row per order, with an order id, a customer id, an order date, an amount, and a status such as completed or cancelled. Nothing in the examples is presented as real company data. The point is the shape of the query, not the numbers.
SHORT-ANSWER BOX
The Analyst Workflow: Question First, Query Second
Good SQL work starts before any code is written. The analyst turns a vague request into a precise question, then works out which tables can answer it. “How are sales doing” is not yet a query. “What was total completed order revenue by region in the first quarter of 2026, and how does it compare with the same quarter a year earlier” is much closer, because it names the measure (total completed order revenue), the grouping (region), the period, and the comparison.
The next step is to find the tables and understand them. Which table holds orders, which holds customers, and which column links them? In the example dataset, orders.customer_id links each order to customers.customer_id. Before writing a summary, an analyst usually inspects a few rows, checks the date range the table actually covers, and looks at distinct values in key columns such as status and region. That exploration prevents a common error: writing a polished query against a mistaken assumption about what a column contains.
A typical analyst query then follows a pattern. Filter early, join only what is needed, aggregate, filter the groups if needed, and sort. Keeping that order in mind helps when a query returns a surprising result, because each stage can be checked on its own. If the row count looks wrong before any join, the filter is the suspect. If the count jumps after a join, the join is the suspect. If a total looks wrong only after grouping, the grouping or the aggregate is the suspect.
One more habit separates careful analysis from fast analysis: never share the first number a query produces. Run a second, independent check on the same question, even a simple one, and confirm that the two agree. The checking section near the end of this article shows how.
Querying and Filtering: Getting the Right Rows
Every analyst query starts with SELECT and FROM. SELECT names the columns to return, and FROM names the table to read them from. Selecting only the columns you need is more than tidy style. It keeps results readable, avoids accidental exposure of columns nobody asked for, and makes it obvious what the query is actually using.
SELECT customer_id, order_date, amount
FROM orders;WHERE filters individual rows before any grouping happens. That timing matters and comes up again with HAVING later in this article. A WHERE clause can test equality, compare numbers and dates, and combine conditions with AND, OR, and NOT. For a full treatment of the comparison operators themselves, see Equal and Not Equal in SQL.
SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE status = 'completed'
AND order_date >= '2026-01-01'
AND order_date < '2026-04-01';Notice the date pattern. Writing a quarter as “from January 1 up to but not including April 1” avoids the classic boundary error of including April 1 orders in a first quarter total. Dates deserve that care in every analyst query, because a single day at the edge of a period can move a total.
Other filters analysts reach for every day include IN for a short list of values, BETWEEN for an inclusive range, LIKE for a text pattern, and IS NULL for missing values. Each answers a slightly different question:
SELECT customer_id, region
FROM customers
WHERE region IN ('North', 'South');
SELECT order_id, amount
FROM orders
WHERE amount BETWEEN 50 AND 200;
SELECT customer_name
FROM customers
WHERE customer_name LIKE 'Mar%';
SELECT order_id
FROM orders
WHERE amount IS NULL;LIKE patterns vary a little between database systems, and the percent sign and underscore have special meanings inside a pattern, so check the documentation for the system you are using before relying on a pattern in a shared report. The important analyst point is the same in every system: filtering is about excluding rows you do not want before you summarize, so that the summary describes exactly the population in the question.
ORDER BY and LIMIT are exploration tools as much as presentation tools. Sorting by amount descending and limiting to ten rows shows the largest orders at a glance. Sorting by order date shows whether recent data looks complete or whether the newest dates are missing, which can be a sign that a data load is delayed. LIMIT is a safety habit too: when exploring an unfamiliar table, limit the first query so a mistake returns hundreds of rows instead of millions.
Joins: Combining Tables That Belong Together
Analyst data is usually spread across tables on purpose. Customers live in one table and orders in another, linked by customer id, so that a customer name is stored once rather than copied onto every order. A join reassembles that information for a question that needs columns from both tables, such as revenue by customer region.
An inner join keeps only rows that match on both sides. An analyst asking for completed orders with their customer region would write:
SELECT c.region, SUM(o.amount) AS total_revenue
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region;A left join keeps every row from the first table named, even when there is no match, and fills the missing side with NULL. That difference is the whole point when the question is about absence: customers who have never ordered, or orders with no matching customer record. A left join from customers to orders, followed by a filter for NULL order ids, finds customers with no orders. For a detailed explanation of how inner joins work, including worked examples, see Inner Join in SQL.
SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;Two join mistakes account for a large share of analyst errors. The first is joining on the wrong key or on a column that is not unique, which duplicates rows and inflates totals. If one customer appears five times in a table that should hold one row per customer, every order for that customer will be counted five times. The fix is to check uniqueness before joining: count distinct keys, and compare row counts before and after the join. If the count jumps in a way the question does not explain, stop and investigate the key.
The second mistake is filtering a left join in a way that quietly turns it back into an inner join. Putting a condition on the right hand table in the WHERE clause removes the NULL rows that the left join was meant to keep, because NULL fails most comparisons. If the condition belongs to the join itself, it usually belongs in the ON clause instead. This is one of those errors that produces a plausible looking number, which is why it is worth checking deliberately whenever a left join is used.
Analysts also combine result sets with UNION, which stacks rows from two queries that return the same number and compatible types of columns. UNION removes duplicate rows by default, while UNION ALL keeps them. For combining monthly extracts or two similar tables, UNION ALL is usually the honest choice unless duplicates genuinely need removing, because silently dropping rows can hide a data problem.
Aggregation and Grouping: From Rows to Summaries
Most analyst questions are summary questions. How many orders, how much revenue, what average order value, and how do those figures break down by region or month? Aggregate functions answer that kind of question. PostgreSQL documentation describes an aggregate function as a function that computes a single result from multiple input rows, giving count, sum, average, maximum, and minimum as the standard examples. The same idea holds across database systems.
The five aggregates to learn first are COUNT, SUM, AVG, MIN, and MAX. Their NULL behavior is worth memorizing because it shapes results. COUNT(*) counts rows, including rows where a column is NULL. COUNT(column) counts only rows where that column is not NULL. SUM and AVG ignore NULL values rather than treating them as zero, which means an average over a column with missing values is an average of the values present, not an average over all rows. That distinction matters whenever missing data is common.
SELECT
COUNT(*) AS order_count,
COUNT(order_date) AS order_date_count,
SUM(amount) AS total_revenue,
AVG(amount) AS average_order_value,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders
WHERE status = 'completed';GROUP BY turns one summary into one summary per group. Grouping by region gives one row per region; grouping by region and month gives one row per region per month. Every column in the SELECT list that is not inside an aggregate function should normally be a grouping column, so that each output row describes exactly one group.
SELECT c.region, COUNT(*) AS order_count, SUM(o.amount) AS total_revenue
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
ORDER BY total_revenue DESC;Filtering after grouping needs HAVING, not WHERE. PostgreSQL documentation draws the line clearly: WHERE selects input rows before groups and aggregates are computed, while HAVING selects group rows after groups and aggregates are computed. So a condition on a raw column, such as status, belongs in WHERE, while a condition on a count or a sum, such as regions with more than 100 orders, belongs in HAVING. For a full explainer of that clause, see Having by in SQL.
SELECT c.region, COUNT(*) AS order_count, SUM(o.amount) AS total_revenue
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
HAVING COUNT(*) > 100
ORDER BY total_revenue DESC;A related analyst pattern is counting the counts: first count something per group, then count or summarize those counts. How many customers placed exactly two orders, or how many days had more than ten orders, cannot be answered by a single COUNT. It needs a grouped count in an inner query and a second count in the outer query. That two level pattern is explained in detail, with worked examples, in Count of Count in SQL.
Grouping also has a trap worth naming: grouping by a column that looks like a category but is actually free text. Region values such as “North”, “north”, and “North ” with a trailing space will form three separate groups unless the data is standardized first. The cleaning section below returns to this problem.
Shaping Results with CASE
Not every grouping column exists in the data. Sometimes the analyst has to create the category inside the query. CASE is the tool for that: it tests conditions row by row and returns a value, much like an if statement in other languages. A common use is banding a numeric value, for example labeling orders as small, medium, or large by amount, and then grouping by that label.
SELECT
CASE
WHEN amount < 50 THEN 'Small'
WHEN amount < 200 THEN 'Medium'
ELSE 'Large'
END AS order_size,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_size
ORDER BY order_size;CASE is also useful for conditional counting and summing, for example counting completed orders and cancelled orders in the same query by summing a CASE that returns 1 or 0. And it is the standard way to handle a value that needs relabeling for a report without changing the stored data. For a detailed guide to CASE syntax and worked examples, see SQL Query with Case.
One caution applies. A CASE expression that grows long, with many branches and nested conditions, is often a sign that the categories belong in a reference table or should be agreed with the people who will read the report. Keeping the logic visible and short makes the query easier to check, and makes it easier for someone else to spot a wrong boundary.
Subqueries and CTEs: Breaking a Hard Question into Stages
Some questions do not fit in a single SELECT. Finding customers whose total spending is above the average customer total, for example, needs two stages: first compute each customer total, then compare those totals with their average. A subquery puts one query inside another to do exactly that. The inner query produces a result that the outer query can filter, join, or select from.
SELECT customer_id, total_spent
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 1000
ORDER BY total_spent DESC;Subqueries also appear in WHERE clauses. IN with a subquery tests membership in a set produced by another query, and EXISTS tests whether a matching row exists at all. EXISTS is often the clearest way to express “customers who have placed at least one order” because it states the condition directly, without needing to join and then remove duplicates.
A common table expression, or CTE, does the same staging job with better readability. PostgreSQL documentation describes WITH as a way to write auxiliary statements for use in a larger query, which can be thought of as defining temporary tables that exist just for one query. The CTE is named at the top of the query, and the main query then reads from it as if it were a table.
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
average_spending AS (
SELECT AVG(total_spent) AS average_total
FROM customer_totals
)
SELECT c.customer_id, c.total_spent
FROM customer_totals AS c, average_spending AS a
WHERE c.total_spent > a.average_total
ORDER BY c.total_spent DESC;Analysts tend to prefer CTEs for multi stage questions for three practical reasons. First, each stage has a name that says what it means, which helps a reader follow the logic. Second, stages can be checked one at a time by running the CTE on its own. Third, a CTE can be referenced more than once in the main query without rewriting the same subquery twice. Subqueries still have their place for short, single use logic, but when a query grows past two stages, a CTE usually reads better and is easier to debug.
CTEs also have a recursive form, used for hierarchical data such as an organization chart or a chain of categories, where each level refers to the previous one. Most day to day analyst work does not need recursion, but knowing it exists prevents a long, manual workaround when a hierarchy question does appear.
Window Functions: Calculations That Keep Every Row
Aggregation collapses rows. That is exactly what a summary needs, and exactly what some questions do not want. Ranking each order within its region, computing a running total over time, or comparing each month with the previous month all need a calculation that leaves every row in place while looking at neighboring rows. Window functions do that.
PostgreSQL documentation defines a window function as a function that performs a calculation across a set of table rows that are somehow related to the current row, and stresses the contrast with aggregates: unlike a regular aggregate, a window function does not cause rows to become grouped into a single output row, because the rows retain their separate identities. That single sentence is the key to knowing when to reach for a window function.
A window function call always contains an OVER clause. PARTITION BY inside OVER divides rows into groups, much like GROUP BY, but without collapsing them. ORDER BY inside OVER sets the order within each partition. A frame clause can then narrow the window further, for example to all rows from the start of the partition up to the current row, which is how running totals are built.
The ranking functions are the most common starting point. ROW_NUMBER gives each row a unique sequential number within its partition. RANK gives tied rows the same rank and then skips the next rank, so two rows tied for first are followed by rank 3. DENSE_RANK gives tied rows the same rank without skipping, so two rows tied for first are followed by rank 2. MySQL documentation describes RANK as the rank of the current row within its partition, with gaps, and DENSE_RANK as the same rank without gaps, and ROW_NUMBER as the number of the current row within its partition. The choice between them is a reporting decision, not a technical one, and it should match what the reader expects: a top 10 list built with RANK can contain fewer than ten rows if there are ties, while one built with ROW_NUMBER always contains ten.
SELECT
region,
customer_id,
total_spent,
RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) AS region_rank
FROM customer_totals
ORDER BY region, region_rank;In that example, customer_totals is the kind of CTE shown in the previous section. Combining a CTE with a window function is a standard analyst pattern: the CTE prepares one row per customer, and the window function ranks those rows within each region without collapsing them.
LAG and LEAD look at a neighboring row, typically the previous or next period, which makes month on month comparison straightforward. A running total uses SUM with a frame that runs from the start of the partition to the current row:
SELECT
order_month,
monthly_revenue,
SUM(monthly_revenue) OVER (ORDER BY order_month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
LAG(monthly_revenue) OVER (ORDER BY order_month) AS previous_month_revenue
FROM monthly_revenue
ORDER BY order_month;Two warnings apply to window functions. First, they are permitted only in the SELECT list and the ORDER BY clause in PostgreSQL, not in WHERE, GROUP BY, or HAVING. To filter on a window result, such as keeping only the top three rows per group, wrap the window query in a CTE or subquery and filter in the outer query. Second, a window function without PARTITION BY treats the whole result as one partition, which is correct for a grand running total but a mistake when the intent was a per group calculation. Always check the partition before trusting a window result.
Cleaning and Validating Data in SQL
Real analyst data is rarely clean. Names arrive with inconsistent capitalization, dates arrive in more than one format, amounts arrive as text, and the same customer appears under two slightly different ids. SQL gives the analyst a set of tools for handling that mess inside the query, but the deeper point is that cleaning should be visible and repeatable, not done silently in a spreadsheet that nobody else can inspect.
NULL handling comes first, because NULL means unknown, not zero and not an empty string. COALESCE returns the first non NULL value in its list, so COALESCE(region, ‘Not provided’) fills a gap for display without pretending the region is known. Counting, averaging, and joining all behave differently around NULL, as shown earlier, so any query over a column with missing values should state how those values are treated.
Text cleaning usually means trimming leading and trailing spaces, standardizing case, and sometimes replacing a known variant with a standard value. A region column containing “North”, “north”, and “North ” should be standardized before grouping, for example by trimming spaces and converting to a consistent case, or the report will show three regions where the business sees one. Date cleaning means converting text dates to real date values and confirming the format, because sorting text dates alphabetically does not always match sorting them chronologically, especially when day and month order differ between sources.
Duplicates need a decision, not just a DISTINCT. SELECT DISTINCT removes exact duplicate rows, which is fine for a quick check, but duplicated customers or duplicated orders usually need a rule: keep the earliest record, keep the most recent, or flag both for the data owner. The ROW_NUMBER window function from the previous section is the standard tool here: number rows within each suspected duplicate group, ordered by the rule you chose, and keep row number 1.
Casting converts a value from one type to another, such as text to a number or text to a date. A cast that fails on some rows is useful information, not just an error to work around, because it points at the rows that do not fit the expected format. Investigate those rows before forcing them through.
Analysts should also be careful about what SQL cleaning cannot fix. If a column mixes currencies without a currency column, no query can safely convert the amounts. If two customers share one id, no query can separate them reliably. In those cases the honest output is a flagged limitation and a note to the data owner, not a cleaned looking number built on a guess.
Putting It Together: One Question, End to End
Consider a complete analyst task: report completed revenue by region for the first quarter of 2026, show each region share of the total, and list the top three customers in each region. The task touches every skill in this article, and breaking it into stages keeps each stage checkable.
Stage one filters and aggregates. Join orders to customers for region, filter to completed orders in the quarter, and group by region to get regional revenue. Stage two adds a window calculation over the regional totals to get each share, or computes the grand total in a CTE and divides by it. Stage three prepares one row per customer with a regional rank, using a CTE for customer totals and RANK over each region partition. Each stage can be run on its own and its row count and total checked before the next stage is added.
A sketch of the combined query shows the structure, with the CTEs doing the staging and the window functions doing the ranking:
WITH regional_revenue AS (
SELECT c.region, SUM(o.amount) AS revenue
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2026-04-01'
GROUP BY c.region
),
customer_totals AS (
SELECT c.region, c.customer_id, SUM(o.amount) AS total_spent
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2026-04-01'
GROUP BY c.region, c.customer_id
),
ranked_customers AS (
SELECT
region,
customer_id,
total_spent,
RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) AS region_rank
FROM customer_totals
)
SELECT region, customer_id, total_spent, region_rank
FROM ranked_customers
WHERE region_rank <= 3
ORDER BY region, region_rank;The discipline in a query like this is not cleverness. It is naming each stage clearly, filtering early, checking the join, and verifying the totals at each step. A reader who can follow the stages can also challenge them, which is exactly what a shared analysis needs.
Common Mistakes, and How to Catch Them
Several mistakes recur in analyst SQL, and most of them produce a number that looks reasonable. Knowing the list helps, because a check can be aimed at each one.
Filtering an aggregate with WHERE instead of HAVING produces an error in most systems, which is the kind outcome, because it forces a fix. Filtering a left join in the WHERE clause, as described earlier, does not produce an error, so it needs a deliberate check: count the NULL rows before and after the filter.
Counting rows after a join without checking key uniqueness inflates totals silently. The check is a row count before the join, a row count after it, and a count of distinct keys on each side. If the join multiplies rows, the question needs to change, for example by aggregating one side before joining, or by joining on a genuinely unique key.
Averaging an average is another quiet error. The average of monthly averages is not the overall average unless every month has the same number of rows. The correct overall average recomputes from the underlying rows, or weights each monthly average by its row count. The same caution applies to percentages: a percentage of a percentage needs its base stated, or it misleads.
Date boundaries cause errors at every period end. Using an inclusive end date with a timestamp column can include part of the next day, while using a date only comparison can exclude it. The “from the first day up to but not including the first day of the next period” pattern avoids both problems and is worth using consistently.
Finally, sorting without a tiebreaker can make a top 10 list unstable: rows tied on the sorting column may appear in a different order each time the query runs. Adding a unique column, such as an id, as a second sort key makes the result repeatable, which matters when a report is compared across runs.
Checking a Result Before Sharing It
A result that has not been checked is a draft, not an answer. A short checklist, applied every time, catches most problems before a reader does.
First, check row counts. How many rows went in, how many came out, and does the change match the filters and joins applied? Second, reconcile totals. Does the sum of the regional breakdown equal the ungrouped total? Does the count of customers in the breakdown match the count of distinct customers in the source? Third, spot check individual rows. Pick a customer or an order from the result and trace it back to the source tables by hand. Fourth, look at the extremes. The largest and smallest values, and any NULL values in a column that should always be filled, often reveal a data problem faster than a middle value does. Fifth, compare with a trusted figure. If last quarter total is known, the new total should be explainable in terms of it, not merely close to it.
When two figures disagree, the investigation order follows the workflow order: check the filter, then the join, then the grouping, then the calculation. Resist the urge to adjust a query until the number matches an expected value. The expected value might be the wrong one, and a query edited to match it hides a real difference in the data.
Documentation matters here too. A query saved with a short note stating the question, the tables, the filters, and any known limitations can be rerun and challenged months later. A query pasted into a chat thread without that note cannot. Analysts who keep their queries with their reports save their future selves a difficult reconstruction.
What SQL Does Not Do, and What Comes Next
SQL is a querying language, not a complete analysis environment. It retrieves, combines, summarizes, and checks data well, but it is not the natural place for statistical modeling, for building interactive charts, or for writing long narrative reports. A common analyst pattern is to use SQL to produce a clean, checked dataset, and then use a separate reporting or charting tool to present it. Keeping those roles separate also keeps the SQL simpler, because presentation logic does not creep into the query.
For a reader who wants to go further, the most useful next steps follow the order of this article. Practice filtering and joining on a small sample dataset until row counts behave as expected. Then practice aggregation until the difference between WHERE and HAVING feels automatic. Then practice CTEs, because readable multi stage queries are the bridge to harder questions, and window functions after that, because they solve ranking and running total problems that aggregation cannot. Throughout, read the official documentation for the database system in use, since function names, date handling, and NULL behavior vary in details that matter for real queries. No single article, including this one, replaces that documentation.
The analyst skill that matters most is not memorizing syntax. It is the habit of asking, for every result, whether the rows, the join, the grouping, and the total all make sense together, and being willing to check before sharing. SQL rewards that habit, because every stage of a query can be run and inspected on its own.
FREQUENTLY ASKED QUESTIONS
What does a data analyst actually use SQL for?
A data analyst uses SQL to pull specific rows from database tables, combine tables that share a key, summarize rows into counts and totals, and check the result. Typical tasks include filtering orders to a period and status, joining orders to customers for region breakdowns, grouping revenue by month or category, and ranking customers or products within a group.
Do analysts need to know every SQL command?
No. Most day to day analyst work uses a small set of operations well: SELECT, WHERE, joins, aggregate functions with GROUP BY and HAVING, CASE, subqueries and CTEs, and a few window functions. Depth in those operations, plus careful checking, covers far more real questions than shallow knowledge of a long command list.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and aggregation, while HAVING filters groups after aggregation. A condition on a raw column such as order status belongs in WHERE. A condition on a computed summary such as a count above 100 belongs in HAVING. Putting an aggregate condition in WHERE is an error in most systems, because the aggregate does not exist yet at that stage.
When should I use a subquery and when should I use a CTE?
Use a subquery for short, single use logic inside one query. Use a CTE when the question has several stages, when a stage needs a readable name, when a stage should be checked on its own, or when the same intermediate result is needed more than once. Both produce the same kind of result; the CTE is usually easier to read and debug as queries grow.
What is the difference between GROUP BY and a window function?
GROUP BY collapses rows into one row per group, with one summary value per group. A window function calculates across related rows while keeping every row in the result. Use GROUP BY for summaries such as total revenue by region. Use a window function for row level results such as ranking each customer within a region, a running total over time, or a comparison with the previous month.
How do analysts check SQL results for errors?
Common checks include comparing row counts before and after each join, reconciling grouped totals against an ungrouped total, spot checking individual rows back to the source tables, inspecting the largest and smallest values and any unexpected NULL values, and comparing a new figure with a previously trusted figure. If a check fails, investigate in workflow order: filter, join, grouping, then calculation.
Why do my totals change when I add a join?
The usual cause is a join key that is not unique on one side, so rows are duplicated and then summed more than once. Check the distinct key count on each side of the join and compare row counts before and after joining. If duplication is the cause, aggregate the duplicated side first, join on a genuinely unique key, or reconsider whether the join is needed for that particular total.
Which database system should an analyst learn SQL on?
The core operations in this article, including SELECT, WHERE, joins, GROUP BY, HAVING, CASE, CTEs, and the common window functions, work in the same general form across major relational database systems, with differences in function names and date handling. Learning the core well on one system transfers well to others, and the official documentation for the system in use is the authority for the details that differ.
Sources & references
- PostgreSQL Documentation, Aggregate Functions: an aggregate function computes a single result from multiple input rows, with count, sum, average, maximum, and minimum as examples, and HAVING filters grouped rows after aggregates are computed while WHERE selects input rows before grouping. https://www.postgresql.org/docs/17/tutorial-agg.html
- PostgreSQL Documentation, Window Functions: a window function performs a calculation across a set of table rows related to the current row, uses an OVER clause, divides rows with PARTITION BY, and, unlike a regular aggregate, does not collapse rows into a single output row. https://www.postgresql.org/docs/current/tutorial-window.html
- PostgreSQL Documentation, WITH Queries (Common Table Expressions): WITH provides auxiliary statements for use in a larger query, which can be thought of as defining temporary tables that exist just for one query, and its basic value is breaking complicated queries into simpler parts. https://www.postgresql.org/docs/14/queries-with.html
- MySQL Reference Manual, Window Function Descriptions: window functions perform a calculation for each row using rows related to that row, with RANK returning the rank within its partition with gaps, DENSE_RANK returning the rank without gaps, and ROW_NUMBER returning the number of the current row within its partition. https://dev.mysql.com/doc/refman/9.7/en/window-function-descriptions.html
