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.
In [1]:
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']
Out[1]:
| 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.
In [2]:
q("""SELECT product, unit_price FROM products
WHERE category = 'Accessories' AND unit_price < 15
ORDER BY unit_price""")
Out[2]:
| 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.
In [3]:
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""")
Out[3]:
| 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.
In [4]:
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""")
Out[4]:
| 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.
In [5]:
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""")
Out[5]:
| 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.
In [6]:
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