Revenue by channel, without double-counting

You have `orders`, `order_items`, `customers` and `channels`. Produce monthly revenue by acquisition channel for the last 12 months, including channels with no revenue in a given month. The trap is deliberate: orders have many items, and a naive join will triple your revenue. Your submission should make it obvious to a reviewer that this did not happen to you.

Part of the SQL & Data Analysis course~90 minutesEvidence: a write-upWe mark it against a checklist you can read first

01 · What counts as done

What you have to hand in

One row per channel per month for the last 12 months, with no gaps.
Revenue matches the order-level total to the penny.
Channels with zero revenue in a month appear with 0, not absent.
The submission includes at least one check you ran to verify the grain.

02 · How it will be marked

The checklist, published before you start

We score your work against each line below, and every score has to quote the part of your work it is based on. You read all of it first, so you can argue with any score on the specifics.

Result grain is correct30% of the grade
The output has exactly one row per intended entity, and joins have not inflated any aggregate.
1. AbsentTotals are inflated by fan-out, or the grain is not stated anywhere.
2. DevelopingGrain is right for some columns but at least one aggregate double-counts.
3. Competent— this is the pass markGrain is correct throughout and stated in a comment or the write-up.
4. StrongGrain is correct, stated, and explicitly verified in the submission.
Answers the question asked30% of the grade
The numbers returned are the numbers the brief asked for, over the right population and period.
1. AbsentAnswers a different question, or the population is wrong.
2. DevelopingBroadly right but the filter or period is off.
3. Competent— this is the pass markCorrect figures over the correct population and period.
4. StrongCorrect, and edge cases in the brief (refunds, cancellations) are handled deliberately.
Missing data is handled deliberately15% of the grade
NULLs and absent rows are handled on purpose, not by accident.
1. AbsentNULLs silently drop rows or produce NULL totals.
2. DevelopingSome NULL handling, applied inconsistently.
3. Competent— this is the pass markNULLs handled correctly wherever they can occur.
4. StrongNULL handling is correct and the choice is justified in the write-up.
A colleague could maintain it15% of the grade
CTEs or subqueries are named for what they answer; the query can be read top to bottom.
1. AbsentOne long query with no structure, or names like `t1`, `a`, `x`.
2. DevelopingSome structure, but the reader has to hold too much in their head.
3. Competent— this is the pass markClear steps with meaningful names.
4. StrongReads like an explanation of the analysis, not just an implementation of it.
The result was checked10% of the grade
The submission shows evidence that the author verified the number before trusting it.
1. AbsentNo sign the result was checked at all.
2. DevelopingA check is mentioned but not shown.
3. Competent— this is the pass markAt least one concrete check is shown with its output.
4. StrongMultiple independent checks, including one that would have caught a grain error.

03 · What this proves

The skills a pass would prove

Pass, and these skills count as proven, with this piece of work attached as the evidence.

This brief is part of the SQL & Data Analysis course

Starting it starts the course: every skill above, in the order they depend on each other, with this brief at the end as the thing you hand in — marked against the checklist you have just read and nothing else.

About three minutes to set up: what you want to do with SQL & Data Analysis, where you are starting from, and how many hours a week you actually have. The subject we already have from this page.Start the SQL & Data Analysis course

See the whole SQL & Data Analysis course, or every graded brief we run.