Skip to content
0

🤖 Learn SQL with AI: Mini Business Cases

📖 Background

Welcome to PageTurner Books, a small online bookstore! You've just joined as a data analyst, and your manager needs quick answers from the sales database.

The good news? You have an AI assistant to help you write SQL queries. The key is not just getting the answer—it's understanding what each query does.

In this challenge, you'll tackle real business questions using SQL. For each question:

  1. Use the AI assistant to help write your SQL query
  2. Run the query to get results
  3. Explain in plain English what the query does and why

This is how data analysts work in the real world—AI helps with the syntax, but you need to understand the logic!

💡 Tip: While AI can help you write queries, the real learning happens when you explain what they do in your own words. Take time to understand each query and write your explanations yourself—that's where the magic happens!

📊 The Database

PageTurner Books has three tables:

TableDescription
booksOur book catalog (book_id, title, author, genre, price)
customersCustomer information (customer_id, name, city, signup_date)
ordersSales transactions (order_id, customer_id, book_id, quantity, order_date)

💡 Tip: In DataLab, SQL cells can query CSV files directly using the path, like FROM "data/books.csv"

Note: These datasets are synthetic, and any titles that match real books is coincidental.

Spinner
DataFrameas
books
variable
CREATE TABLE books AS
SELECT *
FROM "data/books.csv";

SELECT * FROM books
Spinner
DataFrameas
customers
variable
CREATE TABLE customers AS
SELECT *
FROM "data/customers.csv";

SELECT * FROM customers
Spinner
DataFrameas
orders
variable
CREATE TABLE orders AS
SELECT *
FROM "data/orders.csv";

SELECT * FROM orders

💪 The Challenges

Your manager has 4 business questions. For each one:

  1. Ask the AI assistant for help writing the SQL query
  2. Run the query and check the results
  3. Explain in your own words what the SQL does

💡 Tip: Use the AI chat to ask things like "How do I count rows grouped by a column in SQL?" or "Help me write a query to find the top 5 customers by total spending."

📚 Challenge 1: Genre Inventory

Business Question: "How many books do we have in each genre?"

Your manager wants to know if the inventory is balanced across genres.

Spinner
DataFrameas
df
variable
SELECT 
    COALESCE(genre, 'Unknown') AS genre,
    COUNT(1) AS books_count
FROM books
GROUP BY COALESCE(genre, 'Unknown')
ORDER BY books_count DESC, genre ASC
LIMIT 5;

✍️ Your explanation: What does this query do? Explain it like you're telling a colleague who doesn't know SQL.

This query counts how many books we have in each genre in our inventory.

It groups all the books by their genre and counts the number of books in each group. If a book doesn't have a genre listed (i.e., the genre is missing), it labels it as "Unknown" so those books aren't left out.

The results are sorted to show the genres with the most books at the top, and it only displays the top 5 genres.

This helps us quickly see which genres are most common in our store and spot any gaps or imbalances in our inventory.

📅 Challenge 2: January Sales

Business Question: "What was our total revenue in January 2024?"

Finance needs this for the monthly report.

💡 Hint: Filter orders by date and calculate revenue.

Spinner
DataFrameas
df
variable
SELECT 
    ROUND(SUM(o.quantity * b.price), 2) AS january_2024_revenue
FROM 
    orders AS o
INNER JOIN 
    books AS b
    ON o.book_id = b.book_id
WHERE 
    o.order_date >= DATE '2024-01-01'
    AND o.order_date < DATE '2024-02-01';

✍️ Your explanation: What does this query do? How does it filter for January specifically?

To calculate the total revenue for January 2024, revenue is defined as the product of the number of units sold and the unit price of each book.

Since the quantity sold is stored in the orders table and the unit price is stored in the books table, the query joins both tables using the book_id as the relational key.

For each order, the revenue is computed as:

revenue = quantity × price

The query then aggregates these values using SUM() and filters the results to include only orders placed in January 2024.

🏆 Challenge 3: Top Customers

Business Question: "Who are our top 5 customers by total spending?"

The marketing team wants to send thank-you gifts to the best customers. You'll need to combine data from multiple tables to answer this.

💡 Hint: You'll need to JOIN tables and calculate total spending (price × quantity).

Spinner
DataFrameas
df
variable
SELECT 
    o.customer_id,
    ROUND(SUM(o.quantity * b.price), 2) AS total_spending
FROM 
    orders AS o
INNER JOIN 
    books AS b
    ON o.book_id = b.book_id
GROUP BY 
    o.customer_id
ORDER BY 
    total_spending DESC
LIMIT 5;
‌
‌
‌