←Back to the bookChapter 154 · Challenge solutions

Chapter 154 · Tools & Workflow · Challenge Solutions

SQL & Databases · Solutions

Worked solutions to the five chapter challenges: a filtered SELECT, an aggregation with HAVING, a three-table join, a window ranking, and a CTE handed to pandas.

import pandas as pd, sqlite3
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "sql-and-databases--shop.xlsx"
try:
    xls = pd.ExcelFile("../../data/" + fn)
except FileNotFoundError:
    xls = pd.ExcelFile(BASE + fn)
# Load the four sheets into an in-memory SQLite database, then query it with real SQL.
con = sqlite3.connect(":memory:")
for table in ["customers", "products", "orders", "order_items"]:
    pd.read_excel(xls, sheet_name=table).to_sql(table, con, index=False, if_exists="replace")

def q(sql):
    """Run a SQL query against the shop database and return the result as a DataFrame."""
    return pd.read_sql_query(sql, con)

print("tables:", [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")])
q("SELECT * FROM products LIMIT 3")

tables: ['customers', 'products', 'orders', 'order_items']

	product_id	product	category	unit_price
0	1	Cold Brew Kit	Equipment	39
1	2	Pour-Over Set	Equipment	28
2	3	Espresso Beans 1kg	Beans	22

Challenge 1 · Filter and sort

List the Accessories products priced under 15 dollars, cheapest first.

q("""SELECT product, unit_price FROM products
       WHERE category = 'Accessories' AND unit_price < 15
       ORDER BY unit_price""")

	product	unit_price
0	Filter Papers	6
1	Descaler	9
2	Ceramic Mug	12

Challenge 2 · Aggregate with HAVING

Total revenue per product id, keeping only products that brought in more than 8,000 dollars.

q("""SELECT product_id, ROUND(SUM(revenue),2) AS revenue
       FROM order_items
       GROUP BY product_id
       HAVING SUM(revenue) > 8000
       ORDER BY revenue DESC""")

	product_id	revenue
0	12	20722.50
1	1	18879.90
2	8	14833.35
3	3	12521.30
4	2	11198.60

Challenge 3 · Three-table join

Revenue by customer segment (Consumer vs Business), joining line items to orders to customers.

q("""SELECT c.segment, ROUND(SUM(oi.revenue),2) AS revenue, COUNT(DISTINCT o.order_id) AS orders
       FROM order_items oi
       JOIN orders o    ON oi.order_id   = o.order_id
       JOIN customers c ON o.customer_id = c.customer_id
       GROUP BY c.segment
       ORDER BY revenue DESC""")

	segment	revenue	orders
0	Consumer	69138.65	364
1	Business	45453.75	236

Challenge 4 · Window ranking

Rank the channels by revenue with a window function, keeping the revenue alongside the rank.

q("""WITH ch AS (
         SELECT o.channel, SUM(oi.revenue) AS revenue
         FROM order_items oi JOIN orders o ON oi.order_id = o.order_id
         GROUP BY o.channel)
       SELECT channel, ROUND(revenue,2) AS revenue,
              RANK() OVER (ORDER BY revenue DESC) AS rnk
       FROM ch ORDER BY revenue DESC""")

	channel	revenue	rnk
0	web	57460.25	1
1	mobile	37776.25	2
2	store	19355.90	3

Challenge 5 · CTE to pandas

Use a CTE to get revenue per region, read it into pandas, and report each region's share of the total.

df = q("""WITH r AS (
            SELECT c.region, SUM(oi.revenue) AS revenue
            FROM order_items oi
            JOIN orders o    ON oi.order_id   = o.order_id
            JOIN customers c ON o.customer_id = c.customer_id
            GROUP BY c.region)
          SELECT region, ROUND(revenue,2) AS revenue FROM r ORDER BY revenue DESC""")
df["share"] = (df.revenue / df.revenue.sum() * 100).round(1)
print(df.to_string(index=False))

 region  revenue  share
  North 43069.15   37.6
   West 26514.30   23.1
Central 19658.15   17.2
   East 15197.55   13.3
  South 10153.25    8.9

←Back to the bookChapter 154 · Challenge solutions