For data science, learn how to select data, filter rows, join tables, aggregate with GROUP BY, filter aggregates with HAVING, organize multi-step queries with subqueries or CTEs, and calculate across related rows with window functions. Together, these concepts let you move from raw tables to useful, explainable results.
1. SELECT and FROM: choose what to retrieve and where it comes from
SELECT specifies the columns or expressions to return; FROM identifies the table or other query source. Start by selecting only the fields needed for the analysis, which makes the intended output easier to read.
SELECT customer_id, order_date, amount
FROM orders;SQL syntax varies by database, but a typical query can also include a WITH clause, joins, filters, grouping, ordering, and a limit. Apache DataFusion documents these clauses in its SELECT statement syntax.
2. WHERE: filter source rows before aggregation
WHERE keeps rows that meet a condition. For example, to analyze orders from 2025, filter them before calculating totals:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
SELECT customer_id, amount
FROM orders
WHERE order_date >= DATE '2025-01-01';SQLite describes query processing as reading the source with FROM, applying WHERE, and then processing grouping and result expressions. That order explains why WHERE is for individual input rows, not aggregate results. See SQLite’s SELECT documentation.
3. GROUP BY, aggregates, and HAVING: summarize and filter groups
GROUP BY combines rows that share one or more values. Aggregate functions such as COUNT, SUM, and AVG calculate a result for each group. HAVING then filters those groups.
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 500;This query first excludes older orders, then totals the remaining orders per customer, and finally keeps customers whose total exceeds 500. The example uses date-literal syntax common in PostgreSQL; exact syntax can differ by engine.
A common aggregate-query error occurs when a selected expression is neither aggregated nor included in the grouping. PostgreSQL requires selected expressions in a grouped query to be aggregated or functionally dependent on grouped columns. Consult its table-expression documentation for the rule.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
4. JOINs: combine related tables
A join brings columns from related table expressions into one result. Analysts often use joins to combine measures, such as orders, with descriptive dimensions, such as customer names. The join condition states how rows correspond.
SELECT c.customer_name, o.order_date, o.amount
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;Check the relationship before aggregating: if a join matches one source row to multiple rows in the other table, it can multiply records and inflate sums or counts. Apache DataFusion includes JOIN in its documented SELECT grammar.
Rank #3
5. Subqueries: nest a result where it is needed
A subquery is a SELECT nested inside another statement. It is useful when a condition depends on a result computed by another query, without needing to name a separate stage.
SELECT customer_id
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500
);This example returns customers whose order totals exceed 500. Other common forms use a scalar comparison or EXISTS. Microsoft Learn documents subqueries in WHERE or HAVING and describes these forms in its subqueries overview.
6. CTEs: name stages in a multi-step query
A common table expression (CTE) gives a query result a name for use in the statement that follows. CTEs help when a transformation has several logical stages and make it easier to inspect or revise each stage.
Rank #4
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_totals
WHERE total_spend > 500;Here the CTE calculates totals; the outer query filters them. Apache DataFusion describes a WITH clause as defining CTEs that can be referenced by name in the rest of a query, and documents WITH syntax. Microsoft Learn documents CTEs preceding SELECT and several data-modification statements in SQL Server; see its CTE documentation.
7. Window functions: calculate across rows without collapsing them
Unlike a grouped aggregate, a window function adds a calculation to rows while leaving the row-level results visible. Use one for rankings, running totals, or comparisons with other rows in a partition.
SELECT customer_id, order_date, amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;The result retains each order and adds a cumulative total for its customer. PARTITION BY defines the peer group, while ORDER BY within the window defines the sequence for a running calculation. BigQuery and Apache DataFusion document analytic or window expressions in their query syntax: see BigQuery query syntax and DataFusion SELECT syntax.
Best Value
How the concepts fit together
A useful way to build an analysis is to move from the source rows toward the result: identify data with FROM, filter rows with WHERE, join related data, form groups with GROUP BY, filter those groups with HAVING, then order or limit the output. Add a window calculation when the result must retain detail rows while showing a calculation across peers.
| Technique | What it returns or filters | When it helps |
|---|---|---|
WHERE |
Filters individual source rows before grouping. | Restrict the input data for an analysis. |
GROUP BY with aggregates |
Produces a summary for each group rather than one output row per source row. | Calculate totals, counts, or averages by category. |
HAVING |
Filters groups after aggregation. | Keep only groups meeting an aggregate condition. |
| Subquery | Nests a result inside a condition or expression. | Keep a computed result local to one part of a statement. |
| CTE | Names a query stage for use later in the statement. | Make a multi-stage transformation easier to follow. |
| Window function | Adds a calculation across related rows while retaining row-level output. | Rank, accumulate, or compare rows without collapsing them into groups. |
These patterns are broadly useful, but exact syntax and feature support vary by database. Check the documentation for the engine you use—such as PostgreSQL, BigQuery, SQLite, SQL Server, or DataFusion—before relying on dialect-specific syntax.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
