🛢️ Week 6 – SQL for Data Science · Day 35
Day 35: Project: SQL + Pandas HR Analytics
⏱ 3–4 hours📶 Project🎯 100% self-study
Day 35 / 60
✅ Today you will learn
- Design a small star schema (fact + dimensions)
- Create tables with keys and load data
- Build a reusable analysis VIEW
- Answer 8 business questions in SQL
- Hand results to pandas for charts and a report
🏢 In the real world
BazaarKart is moving from Excel to a database. You are asked to set up a mini data warehouse, load 2024 orders, and prepare the quarterly business review: which cities hit their targets, who is growing, which products lead, and how customer segments behave. This is exactly what junior analysts do in their first months.
Step 1: design the schema
Region and category are stored once in dimension tables instead of on every order row – less duplication and one place to fix a typo.
Step 2: create and load
Python
import sqlite3
import pandas as pd
s = pd.read_csv("sales_data.csv")
con = sqlite3.connect("bazaarkart_dw.db")
con.executescript("""
DROP TABLE IF EXISTS fact_orders; DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_city; DROP TABLE IF EXISTS city_targets;
CREATE TABLE dim_city (city TEXT PRIMARY KEY, region TEXT NOT NULL);
CREATE TABLE dim_product (product TEXT PRIMARY KEY, category TEXT NOT NULL);
CREATE TABLE fact_orders (
order_id TEXT PRIMARY KEY, order_date TEXT, city TEXT REFERENCES dim_city(city),
product TEXT REFERENCES dim_product(product), quantity INTEGER, unit_price REAL,
discount REAL, payment_mode TEXT, customer_age REAL, customer_gender TEXT, rating REAL);
CREATE TABLE city_targets (city TEXT, quarter INTEGER, target_lakh REAL);
""")
s[["city", "region"]].drop_duplicates().to_sql("dim_city", con, if_exists="append", index=False)
s[["product", "category"]].drop_duplicates().to_sql("dim_product", con, if_exists="append", index=False)
s.drop(columns=["region", "category"]).to_sql("fact_orders", con, if_exists="append", index=False)
targets = [(c, qtr, t) for c, t in [("Mumbai", 2.4), ("Bengaluru", 2.4), ("Delhi", 2.3), ("Kolkata", 2.0),
("Hyderabad", 1.5), ("Chennai", 1.4), ("Patna", 1.1), ("Ahmedabad", 1.1), ("Pune", 1.2),
("Lucknow", 1.0), ("Jaipur", 0.7)] for qtr in (1, 2, 3, 4)]
con.executemany("INSERT INTO city_targets VALUES (?, ?, ?)", targets)
con.execute("CREATE INDEX idx_orders_date ON fact_orders(order_date)")
con.commit()
for t in ["dim_city", "dim_product", "fact_orders", "city_targets"]:
print(t, con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0], "rows")dim_city 11 rows
dim_product 25 rows
fact_orders 2000 rows
city_targets 44 rows
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
con.executescript("""
DROP VIEW IF EXISTS v_orders;
CREATE VIEW v_orders AS
SELECT o.*, c.region, p.category,
o.quantity * o.unit_price * (1 - o.discount) AS revenue,
CAST(strftime('%m', o.order_date) AS INTEGER) AS month,
(CAST(strftime('%m', o.order_date) AS INTEGER) + 2) / 3 AS quarter
FROM fact_orders o
JOIN dim_city c ON o.city = c.city
JOIN dim_product p ON o.product = p.product;
""")
print(q("SELECT order_id, city, region, category, ROUND(revenue) AS revenue, quarter FROM v_orders LIMIT 4")) order_id city region category revenue quarter
0 BK100359 Delhi North Fashion 2440.0 1
1 BK100422 Mumbai West Fashion 2094.0 1
2 BK100744 Mumbai West Grocery 2625.0 1
3 BK100830 Mumbai West Books & Stationery 139.0 1
Q1: Did each city hit its quarterly target?
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
tv = q("""
WITH actual AS (
SELECT city, quarter, SUM(revenue) / 1e5 AS actual_lakh FROM v_orders GROUP BY city, quarter
)
SELECT t.city, t.quarter, t.target_lakh, ROUND(a.actual_lakh, 2) AS actual_lakh,
ROUND(100 * a.actual_lakh / t.target_lakh) AS achievement_pct
FROM city_targets t LEFT JOIN actual a ON a.city = t.city AND a.quarter = t.quarter
""")
pv = tv.pivot(index="city", columns="quarter", values="achievement_pct")
print(pv.astype(int))
print("City-quarters on target:", (tv["achievement_pct"] >= 100).sum(), "of", len(tv))quarter 1 2 3 4
city
Ahmedabad 122 57 90 140
Bengaluru 118 85 74 124
Chennai 65 124 82 119
Delhi 87 67 113 143
Hyderabad 102 73 42 164
Jaipur 120 57 63 131
Kolkata 61 113 86 160
Lucknow 61 61 116 157
Mumbai 163 72 80 120
Patna 198 45 47 129
Pune 67 73 74 123
City-quarters on target: 21 of 44
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
import seaborn as sns
import matplotlib.pyplot as plt
tv = q("""WITH a AS (SELECT city, quarter, SUM(revenue) / 1e5 AS act FROM v_orders GROUP BY city, quarter)
SELECT t.city, t.quarter, ROUND(100 * a.act / t.target_lakh) AS pct
FROM city_targets t JOIN a ON a.city = t.city AND a.quarter = t.quarter""")
pv = tv.pivot(index="city", columns="quarter", values="pct")
pv = pv.loc[pv.mean(axis=1).sort_values(ascending=False).index]
plt.figure(figsize=(7, 5))
sns.heatmap(pv, annot=True, fmt=".0f", cmap="RdYlGn", center=100, cbar_kws=dict(label="% of target"))
plt.title("Target achievement by city and quarter (%)"); plt.xlabel("Quarter"); plt.ylabel("")
plt.show()
Q2: Which region grew fastest from H1 to H2?
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT region,
ROUND(SUM(CASE WHEN month <= 6 THEN revenue END) / 1e5, 2) AS h1_lakh,
ROUND(SUM(CASE WHEN month > 6 THEN revenue END) / 1e5, 2) AS h2_lakh,
ROUND(100.0 * (SUM(CASE WHEN month > 6 THEN revenue END) - SUM(CASE WHEN month <= 6 THEN revenue END))
/ SUM(CASE WHEN month <= 6 THEN revenue END), 1) AS growth_pct
FROM v_orders GROUP BY region ORDER BY growth_pct DESC
""")) region h1_lakh h2_lakh growth_pct
0 North 6.01 10.00 66.5
1 East 6.16 6.84 11.1
2 South 10.15 10.67 5.2
3 West 9.27 9.71 4.8
💡 Conditional aggregation
SUM(CASE WHEN … THEN revenue END) pivots data inside SQL – one of the most useful patterns for reports.
Q3: Top product per category and its share
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
WITH p AS (
SELECT category, product, SUM(revenue) AS rev,
SUM(SUM(revenue)) OVER (PARTITION BY category) AS cat_rev,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS rn
FROM v_orders GROUP BY category, product
)
SELECT category, product, ROUND(rev) AS revenue, ROUND(100 * rev / cat_rev, 1) AS share_of_category_pct
FROM p WHERE rn = 1 ORDER BY revenue DESC
""")) category product revenue share_of_category_pct
0 Electronics Mobile Phone 1801233.0 46.9
1 Fashion Sneakers 604886.0 39.9
2 Grocery Dry Fruits 1kg 247044.0 31.3
3 Home & Kitchen Mixer Grinder 209764.0 49.3
4 Books & Stationery NCERT Book Set 140285.0 45.3
Q4: Prepaid vs COD – value and satisfaction
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT CASE WHEN payment_mode = 'Cash on Delivery' THEN 'COD' ELSE 'Prepaid' END AS pay_type,
COUNT(*) AS orders, ROUND(AVG(revenue)) AS aov, ROUND(AVG(rating), 2) AS avg_rating,
ROUND(100.0 * SUM(CASE WHEN rating <= 3 THEN 1 ELSE 0 END) / COUNT(rating), 1) AS low_rating_pct
FROM v_orders GROUP BY pay_type
""")) pay_type orders aov avg_rating low_rating_pct
0 COD 391 4142.0 4.06 11.2
1 Prepaid 1609 3269.0 4.06 12.7
Q5: Customer age segments
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT CASE WHEN customer_age IS NULL THEN 'Unknown'
WHEN customer_age < 25 THEN '18-24' WHEN customer_age < 35 THEN '25-34'
WHEN customer_age < 45 THEN '35-44' ELSE '45+' END AS age_group,
COUNT(*) AS orders, ROUND(SUM(revenue) / 1e5, 2) AS revenue_lakh,
ROUND(100.0 * SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) / SUM(revenue), 1) AS electronics_share_pct
FROM v_orders GROUP BY age_group ORDER BY age_group
""")) age_group orders revenue_lakh electronics_share_pct
0 18-24 429 14.41 57.3
1 25-34 816 29.76 57.3
2 35-44 570 17.26 49.1
3 45+ 160 6.86 64.6
4 Unknown 25 0.51 35.0
Q6: Best and worst weeks
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
w = q("""
SELECT strftime('%W', order_date) AS week, MIN(order_date) AS week_start,
COUNT(*) AS orders, ROUND(SUM(revenue) / 1e5, 2) AS revenue_lakh
FROM v_orders GROUP BY week
""")
print("Best 3 weeks:\n", w.nlargest(3, "revenue_lakh").to_string(index=False))
print("Worst 3 weeks:\n", w.nsmallest(3, "revenue_lakh").to_string(index=False))Best 3 weeks:
week week_start orders revenue_lakh
45 2024-11-04 70 4.13
47 2024-11-18 74 2.13
40 2024-09-30 54 2.12
Worst 3 weeks:
week week_start orders revenue_lakh
53 2024-12-30 12 0.31
17 2024-04-22 22 0.51
27 2024-07-01 17 0.54
Q7: Cities ranked by quarter-on-quarter growth in Q4
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
WITH cq AS (SELECT city, quarter, SUM(revenue) AS rev FROM v_orders GROUP BY city, quarter),
g AS (SELECT city, quarter, rev, LAG(rev) OVER (PARTITION BY city ORDER BY quarter) AS prev FROM cq)
SELECT city, ROUND(prev / 1e5, 2) AS q3_lakh, ROUND(rev / 1e5, 2) AS q4_lakh,
ROUND(100 * (rev - prev) / prev, 1) AS qoq_pct,
RANK() OVER (ORDER BY (rev - prev) / prev DESC) AS rnk
FROM g WHERE quarter = 4 ORDER BY rnk
""")) city q3_lakh q4_lakh qoq_pct rnk
0 Hyderabad 0.63 2.46 287.4 1
1 Patna 0.51 1.42 175.4 2
2 Jaipur 0.44 0.92 106.0 3
3 Kolkata 1.72 3.19 86.0 4
4 Bengaluru 1.78 2.98 67.2 5
5 Pune 0.89 1.48 66.9 6
6 Ahmedabad 0.99 1.54 55.2 7
7 Mumbai 1.92 2.89 50.4 8
8 Chennai 1.15 1.66 45.0 9
9 Lucknow 1.16 1.57 35.6 10
10 Delhi 2.61 3.30 26.5 11
Q8: Export a summary table for management
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
summary = q("""
SELECT region, city,
COUNT(*) AS orders, ROUND(SUM(revenue)) AS revenue, ROUND(AVG(revenue)) AS aov,
ROUND(AVG(rating), 2) AS rating,
ROUND(100.0 * SUM(CASE WHEN payment_mode <> 'Cash on Delivery' THEN 1 ELSE 0 END) / COUNT(*), 1) AS prepaid_pct
FROM v_orders GROUP BY region, city ORDER BY revenue DESC
""")
summary.to_csv("qbr_city_summary.csv", index=False)
print(summary.head(6).to_string(index=False))
print("Saved qbr_city_summary.csv with", len(summary), "rows")region city orders revenue aov rating prepaid_pct
West Mumbai 252 1043230.0 4140.0 3.99 77.8
South Bengaluru 299 964020.0 3224.0 4.07 80.6
North Delhi 271 945592.0 3489.0 4.00 79.3
East Kolkata 193 839885.0 4352.0 4.06 82.4
South Hyderabad 200 572190.0 2861.0 4.09 77.5
South Chennai 155 545479.0 3519.0 4.16 84.5
Saved qbr_city_summary.csv with 11 rows
Findings for the quarterly business review
🎯
Targets are seasonalFlat quarterly targets are missed in Q2–Q3 and beaten in Q4. Set seasonal targets.📱
Phones & laptops leadIn Electronics one or two products bring most revenue – stock them first.💳
Push prepaidCompare COD vs prepaid AOV and ratings to size a UPI cashback.🗄️
One source of truthThe view v_orders gives every analyst the same revenue definition.🏅 Job skills unlocked today
✔ Design and create a star schema✔ Load data and create views/indexes✔ Answer business questions with CTEs and windows✔ Combine SQL and pandas into a report
Practice tasks
🏋️ Practice – do it yourself
Task 1: Which category had the highest share of low ratings (≤ 3)?
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaarkart_dw.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT category, ROUND(100.0 * SUM(CASE WHEN rating <= 3 THEN 1 ELSE 0 END) / COUNT(rating), 1) AS low_pct
FROM v_orders GROUP BY category ORDER BY low_pct DESC""")) category low_pct
0 Fashion 13.4
1 Electronics 13.2
2 Grocery 11.9
3 Home & Kitchen 11.5
4 Books & Stationery 10.7
Task 2: Add a
dim_date table (date, month, quarter, weekday, is_festive) and join it to answer: festive vs normal average daily revenue.Task 3: Write the 4-line management summary for this QBR.
Quick quiz
🧠 Quick Quiz – test yourself
1. In a star schema, the fact table holds…
Dimensions describe; facts measure.
2. A VIEW is…
It stores the definition, not the rows.
3. SUM(CASE WHEN month <= 6 THEN revenue END) computes…
Conditional aggregation.
4. An index on order_date mainly…
Like a book index.
5. Why keep region in dim_city instead of every order?
Normalisation.
Interview corner
💼 Interview Corner
What is the difference between OLTP and OLAP databases?
OLTP systems (app databases) handle many small reads/writes and are normalised. OLAP systems (warehouses like BigQuery, Redshift, Snowflake) are optimised for big analytical queries, often with star schemas.
Describe a SQL project you built.
Use this project: designed a star schema, loaded 2,000 orders, created a reusable view with one revenue definition, and answered target achievement, growth, product ranking and segment questions with CTEs and window functions; exported results for a QBR deck.
Summary
- Star schema: fact table + dimension tables linked by keys.
- Views give everyone one definition of metrics.
- Conditional aggregation, CTEs and window functions answer real business questions.
- Week 6 done – SQL ✔. Next stop: machine learning!
➡️ Next
Week 7: Machine Learning. Day 36: what ML really is, types of learning and the ML workflow.
Frequently Asked Questions
Can I do this in MySQL instead of SQLite?
Yes. Replace strftime with DATE_FORMAT/MONTH()/QUARTER() and create the database in MySQL Workbench or phpMyAdmin; the rest is the same.
Should analysis be in SQL or pandas?
Do heavy filtering, joining and aggregation in SQL (close to the data), then use pandas for statistics, charts and modelling on the smaller result.
