Capstone Part 2: Final Analysis

From Raw Data to Business Insights

In this final step, we transform a long list of raw records into a high-level business summary. Think of your data like a stack of grocery receipts.

Welcome to the final stage of your capstone project where we turn raw data into actionable insights. Imagine you have a stack of a hundred grocery receipts from the last year. To find your spending, you'd first filter out older years. Then, you'd group them into piles like 'Dairy' or 'Produce'. Finally, you'd sum the totals for each pile to get your answer.

• Raw data needs structure to provide value.
• SQL mimics real-world categorization and summation.

The Capstone Schema

We will use these three interconnected tables to build our final report: Customers, Orders, and Order_Items.

Before we write the query, let's review our schema. The Customers table gives us city data. The Orders table tracks the dates. And the Order_Items table contains the price and quantity we need for revenue.

• Customers link to Orders via customer_id.
• Orders link to Order_Items via order_id.

The Logical Order of SQL

SQL queries must follow a strict clause order to execute correctly. Think of it as a funnel that narrows down your data.

To build a final analysis, you must follow this specific sequence. First, we select our columns and join our tables. Next, we use WHERE to remove unwanted data before counting. Finally, we use GROUP BY to organize the remaining data into summary rows.

• JOINs happen before filtering.
• WHERE filters rows before they are grouped.
• GROUP BY happens after the data is narrowed down.

Avoiding Common Pitfalls

Beginners often stumble on the Non-Aggregated Rule. If a column isn't in a function, it must be in the GROUP BY clause.

Look at this common mistake. Here, we selected the city and name, but only grouped by city. This will cause an error! To fix it, both columns must appear in the GROUP BY clause.

• Every non-summarized column needs to be in GROUP BY.
• WHERE filters rows; HAVING (advanced) filters groups.

Practice: Chicago Sales

Write a query to find the total quantity of all products sold in the city of 'Chicago'.

Let's try a warm-up. Write a query to find the total quantity of products sold in Chicago. You'll need to join the three tables and filter by city.

• Use SUM(quantity).
• Filter by city in the WHERE clause.

The Final Capstone Challenge

Generate a report showing each customer_name and their total lifetime spend (quantity * price), but only for customers in 'New York'.

This is it! Use everything you've learned. Join the tables, filter for 'New York', and group by name to find the lifetime spend for each customer.

• Combine SELECT, JOIN, WHERE, and GROUP BY.
• Calculate revenue as quantity multiplied by price.