Mathew K Analytics

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…

⬇ Download notebook🧠 Quiz & exercises

What you'll learn

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.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. What does a LEFT JOIN of items to products keep?

  2. What is HAVING used for?

  3. In FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id, what are o and c?

  4. 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.

  5. What happens to rows with no match in an INNER JOIN?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. 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()
    
  2. 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()
    
  3. 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.