Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 76 ยท Getting Data

SQL for Data Analysis

Use SQL in a data science workflow: filter and aggregate in the database, join tables, use CTEs and window functions, pass parameters safely and load results into pandas.

Most company data lives in a relational database, not in a CSV file on your desk. The people who guard that database speak SQL, and a data scientist who can write a good query is far more effective than one who can only ask for exports. The core idea of this lesson is simple: let the database do the heavy lifting, then bring only the result into pandas.

This is a short, practical tour for analysts. It is not a replacement for the full SQL course, which teaches every clause and the differences between database systems. Here you will see the handful of patterns used in almost every analysis, and how to connect them to pandas. For loading data in general, see Loading Data from CSV, JSON and SQL.

Why not load everything into pandas?

Imagine an orders table with 200 million rows. Loading it into a DataFrame would fill your memory and take an hour. But the question "revenue per month for 2025" needs only twelve numbers. A database is built for exactly that: it has indexes, runs close to the data, and sends back only the answer.

flowchart LR
    DB[("Database<br/>200 million rows")] -->|"SQL: filter, join, aggregate"| R["Result<br/>12 rows"]
    R -->|"pd.read_sql_query"| DF["pandas DataFrame"]
    DF --> A["Analysis, charts, models"]

Reduce the data where it lives; analyse the small result in Python.

A good rule: use SQL to select, filter, join and aggregate; use pandas for cleaning, reshaping, statistics, modelling and plotting.

Setting up a practice database

Python includes SQLite in its standard library (sqlite3), so you can practise with no server. The code below builds a small in-memory shop database with customers and orders, then runs a query and reads it into pandas with pd.read_sql_query:

Python A database, a query and a DataFrame

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

read_sql_query(sql, connection) runs the query and returns a DataFrame with the column names from the SELECT. SQLite has no separate date type, so dates arrive as text; convert them with parse_dates=["order_date"] or pd.to_datetime.

Every following example builds the same database again (each runnable block is independent). In your own project you connect once and keep working.

Aggregation: GROUP BY

Almost every business question is "how much, per what?". GROUP BY collapses rows into groups and aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise each group. WHERE filters rows before grouping; HAVING filters groups after:

Python Revenue per month and per country

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second query also joins two tables. A JOIN ... ON matches rows with equal keys: here each order finds its customer and brings the country along. This is the same idea as pd.merge (see Merge and Join); the database simply does it before the data reaches Python.

Finding what is missing: LEFT JOIN

An INNER JOIN keeps only rows that have a match on both sides. A LEFT JOIN keeps all rows from the left table and fills gaps with NULL. That is how you find customers who never ordered, the kind of question that is easy to get wrong with an inner join:

Python Customers with no orders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice COUNT(o.order_id) rather than COUNT(*): counting a column skips NULLs, so customers without orders correctly get 0. COALESCE(x, 0) replaces NULL by a default.

CTEs and window functions

Two features turn long, nested queries into readable analysis. A CTE (common table expression, WITH name AS (...)) names an intermediate result so the query reads from top to bottom. A window function computes something across related rows without collapsing them, for example a running total, a rank within a group, or a comparison with the previous row:

Python Running total, rank and month-over-month change

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

PARTITION BY region restarts the calculation inside each region, ORDER BY month fixes the order, and every original row survives. This is the SQL counterpart of groupby().cumsum(), .shift() and .rank() in pandas. When the same calculation is possible in both, choose by where the data is: in the database, use the window function; already in a DataFrame, use pandas.

Never build queries with string formatting

Suppose a user's input becomes part of the query: f"... WHERE country = '{country}'". A value such as x' OR '1'='1 changes the meaning of the query, an attack called SQL injection, and an accidental quote (O'Brien) simply breaks it. Use parameters: write placeholders in the SQL and pass the values separately, so the database never mixes data with code.

Python Parameterised queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The placeholder style depends on the driver: SQLite uses ? and :name, PostgreSQL's psycopg uses %s or %(name)s. Placeholders work for values only; table and column names cannot be parameters, so choose those from a fixed list in your own code.

Habits for analytic queries

  • Select only the columns you need, not SELECT *, and filter early with WHERE, on dates especially.
  • Look before you pull: run SELECT COUNT(*) or add LIMIT 1000 while developing a query on a large table.
  • Check the grain. Joining a customer table to an orders table multiplies rows. Verify that the result has the number of rows you expect and that totals still match (sum of revenue before and after a join).
  • Mind NULLs. NULL = NULL is not true; use IS NULL. Aggregates ignore NULLs, except COUNT(*).
  • Mind the dialect. Date functions differ between SQLite, PostgreSQL, MySQL and SQL Server (here strftime, elsewhere DATE_TRUNC or FORMAT).
  • Put queries in files under version control, and keep connection details (hosts, passwords) in environment variables.
  • Large results: pass chunksize= to read_sql_query to stream rows in pieces, or aggregate more in SQL.

Writing results back is the mirror image: df.to_sql("monthly_report", con, if_exists="replace", index=False) stores a DataFrame as a table. Connecting to a server database (PostgreSQL, MySQL) normally goes through SQLAlchemy (create_engine(url)); the pandas calls stay the same, as shown in Loading Data from CSV, JSON and SQL.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table has 100 million rows and you need monthly totals. What is the best approach?

    The database can aggregate close to the data and returns just the small result.
  2. 2. What is the difference between WHERE and HAVING?

    Row-level conditions go in WHERE; conditions on aggregates such as SUM(amount) > 500 need HAVING.
  3. 3. How do you list customers who never placed an order?

    A left join keeps every customer; customers with no match have NULLs in the order columns.
  4. 4. What does a window function such as SUM(x) OVER (PARTITION BY g ORDER BY d) do?

    Window functions calculate across related rows without collapsing them.
  5. 5. Why use query parameters (?) instead of f-strings in SQL?

    With placeholders the database treats values purely as data, so hostile or awkward input cannot change the query.

Summary

  • Let the database filter, join and aggregate; bring the small result into pandas with pd.read_sql_query.
  • GROUP BY summarises; WHERE filters rows, HAVING filters groups.
  • Use LEFT JOIN and IS NULL to find missing matches; use COUNT(column) and COALESCE for NULLs.
  • CTEs name intermediate steps; window functions give running totals, ranks and previous-row comparisons without collapsing rows.
  • Always pass values as parameters, never through string formatting.
  • Check the row count after joins, select only needed columns, and keep queries in version control.

Next: Sampling and Bias asks whether the data you collected can answer your question.