Translating Business Questions into SQL

The GPS for Your Data Journey

In the real world, stakeholders don't ask for JOINs or GROUP BYs. They ask questions about growth, loyalty, and performance.

Think of it like finding pizza: your brain automatically translates 'I'm hungry for pizza nearby' into filters like 'Category: Pizza', 'Status: Open', and 'Distance: < 5 miles'.

Welcome to the final module! Before we dive into the capstone project, we need to master the art of translation. Just like you use a map app to filter for the best pizza nearby, a data analyst translates a business goal into a structured SQL plan. When a manager asks, 'Who are our most loyal customers?', they are speaking 'Business'. Your job is to turn that into 'SQL'.

• Business questions are human-centric.
• SQL is the translation of those questions into logic.
• Planning prevents coding errors.

Our Capstone Database Schema

To solve the capstone, we'll use our E-commerce database. Understanding how these four tables connect is the first step in any plan.

Here is a refresher on our schema. We have Customers linked to their Orders. Those orders contain specific items, which finally point to our Product catalog. Notice the underlined fields. These are the keys we use to link the tables together using JOINs.

• Customers connect to Orders via customer_id.
• Orders connect to Order_Items via order_id.
• Order_Items connect to Products via product_id.

The 4-Step Planning Framework

Before writing a single line of code, ask yourself these four questions to build your Logical Plan.

• 1. Output: What columns do I need?
• 2. Sources: Which tables hold them?
• 3. Filters: What should I exclude?
• 4. Logic: Do I need to aggregate or sort?

Use this 4-step framework as your checklist. First, define your Output: what columns go in the SELECT clause? Second, identify your Sources: which tables and JOINs are needed? Third, apply Filters: what goes in the WHERE clause? And finally, determine the Logic: do you need a GROUP BY or ORDER BY?

• SELECT = Output
• FROM/JOIN = Sources
• WHERE = Filters
• GROUP BY/ORDER BY = Logic

Walkthrough: The Capstone Challenge

Question: "Identify our most valuable customer segments (by city) over the last year based on total revenue."

Let's map this request to our SQL Framework.

Let's deconstruct the big capstone question. 'Customer segments by city' means we need to SELECT and GROUP BY city. 'Most valuable' implies we need a SUM of revenue and an ORDER BY to see the top results. 'Over the last year' tells us we need a WHERE clause for the date. Finally, since data is spread across three tables, we'll need JOINs.

• Revenue requires SUM(price * quantity).
• Segmenting by city requires GROUP BY city.
• Time limits require WHERE order_date.

Planning Exercise: Growth Analysis

A stakeholder asks: "Which cities had the highest number of unique orders in 2023?"

Identify the Tables and the Aggregation function needed.

It's your turn. Read the question carefully. Type your plan: which tables will you JOIN, and which aggregate function (SUM, COUNT, or AVG) will you use to find the 'number of unique orders'?

• Identifying correct tables for city and orders.
• Choosing between SUM and COUNT.

Avoiding Common Pitfalls

Two traps often catch beginners during the planning phase:

• Jumping to code too fast: If you don't list your JOINs first, you'll get lost in syntax.
• Vague Metrics: Does 'valuable' mean most orders (COUNT) or most money (SUM)?

Before we start the capstone, remember: don't rush into coding. Map your tables first. Also, always clarify vague terms. If a manager asks for 'top' customers, ask if they mean by frequency or by spend. It makes a huge difference in your SQL logic!

• Always define JOINs before writing SELECT.
• Clarify business definitions (e.g., 'valuable') with stakeholders.