Code / SQL with Data / Unit 5: Joins, changes and the final project

Final project: Kirana shop monthly report फ़ाइनल प्रोजेक्ट: किराना दुकान की मासिक रिपोर्ट

Lesson 13 of 13 · about 20 minutes · Bills with amounts, then the October report by category.

⚡ 0 XP

Step 1 of 2

Bill lines with amounts

The shopkeeper, Meena ji, asks: “How much money did each bill line bring?” The sales table has qty but no price; products has the price. JOIN them, then multiply:

s.qty * p.price AS amount

This is the building block of every sales report: join the facts (sales) with the details (products), then calculate.

Example

SELECT s.id, p.name, s.qty
FROM sales s
JOIN products p
  ON p.id = s.product_id;

🎯 Challenge: Bill lines with ₹ amounts +25 XP

Join sales with products. Show day, the product name, qty, price and qty * price named amount, for every sale, sorted by the sale’s id. Columns in this order: day, name, qty, price, amount.

Try twice to unlock the solution (0/2)

query.sqlCtrl ⏎

Tables (practice database)

Results

Press Run to see your query’s rows here.

Checklist

Press Run to check your code.

Your rows match the expected answer, in the right order
Uses JOIN

Lesson notes (text version)

1. Bill lines with amounts

The shopkeeper, Meena ji, asks: “How much money did each bill line bring?” The sales table has qty but no price; products has the price. JOIN them, then multiply:

s.qty * p.price AS amount

This is the building block of every sales report: join the facts (sales) with the details (products), then calculate.

                SELECT s.id, p.name, s.qty
FROM sales s
JOIN products p
  ON p.id = s.product_id;

Challenge: Bill lines with ₹ amounts

2. The October report

Now the big one. Meena ji’s question: “In October, which categories earned at least ₹400, and which earned the most?”

This needs everything you learned, in this order:

JOIN sales with products (to get price and category)
WHERE — only October: days look like 2026-10-05, so s.day LIKE '2026-10-%' (two sales are from September!)
GROUP BY p.category
SUM(s.qty * p.price) AS revenue
HAVING revenue of at least 400
ORDER BY revenue DESC

Write it step by step and press Run after each step to see the result grow. This is exactly the kind of query data analysts write every day.

Challenge: Kirana shop monthly report

Write one query with two columns, category and revenue (SUM(qty * price)), for sales in October 2026 only (day LIKE '2026-10-%'). Keep only categories with revenue of at least 400, sorted by revenue highest first. It must use JOIN, GROUP BY and HAVING.