Lesson 11 of 13 · 24 min
Sales performance by channel, region and period
By the end of this lesson you can produce a sales performance pack: net sales by channel and region, share of total, year-over-year growth with LAG, average order value, and the top period per channel.
This lesson reuses the revenue definitions from the first lesson of the track, so every number ties back to the same base:
- net sales = Σ (quantity × unit_price − discount_amount) over order lines;
- orders that are
cancelledorpendingare excluded; - the period is the order date (
ordered_at).
Three dimensions describe where sales come from:
| Dimension | Column | Note |
|---|---|---|
| Channel | orders.channel | web, app, marketplace |
| Region | customers.region | the customer's home region; NULL when the city is unknown, so label it |
| Period | ordered_at | year, quarter or month |
orders.delivery_city can differ from the customer's city (gifts, office deliveries). Use the customer's region for who buys, the delivery city for where goods go, and say which one a report uses.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons