Project: a monthly sales report query for the online shop

The Monday morning report. Picture the first Monday of the month. Your manager asks for last month's sales: how many orders, how much revenue, and the average order size. You export the orders, paste them into Excel, look up every product price, build a pivot table, and fix the dates that came in as text. An hour later you have a report, and next month you'll do it all again. What if that whole routine were one query you could run again whenever you like? That's today's project. We'll use everything from the last three lessons on our online shop's data and build a monthly sales report you can save and reuse.

What you will learn. By the end of this lesson you'll be able to do three things. First, turn a full order date like the fourteenth of February into a month label, so orders can be grouped by month. Second, combine the pieces you already know, JOIN, WHERE, GROUP BY and ORDER BY, into one complete report query. Third, save that query as a view, which is a stored query that behaves like a table, so next month you type one short line instead of rebuilding anything. You've seen every building block before, so this lesson is mostly about putting them together in the right order.

Write the report spec first. Before writing any SQL, write down what the finished report should look like. This is the spec, short for specification, and it's the same thing you'd sketch before building a pivot table. We want one row per month. Each row has four columns: the month, the number of orders, the total revenue, and the average order value. Our orders table stores a quantity and a product id, but not a price. The price lives in the products table, so revenue for an order is its quantity times the product's price. Finally, the report should cover only the months we choose, not the shop's whole history.

Plan the query in steps. Here's the plan, in the order the database actually works through it. Start with the orders table. Join it to products so every order row gets its price, just like a VLOOKUP adds a column. Then keep only the rows inside our date range. Next, group the remaining rows by month, the same way a pivot table puts months in its row area. For each group we calculate the count, the sum and the average. Last, sort the months from oldest to newest. If a query ever confuses you, come back to this picture and check which step is misbehaving.

From a date to a month. SQLite stores our dates as text in the form year, dash, month, dash, day. To group by month, we need just the first part. The function strftime, which stands for string format time, does that. You give it a pattern and a date. The pattern percent capital Y, dash, percent small m means four digit year, dash, two digit month. So the fourteenth of February twenty twenty six becomes two thousand twenty six dash zero two. It's like Excel's TEXT function with a format of year and month. The second query shows the original date next to the new month column, a quick way to check it works.

The report query, clause by clause. Let's run it on a small sample: eight orders and three products. Here's the query. The letters o and p are short nicknames for the two tables, so o dot qty means the qty column of the orders table. Watch it work clause by clause. First, each order is joined to the product with the matching product id, so every row now knows its price. Then the rows collapse into months: count gives the number of orders, and sum adds up quantity times price. January has three orders worth sixty eight dollars, February three orders worth ninety two, and March two orders worth one hundred and eight. Order by puts the oldest month first. And there's the finished report, three rows that used to take a pivot table and a lookup.

Add the average and a date range. Now we finish the spec. Our sample now has one extra order from December twenty twenty five. Two things are new in the query. First, average order value uses AVG on quantity times price, wrapped in ROUND with two decimal places, just like Excel's ROUND. Second, a WHERE clause keeps dates from the first of January up to, but not including, the first of April. Because our dates are written year first, plain text comparison sorts them correctly. So the December order simply drops out, and the result shows our three months with their averages: twenty two sixty seven for January, thirty sixty seven for February, and fifty four for March.

Save it as a view. Here's where SQL beats the monthly copy and paste routine. CREATE VIEW saves the query under a name, here monthly sales. A view stores the recipe, not the results, so every time you select from it, SQLite runs the query again on today's data. New orders added last night show up automatically. Think of it as a pivot table that refreshes itself. Notice the view has no date range: it keeps every month, and you choose the months with WHERE when you select from it. From now on, the whole report is one line: select star from monthly sales. You can filter it too, for example select from monthly sales where month equals two thousand twenty six dash zero three, to get just March.

Common mistake: grouping by the full date. The most common slip in this project is grouping by order date instead of by the month. The query runs without any error, but you get one row for every distinct day. On our sample that's eight rows instead of three, and a full year could give up to three hundred sixty five rows instead of twelve. Always group by the month you built with strftime. Then do two quick sanity checks, like you would in Excel. First, count the rows: three months should mean three rows. Second, add up the revenue column and compare it with a plain sum of quantity times price over the same dates. If the two totals disagree, look at your join or your date range.

Recap. Let's wrap up the project. First, strftime with the year and month pattern turns any order date into a month label you can group by. Second, a full report is just the familiar clauses in order: join to get prices, where to pick the date range, group by to make one row per month, and order by to sort. Count, sum and a rounded average fill in the numbers. Third, saving the query as a view turns a monthly chore into one short line that always uses fresh data. You've gone from your first SELECT to a real, reusable business report.

Your exercise. Now it's your turn to extend the report before the next lesson. Add two columns to the monthly query. The first is units sold, the total quantity of items per month, which is a sum of quantity. The second is how many different customers bought something that month. For that, use COUNT with the word DISTINCT in front of the customer id, which counts each customer only once, like removing duplicates in Excel before counting. Save the result as a new view called monthly sales v2 and check the numbers by hand against the sample data. For January, you should see six units and three customers.