SQL for Data Science — Essential Queries to Master

Data lives in databases. To get at it, you need SQL.

Python is the language most data scientists reach for first, but before Python can analyze anything, the data has to come from somewhere. In most organizations, that somewhere is a relational database or a cloud warehouse, and the way in is SQL (Structured Query Language). This guide covers the seven skills that carry most day-to-day analytical work, with PostgreSQL examples, a worked customer-segmentation query, and the traps that catch people who learned SQL from a cheat sheet.

All the examples use PostgreSQL syntax. Other engines differ in small ways, and the article flags the differences that cause the most errors.

---

Why SQL is a core data science skill

Many beginners try to skip SQL and go straight to Python. The shortcut usually costs more time than it saves. SQL is not a niche tool. In the 2025 Stack Overflow Developer Survey, 58.6% of all respondents and 61.3% of professional developers reported using SQL, and PostgreSQL was used by 55.6% of all respondents and 58.2% of professionals.

Four practical reasons make it worth learning first:

1. Data rarely lives in CSV files. Production data sits in databases, and a query is how you pull the slice you need.
2. Aggregation belongs near the data. Computing a count or average inside the database means only the small result travels over the network, instead of millions of raw rows landing in your notebook.
3. Cloud warehouses speak SQL. Warehouses such as BigQuery and Snowflake are queried in SQL dialects, so the skill carries across employers.
4. Colleagues share the language. Analysts, engineers, and product managers read SQL, so a query is a way to show your work.

For the job-market side, the U.S. Bureau of Labor Statistics reports a median pay of $120,230 per year for data scientists in 2025 and projects employment growth of 35% from 2025 to 2035, which it calls much faster than average, on its Occupational Outlook Handbook page. The page does not break out SQL specifically, so treat it as context for the field, not a statement about SQL itself.

A sensible order of learning is SQL, then Pandas, then a machine learning library. Each step builds on the one before it, and a model is only as good as the data extracted for it.

---

A short history, and why it still matters

SQL has a long lineage. In 1970, Edgar F. Codd, working at IBM's San Jose Research Lab, published "A Relational Model of Data for Large Shared Data Banks," the paper that proposed organizing data as tables of rows and columns. IBM began its System R project in 1973, and Don Chamberlin and Raymond Boyce, members of that team, are credited as SQL's co-inventors, according to IBM's history of the relational database.

The history matters because it explains the language's character. SQL is declarative. You describe the result you want, and the database engine decides how to compute it. That is the opposite of a Python loop, where you spell out each step. It is like ordering a meal from a menu versus cooking it yourself. You specify the dish, and the kitchen picks the method.

---

Set up a practice environment

You can learn SQL on a laptop in minutes. Three routes work well for beginners:

• Local PostgreSQL. Install it with your system's package manager and connect with the psql command-line client. It teaches you the standard tools and matches what many teams run.
• SQLite. A single-file database that ships with many systems and with Python, which makes it a low-friction way to practice.
• DuckDB. An in-process analytical database that runs inside your Python session. The DuckDB project page describes it as an embedded database with a columnar-vectorized execution engine, designed for analytical workloads and released under the MIT License. For a hands-on introduction, see What Is DuckDB?, or the comparison in DuckDB vs Pandas.

Hosted free tiers come and go. ElephantSQL, once a popular free PostgreSQL host for learners, announced it would stop sales on May 1, 2024 and shut down completely by January 27, 2025, according to a migration guide from a competing vendor. The lesson is to avoid depending on a free host for anything you cannot recreate.

Whichever tool you choose, create a few small tables so you can check your own answers. Tables with five to ten rows are easier to verify by hand than a million-row sample.

---

How a query actually runs

Before the skills, one idea clears up most beginner confusion. You write a query in one order, but the database processes it in a different order. According to the PostgreSQL SELECT documentation, the processing runs roughly as follows.

Step	Clause	What it does
1	FROM and JOIN	Builds the set of input rows
2	WHERE	Filters individual rows
3	GROUP BY	Collapses rows into groups
4	HAVING	Filters whole groups, after aggregation
5	SELECT list	Computes the output expressions
6	DISTINCT	Removes duplicate output rows
7	ORDER BY, then LIMIT	Sorts, then trims the result

This explains several rules at once. You cannot use a column alias from the SELECT list inside WHERE in standard SQL, because WHERE runs before the SELECT list exists. You cannot filter on an aggregate in WHERE, because the groups do not exist yet, which is exactly the problem HAVING solves. And window functions, covered below, appear only in the SELECT list and ORDER BY because they run late.

One more fact from the same documentation deserves its own line. Without ORDER BY, the order of returned rows is unspecified, and the documentation says it must not be relied on. A query that happens to return rows in a nice order today can return them differently tomorrow.

---

Skill 1: SELECT and WHERE, getting the data you need

Every query starts here. SELECT picks columns, FROM names the table, and WHERE keeps only the rows that match a condition.

-- Get specific columns
SELECT name, age, salary, department
FROM employees;

-- Filter rows
SELECT name, salary
FROM employees
WHERE salary > 80000;

-- Multiple conditions
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 90000
  AND hire_date >= '2024-01-01';

-- IN operator (multiple values)
SELECT name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'Engineering');

-- LIKE for pattern matching
SELECT name, email
FROM employees
WHERE email LIKE '%@company.com';

Handling NULL, the value that means "unknown"

NULL is the most common source of silent wrong answers. A NULL is not zero or an empty string. It means the value is missing or unknown, so comparing anything to it with = never returns true.

-- Wrong: returns no rows, even if some phones are missing
SELECT * FROM employees WHERE phone = NULL;

-- Right: missing phone numbers
SELECT * FROM employees WHERE phone IS NULL;

-- Right: rows that have a phone number
SELECT * FROM employees WHERE phone IS NOT NULL;

NULLs also change counts. COUNT(*) counts rows, while COUNT(phone) counts only rows where phone is not NULL, so the two numbers differ whenever data is missing. When you sort, PostgreSQL treats NULL as larger than any non-NULL value by default, so ascending order puts NULLs last and descending order puts them first. You can override that with NULLS FIRST or NULLS LAST. Missing data is a statistical issue as well as a SQL one, and deciding how to fill or drop it is a topic of its own (see the related articles at the end).

---

Skill 2: GROUP BY and aggregation, summarizing data

This is where SQL becomes an analysis tool. GROUP BY collapses all rows that share the same values into one row, and aggregate functions compute a summary for each group.

-- Employee statistics by department
SELECT
  department,
  COUNT(*)      AS employee_count,
  AVG(salary)   AS avg_salary,
  MIN(salary)   AS min_salary,
  MAX(salary)   AS max_salary,
  SUM(salary)   AS total_payroll
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Departments with more than 10 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10
ORDER BY headcount DESC;

-- Sales by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(DISTINCT order_id)        AS orders,
  SUM(amount)                     AS revenue,
  AVG(amount)                     AS avg_order_value
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY 1
ORDER BY 1;

The PostgreSQL documentation states the WHERE versus HAVING distinction directly. WHERE filters individual rows before grouping, while HAVING filters the groups created by GROUP BY. If you want only orders from this year, use WHERE. If you want only departments with more than 10 people, use HAVING.

Function	What it returns
COUNT(*)	Number of rows in the group
COUNT(col)	Number of non-NULL values in the column
COUNT(DISTINCT col)	Number of unique non-NULL values
SUM(col)	Total of the non-NULL values
AVG(col)	Mean of the non-NULL values
MIN(col), MAX(col)	Smallest and largest value

A rule that trips up newcomers: every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate function. PostgreSQL enforces this and raises an error otherwise. Some engines historically accepted looser queries and returned an arbitrary value, which hides bugs.

The average of averages trap

Suppose department A has 2 employees with an average salary of 100, and department B has 8 employees with an average of 50. The mean of the two department averages is 75, but the true company average is (2 × 100 + 8 × 50) ÷ 10 = 60. Averaging averages ignores group size. In SQL, compute the overall average from the raw rows, or carry the sum and count through and divide at the end.

---

Skill 3: JOINs, combining tables

Real databases split data across tables to avoid repetition. An employees table stores a department ID, and a departments table stores the department details. A JOIN stitches them back together using a matching column.

-- INNER JOIN: only rows that match in both tables
SELECT e.name, e.salary, d.department_name, d.budget
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN: all employees, with department info if it exists
SELECT e.name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- Employees without a department get NULL for department_name

-- Multiple joins, including a self-join for managers
SELECT
  e.name,
  d.department_name,
  m.name  AS manager_name,
  l.city  AS office_location
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id
JOIN locations l ON d.location_id = l.id
WHERE e.salary > 70000;

The easiest way to hold the join types in your head is with two tiny tables, the same ones the PostgreSQL manual uses. Table t1 holds the numbers 1, 2, and 3. Table t2 holds 1, 3, and 5. Joining on the number gives different results depending on the type.

Join type	Rows returned for t1 and t2	Why
INNER JOIN	1 and 3	Only values present in both
LEFT JOIN	1, 2 (with NULLs), 3	Every row of t1; unmatched t1 rows get NULLs
RIGHT JOIN	1, 3, 5 (with NULLs)	Every row of t2; unmatched t2 rows get NULLs
FULL OUTER JOIN	1, 2, 3, 5	Every row from both sides
CROSS JOIN	9 combinations	Every pairing, 3 × 3

Think of the join types as a guest list. An inner join is the people who appear on both lists. A left join is everyone on the first list, with a blank next to anyone who is missing from the second.

Where you put a condition changes the answer

The manual makes a point that causes real bugs. In an outer join, a condition in the ON clause is applied before the join, while a condition in the WHERE clause is applied after it. With a LEFT JOIN, moving a filter on the right-hand table from ON to WHERE quietly removes the NULL-padded rows and turns the query into an inner join. If you want "all customers, and their orders from this year, if any," the date filter belongs in the ON clause.

-- Keeps customers with no 2026 orders (NULL-padded)
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-01-01';

-- Drops customers with no 2026 orders (acts like an inner join)
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

To find rows with no match at all, such as customers who never ordered, LEFT JOIN and then test for NULL on the right-hand key in WHERE:

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The fan-out trap

Joining a table to another table where one row matches many rows multiplies the first table's rows. Imagine a customers table with a credit_limit column and an orders table. After a join, a customer with three orders appears three times, so SUM(credit_limit) counts that customer's limit three times. The symptom is a total that is suspiciously large. The fixes are to aggregate the many-side table first in a CTE and then join, or to count distinct keys instead of rows. Always compare the row count before and after a join. If it grew when you expected it to stay the same, you have fan-out.

---

Skill 4: window functions, analytics without collapsing rows

Window functions compute a value across a set of rows related to the current row, without merging those rows into one. The PostgreSQL manual puts it this way: with window functions, the rows retain their separate identities. They are the tool for rankings, running totals, and comparisons to a previous row.

A window function always has an OVER clause. PARTITION BY inside OVER splits rows into groups, similar to GROUP BY but without collapsing them. ORDER BY inside OVER sets the order in which rows are processed within each group.

-- Rank employees by salary within each department
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  DENSE_RANK() OVER (ORDER BY salary DESC)                   AS overall_rank
FROM employees;

-- Running total of sales
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Compare each employee to the department average
SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department)          AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS vs_dept_avg
FROM employees;

-- LAG: compare each month to the previous one
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) - 1 AS mom_growth
FROM monthly_revenue;

Think of a window function as a spreadsheet column that can peek at neighboring rows. A GROUP BY query is a pivot table that throws away the detail. A window function keeps every row and adds a computed column beside it.

The tie rule that surprises people

When a window has an ORDER BY, the default frame runs from the start of the partition up through the current row, plus any following rows that tie with the current row. That detail changes running totals when values repeat. Take three orders: two on January 3 (40 and 60) and one on January 5 (25).

order_date	amount	running_total (default frame)
2026-01-03	40	100
2026-01-03	60	100
2026-01-05	25	125

Both January 3 rows show 100, not 40 and then 100, because they tie on the sort key and each row's frame includes its tied peers. If you want a strict row-by-row total, add a tiebreaker such as the order ID to the ORDER BY, or state the frame explicitly with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Window functions have one other restriction. They are allowed only in the SELECT list and ORDER BY. To filter on a window result, such as "the top three salaries per department," wrap the query in a subquery or CTE and filter in the outer query:

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;

---

Skill 5: subqueries and CTEs, building complex logic in readable steps

A subquery is a query nested inside another. A CTE (common table expression) names a query at the top with WITH so later parts can refer to it. The PostgreSQL documentation describes the main use of WITH as breaking a complicated query into named parts.

Subqueries

-- Employees earning above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Customers with more orders than the average customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > (
  SELECT AVG(order_count)
  FROM (
    SELECT COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
  ) subq
);

CTEs: the same logic, readable

WITH customer_orders AS (
  SELECT
    customer_id,
    COUNT(*)    AS order_count,
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
avg_stats AS (
  SELECT AVG(order_count) AS avg_orders FROM customer_orders
)
SELECT
  c.customer_id,
  c.order_count,
  c.total_spent,
  c.order_count - a.avg_orders AS vs_average
FROM customer_orders c
CROSS JOIN avg_stats a
WHERE c.order_count > a.avg_orders
ORDER BY c.total_spent DESC;

A CTE reads like a recipe with named prep steps. Instead of one tangled instruction, you chop the onions, then make the sauce, then combine them. Each step has a name, so a reader can follow it and you can test each step on its own.

Two details from the PostgreSQL documentation are worth knowing. First, a CTE that is not recursive and has no side effects can be folded into the parent query when it is referenced once, so the optimizer sees the whole thing. Referenced more than once, it is computed once and reused by default. Second, you can override this behavior with MATERIALIZED or NOT MATERIALIZED. Most analysts never need either keyword, but they matter when a CTE is slow, and you suspect the planner is making the wrong choice.

CTEs can also be recursive, which lets a query refer to its own output. That is the tool for hierarchies such as an org chart or a bill of materials, where a row points to its parent.

---

Skill 6: CASE expressions, conditional logic

CASE is SQL's if-then-else. It lets you bucket values, build flags, and pivot rows into columns.

-- Classify employees by salary band
SELECT
  name,
  salary,
  CASE
    WHEN salary < 50000  THEN 'Junior'
    WHEN salary < 90000  THEN 'Mid-Level'
    WHEN salary < 140000 THEN 'Senior'
    ELSE 'Principal'
  END AS seniority_band
FROM employees;

-- Manual pivot: counts by category in one row per department
SELECT
  department,
  COUNT(CASE WHEN remote = TRUE  THEN 1 END) AS remote_count,
  COUNT(CASE WHEN remote = FALSE THEN 1 END) AS onsite_count,
  AVG(CASE WHEN remote = TRUE  THEN salary END) AS avg_remote_salary,
  AVG(CASE WHEN remote = FALSE THEN salary END) AS avg_onsite_salary
FROM employees
GROUP BY department;

The pivot works because a CASE with no ELSE returns NULL for rows that do not match, and aggregates ignore NULLs. COUNT(CASE WHEN ... THEN 1 END) therefore counts only matching rows. The branches are evaluated in order and the first match wins, which is why the salary bands above go from smallest to largest. Reverse them and every salary below 140,000 would be labeled by whichever branch came first.

---

Skill 7: dates and text, because real data is messy

Dates and strings account for a large share of cleaning work. A well-written query can do much of it before the data ever reaches Python, though a full cleaning workflow is bigger than SQL alone, as the guide to what data cleaning actually involves shows.

-- Date operations (PostgreSQL)
SELECT
  order_id,
  order_date,
  EXTRACT(year  FROM order_date)       AS year,
  EXTRACT(month FROM order_date)       AS month,
  DATE_TRUNC('week', order_date)       AS week_start,
  order_date + INTERVAL '30 days'      AS follow_up_date,
  ship_date - order_date               AS days_to_ship
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';

-- String functions
SELECT
  UPPER(name)                                        AS name_upper,
  LOWER(email)                                       AS email_lower,
  TRIM(phone)                                        AS phone_clean,
  SUBSTRING(email, 1, POSITION('@' IN email) - 1)    AS username,
  CONCAT(first_name, ' ', last_name)                 AS full_name,
  LENGTH(description)                                AS desc_length
FROM users
WHERE email LIKE '%gmail.com';

Three facts from the PostgreSQL date and time documentation save hours:

• EXTRACT pulls a field such as year or month from a date and returns a numeric value.
• DATE_TRUNC rounds a timestamp down to a unit, which makes it the standard way to group by week or month.
• Subtracting two dates returns the number of days between them. PostgreSQL has no DATEDIFF function, so code copied from another engine will fail with an error.

Task	PostgreSQL approach	Note
Days between two dates	ship_date - order_date	Integer for date types
Group by month	DATE_TRUNC('month', order_date)	Returns the first moment of the month
Year of a date	EXTRACT(year FROM order_date)	Numeric result
Elapsed time as years, months, days	AGE(later, earlier)	Symbolic, month lengths matter

Other warehouses provide their own date-difference functions with different names and argument orders. When a query ports to a new engine, check the date functions first.

---

A realistic query: customer segmentation

Here is the kind of query that shows up in a first-week analytics task. It scores each customer by how recently and how often they bought, then summarizes each segment. It uses a CTE, aggregation, date arithmetic, and CASE together.

WITH customer_metrics AS (
  SELECT
    customer_id,
    MIN(order_date)                 AS first_order,
    MAX(order_date)                 AS last_order,
    COUNT(DISTINCT order_id)        AS total_orders,
    SUM(amount)                     AS lifetime_value,
    AVG(amount)                     AS avg_order_value,
    CURRENT_DATE - MAX(order_date)  AS days_since_last_order
  FROM orders
  GROUP BY customer_id
),
segments AS (
  SELECT
    customer_id,
    lifetime_value,
    total_orders,
    days_since_last_order,
    CASE
      WHEN days_since_last_order <= 30  AND total_orders >= 3   THEN 'Champions'
      WHEN days_since_last_order <= 90  AND lifetime_value > 500 THEN 'Loyal'
      WHEN days_since_last_order BETWEEN 91 AND 180             THEN 'At Risk'
      WHEN days_since_last_order > 180                          THEN 'Lost'
      ELSE 'New'
    END AS segment
  FROM customer_metrics
)
SELECT
  segment,
  COUNT(*)                          AS customers,
  ROUND(AVG(lifetime_value), 2)     AS avg_ltv,
  ROUND(SUM(lifetime_value), 2)     AS total_revenue
FROM segments
GROUP BY segment
ORDER BY total_revenue DESC;

Read it from the top. The first CTE collapses the orders table to one row per customer. The second CTE turns those numbers into a label. The final SELECT counts customers and revenue per label. Because CASE evaluates top to bottom, a customer who qualifies as a Champion never reaches the Loyal rule.

The thresholds here (30 days, 90 days, 500 in lifetime value) are illustrative. A real project would choose them from the data, perhaps using quantiles, and would check that the segments are stable over time. The query structure stays the same whatever numbers you use. This is also a natural starting point for analysis outside SQL, such as A/B testing which of the segments responds to an offer.

---

Performance: why some queries are slow

You do not need to be a database administrator, but a few habits keep queries fast and costs low.

An index is a separate structure that lets the database find matching rows without scanning the whole table. The PostgreSQL documentation compares it to the index at the back of a book. Instead of reading every page, you look up the term and flip to the right place. The same page warns that indexes are not free. Every write must update every index, so unused indexes slow down inserts and updates. For a deeper look, see why database indexes speed up queries.

-- Good: a condition that can use an index on order_date
SELECT order_id, customer_id, amount
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- Often slower: wrapping the indexed column in a function
SELECT order_id, customer_id, amount
FROM orders
WHERE EXTRACT(year FROM order_date) = 2026;

-- Explore a large table cheaply
SELECT * FROM large_table LIMIT 100;

-- Name only the columns you need in production code
SELECT order_id, customer_id, amount FROM orders WHERE order_date >= '2026-01-01';

An ordinary index on order_date helps when the condition compares the column itself to a range. Wrapping the column in a function usually prevents that, unless a matching expression index exists, which is why the range form is the safer habit. Note also that YEAR(order_date), which appears in many tutorials, is not valid in PostgreSQL. EXTRACT(year FROM order_date) is the PostgreSQL form.

One caution on LIMIT for exploration. Without ORDER BY, PostgreSQL documentation notes the row order is unspecified, so LIMIT 100 returns an arbitrary hundred rows, not the first or the best. That is fine for peeking at a table's shape and wrong for anything you will report.

Reading a query plan with EXPLAIN

To see how the database will run a query, put EXPLAIN in front of it. The output is a tree of plan nodes with estimated costs and row counts. The estimates are in arbitrary cost units, not seconds. EXPLAIN ANALYZE goes further. It actually runs the query and adds the true row counts and timings beside the estimates. A large gap between estimated and actual rows is a common sign that the planner is working from stale statistics.

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

The PostgreSQL manual includes a warning that matters for beginners. Because EXPLAIN ANALYZE executes the statement, an INSERT, UPDATE, or DELETE really happens. If you want to measure a data-modifying statement, wrap it in a transaction and roll it back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'archived' WHERE order_date < '2020-01-01';
ROLLBACK;

Rolling back undoes the table changes, but not effects outside the database, such as a function that calls an external service. Run risky statements on a copy of the data.

---

Choosing a database to learn on

The core of SQL transfers across engines, but dialects differ and pricing models matter for analytics at scale.

Database	Model	Best for
PostgreSQL	Open-source server	General-purpose default, standards-friendly
MySQL	Open-source server	Web applications
SQLite	Embedded single file	Local learning and small apps
DuckDB	Embedded analytical engine, MIT license	Fast analytics on local files
BigQuery	Managed cloud warehouse	Large-scale analytics on Google Cloud
Snowflake	Managed cloud warehouse	Enterprise warehousing
Redshift	Managed cloud warehouse	Warehousing on AWS

For BigQuery, Google's pricing page bills on-demand queries by the amount of data processed, with a monthly free allowance, and rates vary by region. The practical consequence is that BigQuery charges by bytes read, not rows returned, so selecting only the columns you need and filtering early directly lowers the bill. Pricing changes, so confirm the current rate before budgeting. Snowflake and Redshift each use their own billing units and change them, so check the vendor's current pricing page instead of relying on a number in an article.

Start with PostgreSQL or DuckDB. The ideas in this article carry over to cloud warehouses, and the differences are mostly in date functions, quoting rules, and a few advanced features. For where SQL sits in a modern pipeline, dbt organizes SQL transformations into tested models, and the data engineering vs data science comparison explains who owns which part of the work.

---

Common mistakes and how to avoid them

Mistake	What goes wrong	Fix
= NULL instead of IS NULL	Returns no rows	Use IS NULL and IS NOT NULL
Filter on a right-table column in WHERE after a LEFT JOIN	Outer join becomes an inner join	Move the condition into ON
Aggregating after a one-to-many join	Totals inflated by duplicated rows	Aggregate first in a CTE, then join
Averaging averages	Wrong overall mean	Compute from raw rows or carry sum and count
Relying on row order without ORDER BY	Results change between runs	Always add ORDER BY when order matters
SELECT * in production	Reads unneeded columns, breaks when schemas change	Name the columns
Copying a function from another dialect	Syntax error	Check date and string functions for your engine

A habit that prevents most of these is to sanity-check every result. Count rows before and after a join. Compare a total to a number you can verify another way. Test on a small table where you can compute the answer by hand. A query that runs without errors is not necessarily correct.

---

Practice plan: what to build in order

Reading examples teaches less than writing queries against data that surprises you. A short progression works well.

1. Week one: filter and sort. Use SELECT, WHERE, ORDER BY, and LIMIT on a table you know. Predict the row count before running each query.
2. Week two: summarize. Practice GROUP BY, HAVING, and COUNT(DISTINCT). Compare each answer with a spreadsheet pivot.
3. Week three: combine. Join three tables, then deliberately break a join to see fan-out and NULL padding.
4. Week four and beyond: window functions and CTEs. Rebuild the segmentation query on your own data, then add a LAG-based month-over-month comparison.

The time this takes varies a lot with prior experience and how much you practice, so treat any fixed number of weeks as a rough pacing guide. The skill that takes longest is not syntax. It is learning to ask whether the number on screen could be wrong.

---

SQL and Python together

SQL and Python are partners, not rivals. A common pattern is to let SQL do the heavy filtering and aggregation, then pull the compact result into Python for modeling and plotting. Pandas can read a query result directly into a DataFrame, and tools like DuckDB can run SQL over a DataFrame without leaving Python. The DuckDB comparison linked earlier shows when each fits, and the Python vs R comparison puts the choice of language in context.

A rule of thumb: if a step reduces data, such as filtering, joining, or aggregating, do it in SQL. If a step needs modeling, custom functions, or plotting, do it in Python. The boundary will move depending on data size and team habits.

---

FAQ

Q: Should I learn SQL before Python? A: It helps to learn SQL early, because the data you will analyze almost always arrives through a query. Many tasks, such as filtering, joining, and aggregating, are natural in SQL. You can learn both in parallel, though, and the right order depends on your first project.

Q: How long does it take to learn SQL well enough for data science? A: It varies with prior experience and practice time. Basic SELECT, WHERE, GROUP BY, and JOIN queries can be learned relatively quickly, while window functions, CTEs, and debugging subtle join problems take longer and come mostly from working with real data.

Q: Is SQL enough for data science without Python? A: For reporting, dashboards, and many business analyses, SQL plus a BI tool goes a long way. For machine learning, statistical modeling, and custom automation, you will want a programming language such as Python or R.

Q: What is the difference between WHERE and HAVING? A: WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Use WHERE for conditions on raw columns and HAVING for conditions on aggregates such as COUNT(*) > 10.

Q: What is the difference between SQL and NoSQL? A: SQL databases are relational. They store data in tables with defined columns and relationships and are queried with SQL. NoSQL databases, such as document stores and key-value stores, use other models and often have flexible schemas. Analytical work usually runs on relational or warehouse systems, although the boundary has blurred as many engines support both styles.

Q: Why does my SQL code fail when I copy it from another tutorial? A: SQL has a standard core, but each engine adds its own functions and quirks. PostgreSQL, for example, has no DATEDIFF, and YEAR() is not valid there. Check which engine the tutorial targets and consult that engine's documentation for date, string, and quoting rules.

---

Sources

• Stack Overflow, 2025 Developer Survey, technology section: survey.stackoverflow.co/2025/technology
• PostgreSQL documentation, window functions tutorial: postgresql.org/docs/current/tutorial-window.html
• PostgreSQL documentation, WITH queries (CTEs): postgresql.org/docs/current/queries-with.html
• PostgreSQL documentation, table expressions and joins: postgresql.org/docs/current/queries-table-expressions.html
• PostgreSQL documentation, SELECT, sorting rows, EXPLAIN, and date functions: sql-select, queries-order, using-explain, functions-datetime
• PostgreSQL documentation, indexes: indexes-intro
• IBM, history of the relational database: ibm.com/history/relational-database
• U.S. Bureau of Labor Statistics, Data Scientists: bls.gov/ooh/math/data-scientists.htm
• Google Cloud, BigQuery pricing: cloud.google.com/bigquery/pricing
• DuckDB, Why DuckDB: duckdb.org/why_duckdb.html

---

Related Articles

• Best Data Science Tools — 2026 Tech Stack
• Data Visualization Tools — Tableau vs Power BI vs Matplotlib
• How to Handle Missing Data — Imputation Strategies

---

Bottom Line

SQL is where most analytical work begins. It is where the data lives, it is the shared language between analysts and engineers, and it rewards care. The syntax is small. The hard part is trusting your result.

Master SELECT, WHERE, GROUP BY, JOINs, and window functions, and check every output against something you can verify. Everything else follows.

Last verified: October 2026