AI Hints
Homeโ€บSQL Tutorialโ€บFinal Project: The Year in Review
SQL ยท Lesson 114 of 126

What Is Going Wrong?

Finding the problem.

โฑ 2 min readโ–ถ Video at 2:40:24๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

A good analyst doesn't only share good news. The bank will trust Helen more if she knows her problems, and has a plan. Let's look at returns, by country.

What Is Going Wrong?
From the video: What Is Going Wrong?
  • Not only good news
  • Know your problems
  • Returns by country

Question 4: returns by country

For each country, the percentage of orders that were returned. Run it. Germany stands out. Seven point one percent of German orders were returned. That's almost three times the rate in Australia. Helen should find out why. Maybe the delivery takes too long, or books arrive damaged.

query.sqlsql
SELECT c.country,
       COUNT(*) AS orders,
       ROUND(100.0 * SUM(CASE WHEN o.status = 'returned'
                              THEN 1 ELSE 0 END) / COUNT(*), 1) AS return_pct
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.country
ORDER BY return_pct DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  country  โ”‚ orders โ”‚ return_pct โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Germany   โ”‚ 140    โ”‚ 7.1        โ”‚
โ”‚ Canada    โ”‚ 272    โ”‚ 4.8        โ”‚
โ”‚ UK        โ”‚ 350    โ”‚ 4.6        โ”‚
โ”‚ Ireland   โ”‚ 165    โ”‚ 4.2        โ”‚
โ”‚ USA       โ”‚ 374    โ”‚ 2.7        โ”‚
โ”‚ Australia โ”‚ 297    โ”‚ 2.4        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

One more number

The bank asks one last thing. What was the average order value in twenty twenty five, counting only delivered and shipped orders? Pause and try. Here's a solution. A subquery gives each order's total, and the outer query averages them. Thirty one dollars eighty five.

โœ๏ธ Exercise: One more number

Average order value in 2025 (delivered + shipped)

Show hint

Subquery for order totals, then AVG(total)

Show solution
solution.sqlsql
SELECT ROUND(AVG(total), 2) AS avg_order_2025
FROM (SELECT o.order_id, SUM(oi.quantity * b.price) AS total
      FROM orders AS o
      JOIN order_items AS oi ON oi.order_id = o.order_id
      JOIN books       AS b  ON b.book_id   = oi.book_id
      WHERE o.order_date >= '2025-01-01'
        AND o.status IN ('delivered', 'shipped')
      GROUP BY o.order_id);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ avg_order_2025 โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 31.85          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The story for the bank

Now put it into a story. Orders almost doubled, and revenue grew by eighty two percent. Every genre grew, led by history, business, and fantasy. Dragons of Ember Bay is the star. But cancellations are rising, and German returns are high. Helen has a plan for both. That's a story a bank can say yes to.

Growth+86% orders, +82% revenue
LeadersHistory, Business, Fantasy
StarDragons of Ember Bay
To fixcancellations, German returns

Presenting your numbers

A few tips for presenting numbers to people who don't write SQL. Lead with the answer, not the query. Round your numbers, nobody needs the cents. Compare with something, like last year. And end with what to do next. Your queries are the engine, but the story is what people remember.

Lead with the answernot the query
Roundno cents needed
Comparewith last year
End withwhat to do next

Summary

You just did the job of a data analyst, from raw tables to a story a business can act on. Congratulations. Before the recap, let's make all of it stick, with some quick practice.

Key takeaways
  • Numbers
  • Growth and stars
  • Problems and a plan
Next up: Practice Gym: The Basics โ€” quick warm-up challenges