Lesson 17 · Data analytics zero to hero
SQL Joins & Aggregations Explained | Data Analytics #17
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…
- CourseData analytics zero to hero
- Lesson17 of 30
- Video12 min
- FormatJupyter notebook · 8 code cells
What you'll learn
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbData 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')
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)
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)
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)
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)
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)
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)
conn.close()
print('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.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
What does a LEFT JOIN of
itemstoproductskeep?LEFT JOIN keeps every row from the left table (items); unmatched right-side columns are NULL.
What is
HAVINGused for?WHERE filters individual rows; HAVING filters aggregated groups.
In
FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id, what areoandc?Aliases shorten table names so you can write o.order_id instead of orders.order_id.
What does this compute?
sql SUM(CASE WHEN p.product_id IS NULL THEN 1 ELSE 0 END)
after a LEFT JOIN of items to products.
After a left join, NULL in the product columns means no match. CASE WHEN turns each such row into 1 and SUM counts them.
What happens to rows with no match in an INNER JOIN?
INNER JOIN returns only rows whose keys match in both tables.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Create two tiny tables, orders and customers, in SQLite and INNER JOIN them to show order_id and customer_state.
💡 Hint
Use pd.DataFrame(...).to_sql(...) and a JOIN ... ON query.
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') pd.DataFrame({'order_id': ['o1', 'o2', 'o3'], 'customer_id': ['c1', 'c2', 'c9']}).to_sql('orders', conn, index=False) pd.DataFrame({'customer_id': ['c1', 'c2'], 'customer_state': ['SP', 'RJ']}).to_sql('customers', conn, index=False) q = ''' SELECT o.order_id, c.customer_state FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ''' print(pd.read_sql(q, conn)) conn.close()
LEFT JOIN items to products and count the total items and the unmatched ones with SUM(CASE WHEN ...).
💡 Hint
LEFT JOIN, then check p.product_id IS NULL.
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') pd.DataFrame({'order_id': ['o1', 'o2', 'o3'], 'product_id': ['p1', 'p2', 'p9'], 'price': [10.0, 20.0, 5.0]}).to_sql('items', conn, index=False) pd.DataFrame({'product_id': ['p1', 'p2'], 'product_category_name': ['toys', 'books']}).to_sql('products', conn, index=False) q = ''' 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 ''' print(pd.read_sql(q, conn)) conn.close()
Join items, products and a translation table, then GROUP BY the English category to get number of items and total revenue (rounded to 2 decimals), keeping only categories with at least 2 items using HAVING.
💡 Hint
Join the three tables, GROUP BY, then HAVING COUNT(*) >= 2.
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') pd.DataFrame({'product_id': ['p1', 'p2', 'p1', 'p3'], 'price': [10.5, 20.0, 10.5, 7.25]}).to_sql('items', conn, index=False) pd.DataFrame({'product_id': ['p1', 'p2', 'p3'], 'product_category_name': ['brinquedos', 'livros', 'livros']}).to_sql('products', conn, index=False) pd.DataFrame({'product_category_name': ['brinquedos', 'livros'], 'product_category_name_english': ['toys', 'books']}).to_sql('translation', conn, index=False) q = ''' 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 HAVING COUNT(*) >= 2 ORDER BY total_revenue DESC ''' print(pd.read_sql(q, conn)) conn.close()
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



