Skip to content
Tutorials Center
My learning

Topics

Lesson 107 of 125 ยท Projects

Project: Sales Analysis

Analyze a year of online-store sales with SQL. Define revenue, chart monthly trends, rank products and customers, catch data problems and build a cohort table.

Sooner or later someone will hand you a database and ask a very human question: "How did we do this year?" There is no single query for that. You have to agree on what the words mean, check that the data can be trusted, and then answer a series of smaller questions, each one building on the last.

In this project you'll play the analyst for the online store in the shop sample database. You'll explore the tables, define revenue precisely, follow the monthly trend, find the best products and customers, uncover a data-quality problem, and finish with a cohort table that shows whether customers come back. Every stage is a runnable query you can change and rerun.

You'll use GROUP BY, joins, CTEs, window functions, LAG, running totals, ranking, CASE and pivoting. If one of them feels shaky, the link takes you back to its lesson.

Stage 1: get to know the data

Before you answer anything, look at what you have. A quick row count of every table tells you the size of the problem and catches surprises early, such as an empty table or a million rows where you expected a hundred:

SQL How big is each table? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Six categories, 26 products, 40 customers, 120 orders and 292 order lines. UNION ALL stacks the five one-row results into a single table.

The shape of the data matters as much as its size. An order has a status, and an order line (order_items) stores its own unit_price, the price paid at the time of the sale. That's a deliberate design: product prices change, but an old order must keep the price the customer actually paid.

Next, look at the statuses and the date range, because both will shape every later query:

SQL Orders by status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every order falls in 2025, so "this year" means 2025. 57 orders were delivered, 24 are pending, 22 are shipped and 17 (14.2%) were cancelled. The SUM(COUNT(*)) OVER () trick adds up the group counts across the whole result, which gives the denominator for the percentage without a second query. It's explained in Grouping in Practice.

Stage 2: define revenue

"Revenue" sounds obvious until you write the query. Do cancelled orders count? Does shipping? Is a pending order revenue yet? Different companies answer differently, so the most important step is to pick a definition and write it down. For this project:

  • Revenue is the sum of quantity * unit_price over order lines.
  • Cancelled orders are excluded. Pending, shipped and delivered orders all count, because the customer committed to buy.
  • Shipping is excluded. It pays for delivery and is reported separately.

The definition lives in one place: a CTE called order_totals with one row per counted order. Every later stage starts from it, so if the definition changes, you change it once.

SQL Headline numbers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

So 103 orders from 33 customers brought in 26,515.90 in revenue, an average order value of 257.44 and about 4.7 units per order. Shipping added another 317.80, kept separate as agreed.

Notice the two-step shape: first one row per order, then the averages over those rows. If you computed AVG(oi.quantity * oi.unit_price) straight over the joined lines, you'd get the average line value, not the average order value. The aggregate-per-order-first rule is one of the most common sources of wrong numbers in reports.

Check the number

Never trust a total you can't reconcile. A good check is to compute the same figure a different way and see that the pieces add up. Here, revenue by status should sum to the grand total over all non-cancelled statuses:

SQL Reconcile revenue by status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Delivered (13,534.45) + pending (7,668.95) + shipped (5,312.50) = 26,515.90, matching the headline. The cancelled orders were worth 4,728.95, which is a useful number on its own: that's sales the store lost.

Wrap money in ROUND(..., 2) only at the end. Prices are stored as REAL (floating-point), so a raw sum can print as something like 26515.899999999998. Rounding inside the calculation, before summing, can make totals drift by a cent or two. Production systems usually store money as integer cents or a DECIMAL type, as explained in Data Types.

Stage 3: the monthly trend

A yearly total hides the story. Group the orders by month with strftime('%Y-%m', ...), then let window functions compare each month to the one before it and keep a running total:

SQL Revenue by month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read the result like a story. March was the best month (15 orders, 4,514.00), and April collapsed to just 2 orders and 213.00. With so few orders per month, single months swing wildly: May's change column shows +3,088.90 right after April's -4,301.00. The three-month moving average (from Running Totals and Moving Averages) smooths that noise, and the running total ends at exactly 26,515.90, the headline revenue. That's another free consistency check.

LAG(revenue) returns the previous row's value in the window order, and it's NULL for January because there is no December 2024 to compare with. That NULL is honest: don't replace it with 0, which would claim January grew from nothing.

A month with zero orders would simply be missing from this result, and LAG would then compare across the gap. Every month of 2025 has orders here, but in real reports you generate the full list of months with a recursive CTE and LEFT JOIN the sales to it.

Try changing the grouping to quarters. One way is (CAST(strftime('%m', ordered_at) AS INTEGER) + 2) / 3, which turns months 1โ€“3 into 1, 4โ€“6 into 2, and so on, using integer division.

Stage 4: products and categories

The next question is what sells. Restrict the order lines to counted orders, join them to products and categories, and rank by revenue. A percentage of the total shows how concentrated sales are:

SQL Top products by revenue ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The Ultralight Tent leads with 3,780.00 (14.3% of revenue) from only 20 units, while the Chef Knife needed 34 units to reach 2,006.00. Units and revenue tell different stories, so a good report shows both. Note that the window function runs before LIMIT, so each percentage is a share of all products, not of the top five.

Now zoom out to categories, and find the best product inside each one with ROW_NUMBER():

SQL Categories and their best sellers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Audio is the top category (7,132.00, 26.9%), closely followed by Outdoor (6,557.20). Stationery sells plenty of items but cheap ones, so it brings in only 6.5%. The MAX(CASE ...) expression picks the one product per category whose rank is 1; all the other rows give NULL, and MAX ignores NULLs.

ROW_NUMBER() picks exactly one winner even when two products tie. If ties should share first place, use RANK() and expect more than one best seller per category. The ranking lesson compares the three functions.

The other end of the list matters too. Products that barely sell tie up money in stock. A LEFT JOIN keeps products with no sales at all, and COALESCE turns their missing sum into 0:

SQL Slowest sellers and their stock ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every product sold at least once, but the Ergonomic Mouse moved only 7 units while 40 sit in the warehouse. If you used an inner join here, a product that never sold would vanish from the report, which is exactly the product you most want to see. That's the LEFT JOIN habit for "show me everything, even with no matches".

Stage 5: customers

Who are the best customers, and how much does the store depend on them? Start with a top-five list, reusing order_totals:

SQL Top customers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Rania Andersson spent the most (1,710.30 over 6 orders), but look at Hana Weber and Rosa Santos: only 3 orders each, at more than 520 per order. Two different kinds of valuable customer: frequent buyers and big-basket buyers. Group by c.id, never by name alone, because two customers can share a name (this shop has three customers called Rosa).

Repeat customers

A healthy store doesn't live on one-time buyers. Bucket customers by how many orders they placed, and see how much revenue each bucket brings:

SQL Revenue by order count ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

21 of the 33 buying customers ordered three or more times, and they produced 82.9% of revenue. Only 4 customers bought once. This is the most loyal customer base you'll ever see in a sample database, and it means the store's future depends on keeping those 21 happy.

The labels sort correctly because '1 order', '2 orders' and '3+ orders' happen to be in alphabetical order. For labels that don't, sort by a CASE expression or by MIN(order_count).

Finally, which customers never bought anything? This is an anti-join, and NOT EXISTS states it directly:

SQL Customers who never bought ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Seven customers. Remove the AND o.status <> 'cancelled' line and you get six: Lena Andersson did place an order, but cancelled it. A marketing team would treat those two groups differently, so the precise question matters.

Stage 6: a data-quality check

Analysts spend a surprising amount of time doubting the data, and with good reason. Here's a rule that should always hold: a customer can't order before they signed up. Test it:

SQL Orders placed before sign-up ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The rule fails for 8 customers and 22 orders. Maya Adeyemi supposedly signed up on 2025-12-06 but ordered on 2025-01-24. In a real company you'd now ask why. Maybe signed_up is actually "created a password" and guests could check out earlier, or maybe an import overwrote the dates. Either way, report it rather than silently fix it, and don't use signed_up for anything important until it's explained.

The comparison works because both columns hold ISO 8601 text ('2025-01-24 18:48' and '2025-12-06'), which sorts in date order. A date-only value compares as smaller than any time on the same day, which is fine for this check.

That finding changes the next stage. The obvious way to group customers into cohorts would be by sign-up date, but you just learned not to trust it. Use something you can trust: the date of each customer's first order.

Stage 7: a cohort table

A cohort is a group of customers who started at the same time. A cohort table answers "of the customers who first bought in Q1, how many were still buying in Q2, Q3 and Q4?" It's the classic way to measure retention.

Build it in three steps: list which quarters each customer was active in, find each customer's first quarter (their cohort), then count active customers per cohort and quarter with conditional aggregation, the pivoting technique:

SQL Quarterly retention cohorts ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read each row from left to right. 22 customers first bought in Q1; 13 of them bought again in Q2, 9 in Q3 and 11 in Q4. The Q2 cohort has 7 customers, and 3 of them were still active in Q4. Nobody had a first order in Q4, so there's no Q4 row. The empty cells (zeros before a cohort starts) are expected: a customer can't be active before their first order.

COUNT(DISTINCT CASE WHEN ... THEN customer_id END) counts each customer once per quarter, because the CASE returns NULL for the other quarters and COUNT skips NULLs. The DISTINCT in active already removes duplicates, so plain COUNT would also work here, but keeping DISTINCT makes the query safe if someone later changes active.

To turn counts into retention rates, divide by the cohort size, for example ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.quarter = 2 THEN a.customer_id END) / COUNT(DISTINCT c.customer_id), 1) AS q2_pct. Try adding those columns.

Putting it together

Look at what you can now tell the store's owner, with a query behind every sentence:

  • 2025 revenue was 26,515.90 from 103 orders and 33 customers; 14% of orders were cancelled, worth 4,728.95.
  • Sales peaked in March and were weak in April; monthly numbers are noisy, so judge trends on the moving average.
  • Audio and Outdoor bring in more than half of revenue; the tent and the dumbbells are the stars.
  • 21 loyal customers produce 83% of revenue, and the Q1 cohort is still half active at year end.
  • signed_up is unreliable for 8 customers and needs investigating before anyone uses it.

The process matters more than the numbers: define terms first, put the definition in one CTE, check totals two ways, and question the data before you trust it.

Ideas to extend it

  • Quarterly report: rewrite Stage 3 by quarter and add each quarter's share of the year.
  • Retention rates: add percentage columns to the cohort table, then compute the average retention one quarter after the first purchase.
  • Days to second order: use LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) and julianday() to measure how long customers wait before buying again.
  • Cancellation analysis: which products and countries have the highest cancellation rate? Use AVG(status = 'cancelled') per group.
  • Restock alert: list products with stock = 0 and how many units they sold this year, so the buyer knows what to reorder first.
  • Product pairs: self-join order_items on order_id to find the products most often bought together.
  • Save your work: turn order_totals into a view so every report uses the same revenue definition.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the project compute one row per order (order_totals) before averaging?

    Each order has several lines. Averaging the lines answers a different question, so total each order first and average the totals.
  2. 2. In the monthly report, why is LAG(revenue) OVER (ORDER BY month) NULL for January?

    LAG reads the previous row in the window order. The first row has no previous row, so it returns NULL (or the default you pass as the third argument).
  3. 3. The "never bought" query returns 7 customers with the cancelled filter and 6 without it. What explains the difference?

    Lena Andersson placed one order and cancelled it. She has an order, but no counted purchase.
  4. 4. What does COUNT(DISTINCT CASE WHEN a.quarter = 2 THEN a.customer_id END) count?

    The CASE yields the customer id for quarter 2 rows and NULL otherwise. COUNT skips NULLs and DISTINCT counts each customer once.
  5. 5. You found orders placed before the customer's sign-up date. What is the best next step?

    Silent fixes hide real problems. Flag the rule violation, work around it (here, cohorts by first order), and get the cause explained.

Summary

  • Start every analysis by exploring row counts, value distributions and date ranges.
  • Define business terms such as revenue explicitly and keep the definition in one CTE (or a view).
  • Aggregate to the right grain first (one row per order), then aggregate again for averages.
  • Window functions give month-over-month change (LAG), moving averages and running totals without extra queries.
  • Reconcile totals two different ways, and test rules the data should obey.
  • Cohort tables combine MIN() per customer with conditional aggregation to show retention.

Next: Project: Design a Library Database, where you'll design a schema from scratch, with constraints, seed data and queries.