Lesson 17 · Data analytics zero to hero

SQL Joins & Aggregations Explained | Data Analytics #17

Video seventeen of the 30-part series: JOIN, GROUP BY, and HAVING, combining multiple real tables the way real analyst SQL usually looks. We're back to the…

⬇ Download notebook Open in Colab ↗

CourseData analytics zero to hero
Lesson17 of 30
Video12 min
FormatJupyter notebook · 8 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 17: SQL Joins and Aggregations#

Video seventeen of the 30-part series: JOIN, GROUP BY, and HAVING, combining multiple real tables the way real analyst SQL usually looks.
We're back to the real, multi-table Olist Brazilian E-Commerce extract from video eleven, this time queried with SQL instead of pandas merge.
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.

Part 1: Loading Multiple Real Tables into SQLite#

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('category_translation.csv').to_sql('translation', conn, index=False, if_exists='replace')
print('All five real tables loaded')

All five real tables loaded

Part 2: INNER JOIN#

query = '''
SELECT o.order_id, o.order_status, c.customer_state
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id order_status customer_state
0  e481f51cbdc54678b7cc49136f2d6af7    delivered             SP
1  53cdb2fc8bc7dce0b6741e2150273451    delivered             BA
2  47770eb9100c2d0c44946d9cf07ec65d    delivered             GO
3  949d5b44dbf5de918fe9c16f97b45f8a    delivered             RN
4  ad21c59c0840e6cb83a9ceb5573f8159    delivered             SP

Part 3: LEFT JOIN#

query = '''
SELECT i.order_id, i.product_id, p.product_category_name, i.price
FROM items i
LEFT JOIN products p ON i.product_id = p.product_id
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id                        product_id  \
0  00125cb692d04887809806618a2a145f  1c0c0093a48f13ba70d0c6b0a9157cb7   
1  00571ded73b3c061925584feab0db425  8695c431b31927efef5343e675f279e7   
2  00571ded73b3c061925584feab0db425  8695c431b31927efef5343e675f279e7   
3  00946f674d880be1f188abc10ad7cf46  4dcb49b9ca7e48d2f108d40caa77caa2   
4  00946f674d880be1f188abc10ad7cf46  9bb2d066e4b33b624cbdfec7d50b3dcb   

  product_category_name  price  
0      moveis_decoracao  109.9  
1            perfumaria  179.9  
2            perfumaria  179.9  
3              pet_shop   99.9  
4              pet_shop   99.9

query = '''
SELECT
    COUNT(*) AS total_items,
    SUM(CASE WHEN p.product_id IS NULL THEN 1 ELSE 0 END) AS unmatched_items
FROM items i
LEFT JOIN products p ON i.product_id = p.product_id
'''
result = pd.read_sql(query, conn)
print(result)

   total_items  unmatched_items
0         1365                0

Part 4: Joining Three Real Tables at Once#

query = '''
SELECT o.order_id, c.customer_state, p.product_category_name, 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
JOIN products p ON i.product_id = p.product_id
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

                           order_id customer_state  product_category_name  \
0  e481f51cbdc54678b7cc49136f2d6af7             SP  utilidades_domesticas   
1  53cdb2fc8bc7dce0b6741e2150273451             BA             perfumaria   
2  47770eb9100c2d0c44946d9cf07ec65d             GO             automotivo   
3  949d5b44dbf5de918fe9c16f97b45f8a             RN               pet_shop   
4  ad21c59c0840e6cb83a9ceb5573f8159             SP              papelaria   

    price  
0   29.99  
1  118.70  
2  159.90  
3   45.00  
4   19.90

Part 5: GROUP BY Across a Join#

query = '''
SELECT t.product_category_name_english AS category, COUNT(*) AS num_items, ROUND(SUM(i.price), 2) AS total_revenue
FROM items i
JOIN products p ON i.product_id = p.product_id
JOIN translation t ON p.product_category_name = t.product_category_name
GROUP BY t.product_category_name_english
ORDER BY total_revenue DESC
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)

         category  num_items  total_revenue
0   health_beauty        139       17953.39
1  sports_leisure        131       15970.22
2   watches_gifts         65       13785.66
3  bed_bath_table        125       11810.30
4      cool_stuff         49        9339.41

Part 6: HAVING#

query = '''
SELECT customer_state, COUNT(*) AS num_orders
FROM customers
GROUP BY customer_state
HAVING COUNT(*) >= 20
ORDER BY num_orders DESC
'''
result = pd.read_sql(query, conn)
print(result)

   customer_state  num_orders
0              SP         512
1              RJ         134
2              MG         120
3              RS          73
4              PR          72
5              BA          54
6              SC          36
7              GO          33
8              DF          27
9              ES          24
10             PE          23

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

Connection closed

Wrap-Up: What You Learned#

Loading several real CSVs into separate tables in one shared SQLite database.
INNER JOIN, for keeping only matched rows across two real tables.
LEFT JOIN, for keeping every row from the first table, and checking for unmatched rows with CASE WHEN.
Chaining multiple JOIN clauses to combine three or more real tables in one query.
GROUP BY after a join, and HAVING for filtering on aggregated results.
All of it run against the real, multi-table Olist e-commerce dataset. Video eighteen puts SQL and Python together directly: subqueries, and passing query results straight into pandas for further real analysis. 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