Lesson 18 · Data analytics zero to hero

Python + SQL: Real Analyst Queries | Data Analytics #18

Video eighteen of the 30-part series, and the last stop in the SQL block: subqueries, CTEs, window functions, and safe parameterized queries. Still the…

⬇ Download notebook Open in Colab ↗

CourseData analytics zero to hero
Lesson18 of 30
Video12 min
FormatJupyter notebook · 9 code cells

SQLite pandas

Data

No separate download needed — the notebook creates or downloads everything it uses.

📓 Full notebook

Download .ipynb

Data Analytics Zero to Hero, Video 18: Python + SQL, Building Analyst Queries#

Video eighteen of the 30-part series, and the last stop in the SQL block: subqueries, CTEs, window functions, and safe parameterized queries.
Still the real, multi-table Olist e-commerce dataset from the last two videos.
Let's jump straight in.

Before You Start#

Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
Place orders_sample.csv, customers_sample.csv, order_items_sample.csv, products_sample.csv, category_translation.csv, and payments_sample.csv in the same folder as this notebook.

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
pd.read_csv('orders_sample.csv').to_sql('orders', conn, index=False, if_exists='replace')
pd.read_csv('customers_sample.csv').to_sql('customers', conn, index=False, if_exists='replace')
pd.read_csv('order_items_sample.csv').to_sql('items', conn, index=False, if_exists='replace')
pd.read_csv('products_sample.csv').to_sql('products', conn, index=False, if_exists='replace')
pd.read_csv('payments_sample.csv').to_sql('payments', conn, index=False, if_exists='replace')
print('All five real tables loaded')

All five real tables loaded

Part 1: Subqueries#

query = '''
SELECT order_id, payment_value
FROM payments
WHERE payment_value > (SELECT AVG(payment_value) FROM payments)
ORDER BY payment_value DESC
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id  payment_value
0  da8be3bb62e9bf01e2e1a3bfd74ebd1a        2751.24
1  947ee6ab639791b5711558a7e55cf98e        2223.12
2  6cb134bb285a64b0425d1fdaa00d4214        2031.09
3  4f7ce3efe568a5e57290a9fa3e45b1f5        1727.67
4  dd6d0f11a9c3d2abdc91e95b9598b332        1692.05

query = '''
SELECT customer_id, customer_state
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE order_status = 'delivered'
)
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                        customer_id customer_state
0  df0aa5b8586495e0ddf6b601122e43a1             SP
1  ae8db0691449a44352e7d535ddf78c5e             SP
2  4b003ee1eabaffe8ff8e6d75394a9b42             BA
3  1681471e9172d7e12ba08e9ac9e8628b             RJ
4  a4a00c41b5e3ed88bf0e0bb96576c2b2             RJ

Part 2: Common Table Expressions with WITH#

query = '''
WITH order_totals AS (
    SELECT order_id, SUM(price) AS order_total
    FROM items
    GROUP BY order_id
)
SELECT order_id, order_total
FROM order_totals
WHERE order_total > 200
ORDER BY order_total DESC
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id  order_total
0  da8be3bb62e9bf01e2e1a3bfd74ebd1a      2690.00
1  947ee6ab639791b5711558a7e55cf98e      2139.99
2  6cb134bb285a64b0425d1fdaa00d4214      1999.00
3  4f7ce3efe568a5e57290a9fa3e45b1f5      1695.00
4  dd6d0f11a9c3d2abdc91e95b9598b332      1599.00

Part 3: Window Functions#

query = '''
SELECT customer_state, COUNT(*) AS num_customers,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS state_rank
FROM customers
GROUP BY customer_state
LIMIT 8
'''
result = pd.read_sql(query, conn)
print(result)

  customer_state  num_customers  state_rank
0             SP            512           1
1             RJ            134           2
2             MG            120           3
3             RS             73           4
4             PR             72           5
5             BA             54           6
6             SC             36           7
7             GO             33           8

query = '''
SELECT order_id, price,
    SUM(price) OVER (ORDER BY order_id) AS running_total
FROM items
ORDER BY order_id
LIMIT 6
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id  price  running_total
0  00125cb692d04887809806618a2a145f  109.9          109.9
1  00571ded73b3c061925584feab0db425  179.9          469.7
2  00571ded73b3c061925584feab0db425  179.9          469.7
3  00946f674d880be1f188abc10ad7cf46   99.9          669.5
4  00946f674d880be1f188abc10ad7cf46   99.9          669.5
5  00bdcdda88e6b02977fc6ce3d412c600  118.9          788.4

Part 4: Parameterized Queries#

target_state = 'SP'
query = 'SELECT customer_id, customer_city FROM customers WHERE customer_state = ? LIMIT 5'
result = pd.read_sql(query, conn, params=(target_state,))
print(result)

                        customer_id          customer_city
0  df0aa5b8586495e0ddf6b601122e43a1                 sumare
1  ae8db0691449a44352e7d535ddf78c5e                guaruja
2  e773252cc3222d0c3ae7694b271e2e32  santa rosa de viterbo
3  4cd274ce8ba240b1cff6480fa9911df1         ribeirao pires
4  2be831e199cd5308b6e3ab6f36718526            sao vicente

Part 5: Handing a Query's Result to pandas#

query = '''
SELECT o.order_id, c.customer_state, i.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN items i ON o.order_id = i.order_id
'''
combined = pd.read_sql(query, conn)
state_summary = combined.groupby('customer_state')['price'].agg(['sum', 'mean', 'count']).sort_values('sum', ascending=False)
print(state_summary.head(5))

                     sum        mean  count
customer_state                             
SP              61148.05  103.290625    592
RJ              22778.29  154.954354    147
MG              14640.47  107.650515    136
RS              11269.20  142.648101     79
PR              10367.07  126.427683     82

conn.close()
print('Connection closed')

Connection closed

Wrap-Up: What You Learned#

Subqueries: scalar subqueries in WHERE, and IN with a subquery result set.
Common Table Expressions with WITH, for naming a subquery and keeping complex real queries readable.
Window functions: RANK and a running total with SUM OVER, computing per-row values without collapsing groups.
Parameterized queries with ? placeholders, avoiding SQL injection when a query depends on a Python variable.
Feeding a real SQL query's result straight into ordinary pandas analysis.
This wraps up the SQL block. Video nineteen starts an Excel automation block: building real workbooks, formulas, and formatting entirely with Python. Subscribe so it lands automatically see you there.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help