Turn raw data into reports and dashboards people act on. Twelve lessons, four practice blocks, and the terms your role uses most.
BEFORE YOU START
What This Programme Covers
12lessons
4modules
6practice blocks
Level 2 → 4Working to Fluent
Who this is for
You write queries, build reports and answer questions with data. You may have arrived from finance, operations or marketing rather than from engineering.
About 156 minutes of lessons, plus the practice blocks. Work through it in order, or jump to the module you need.
Half of analysis is a conversation before any query is written.
1. Learn
A request arrives as "can you send me the sales numbers". Your job is to turn it into a decision, a measure, a slice, a period and a comparison, before you open anything. Ask what changes depending on the answer. If nothing does, say so kindly and save everyone a week.
Decision firstMeasure and slicePeriod and comparison
For example: "Send me sales" becomes "revenue by product, by month this year against last, so we can decide what to drop".
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3A stakeholder asks for "the sales data". What is the first thing to establish?
Everything else follows from the decision. Without it, you are guessing at scope.
Question of 3Which part of a request most often gets left out?
People name what they want counted, but rarely what it should be judged against.
Question of 3Nothing changes whatever the answer is. What should you do?
Reports nobody acts on still cost you a week and still need maintaining.
Question of 3"Last month" is ambiguous because:
Agree the period in writing, or two reports will disagree later.
Question of 3Who should agree the measure definition?
A definition agreed in the request never has to be argued about in the meeting.
Practice complete.
3. Apply at work
Take the last request you received and rewrite it in those five parts. Send it back and ask if that is right.
Common mistake
Starting the query while the request is still vague, then rebuilding it three times. Ten minutes of questions saves three rounds.
Remember this: Decision, measure, slice, period, comparison. Then open the database.
Grain, keys and nulls decide whether your answer is right.
1. Learn
Before you trust a table, find out what one row represents, which column identifies it uniquely, and where the blanks are. Grain decides whether a join doubles your totals. Nulls decide whether your average is wrong. Both are invisible until they bite.
One row = what exactlyWhich column is the keyWhere are the blanks
For example: An orders table with one row per line means counting rows counts lines, not orders.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3What does grain mean?
Grain is the level of detail: one order, one order line, one day per store.
Question of 3Your order count looks twice as high as the finance figure. First check:
Grain mismatches are the most common cause of doubled counts.
Question of 3A column has 30% blanks. What is the risk to an average?
Which rows are missing matters as much as how many.
Question of 3What makes a column a good key?
Uniqueness is what lets you join without multiplying rows.
Question of 3Where should you check grain?
A join is only safe when you know the grain on both sides.
Practice complete.
3. Apply at work
Take a table you use weekly and write down its grain, its key, and which columns contain blanks.
Common mistake
Assuming a table is at the grain its name suggests. "Orders" often turns out to be order lines.
Remember this: Ask what one row is, what makes it unique, and what is missing.
Most number disagreements are definition disagreements.
1. Learn
Write down what a measure counts, what it excludes, where it comes from and who owns it. Do it once, in a place people can find, and the same argument stops recurring every month. This is the least glamorous thing an analyst does and the highest leverage.
What it countsWhat it excludesSource and owner
For example: "Active customer" might mean bought in 90 days, or logged in this month, or not cancelled. All three are defensible; only one can be the number.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3Two teams report different customer counts. Most likely cause:
Definitions diverge quietly. Data errors are far rarer than definition drift.
Question of 3A good written definition includes:
Exclusions and ownership are what stop the argument returning.
Question of 3Where should the agreed definition live?
If it is not findable, it will be redefined by the next person.
Question of 3Someone proposes a definition you disagree with. The useful move is:
An agreed imperfect definition beats two competing perfect ones.
Question of 3Why does the owner matter?
Without an owner, nobody can say yes to a change, so it drifts again.
Practice complete.
3. Apply at work
Pick a measure two teams report differently. Write both definitions side by side and take them to whoever can decide.
Common mistake
Settling the definition in the meeting and never writing it down. You will have the same meeting next quarter.
Remember this: One written definition per measure, with its source and its owner.
MODULE 2 · 55 MINUTES
Getting the Data
SQL is the core of the job. Enough to get what you need, check it, and not double your totals.
Start by looking at the table, then narrow it. SELECT names the columns, WHERE filters rows before anything is calculated, ORDER BY sorts, LIMIT stops. Getting comfortable here is most of day-to-day querying.
SELECT the columnsWHERE filters rowsORDER BY and LIMIT
For example: SELECT product, value FROM orders WHERE region = 'North' ORDER BY value DESC;
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3WHERE runs:
That is why it cannot filter on an aggregate: the aggregate does not exist yet.
Question of 3Text values in SQL go in:
Single quotes for text, no quotes for numbers, in standard SQL.
Question of 3What does LIMIT do?
It is how you look at a table without loading all of it.
Question of 3Which comes last in a written query?
Sorting happens after the rows are chosen.
Question of 3You need the highest values first. Add:
DESC reverses the default ascending sort.
Practice complete.
3. Apply at work
Write one query against a table you use, filtered to a single value, sorted by the measure you care about.
Common mistake
Running SELECT * on a huge table and waiting. Filter first, then widen.
GROUP BY collapses rows into one per value, and aggregates summarise each group. COUNT counts rows, SUM adds, AVG averages. HAVING filters the groups afterwards, which is the step most people miss.
GROUP BY collapses rowsAggregates summarise themHAVING filters the groups
For example: SELECT region, SUM(value) AS revenue FROM orders GROUP BY region HAVING SUM(value) > 1000;
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3COUNT(*) counts:
COUNT(column) ignores nulls; COUNT(*) counts every row.
Question of 3Which filters after grouping?
HAVING is the only one that can test an aggregate.
Question of 3You group by region and select product without aggregating it. What happens?
Every selected column must be grouped or aggregated.
Question of 3SUM ignores:
Nulls are skipped, which is why a sum can differ from what you expect.
Question of 3Twelve rows become four after GROUP BY region. That means:
Grouping collapses, it does not remove data.
Practice complete.
3. Apply at work
Write a query that gives one row per category, with a count and a total, sorted by the total.
Common mistake
Using WHERE where you meant HAVING. WHERE cannot see a total that has not been calculated yet.
Remember this: Rows in, groups out. WHERE before, HAVING after.
An inner join keeps rows that match on both sides; a left join keeps everything on the left and fills blanks on the right. Join on a key that is unique on one side, or rows multiply. If your totals jump after adding a join, that is the reason.
Inner keeps matchesLeft keeps everything on the leftUnique on one side
For example: Joining orders to a customer table with duplicate customer rows silently doubles every order.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3Your totals doubled after adding a join. Most likely:
Duplicates on the joined side multiply every matching row.
Question of 3A left join is right when:
For example, every customer, including those with no orders.
Question of 3How do you catch a join that multiplied rows?
It is the fastest check there is, and almost nobody does it.
Question of 3Joining two fact tables directly usually:
Join both to their shared dimensions instead.
Question of 3An inner join drops rows. That means:
Sometimes correct, sometimes data loss. Check which.
Practice complete.
3. Apply at work
Take a query with a join and count the rows before and after. If they differ, find out why.
Common mistake
Checking the query runs rather than checking the row count before and after. Run both.
Remember this: Count rows before and after every join.
Practise this module
SQL Query Workbench
Six exercises against a live database, with real errors and checked answers.
Before anything goes out, check the total against the source system, check a single row end to end, and check the extremes. Most errors announce themselves in the maximum, the minimum or the blank count.
Total against the sourceOne row end to endLook at the extremes
For example: If your revenue is 3% above finance, find the 3% before you send it, not after.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3What should you reconcile against?
Independence is the point. Your last version may already be wrong.
Question of 3Which check catches most errors fastest?
Extremes expose bad joins, bad units and missing filters immediately.
Question of 3You find a 3% gap to finance. You should:
An unexplained gap will be found by someone else, at the worst moment.
Question of 3Tracing one row end to end tells you:
It catches logic errors that totals can hide.
Question of 3Blank counts matter because:
Nulls quietly change results in ways totals do not reveal.
Practice complete.
3. Apply at work
Reconcile one report you own to its source system, and write down the gap and its cause.
Common mistake
Reconciling against your own previous version, which repeats yesterday’s error confidently.
Remember this: Total, one row, extremes. Against an independent source.
A star schema keeps totals right when people slice.
1. Learn
Facts hold the numbers, dimensions hold the words you slice by, and each dimension joins to the fact on a key that is unique on its side. Dimensions do not join to each other, and facts do not join to facts. That shape is what makes a model both readable and correct.
Facts hold numbersDimensions hold wordsFacts meet through dimensions
For example: Sales joins to product, store and date. Returns joins to the same product and store.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3In a star schema, dimensions:
Sideways joins break the shape and the grain.
Question of 3A conformed dimension is:
It is what lets two facts be compared consistently.
Question of 3Why not join two facts directly?
Both should point at the shared dimensions instead.
Question of 3The fact table usually contains:
Words live in dimensions; numbers and keys live in the fact.
Question of 3One wide table with everything in it:
It is fine for one grain and dangerous for two.
Practice complete.
3. Apply at work
Sketch the model behind a report you maintain. Mark each table as fact or dimension, and each join key.
Common mistake
Building one wide table with everything in it. It works until two grains meet, then every total is wrong.
Remember this: One fact in the middle, dimensions around it, nothing joining sideways.
A measure has to be right at every level someone slices it to.
1. Learn
A measure is a calculation that reruns for whatever the report is filtered to. Ratios are where they go wrong: an average of averages is not the average. Calculate the parts, then divide, so the measure holds at every level.
Recalculates per filterDivide totals, not averagesTest at two levels
For example: Average order value should be total value divided by order count, not the average of each day’s average.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3An average of averages is wrong because:
A day with two orders counts as much as a day with two hundred.
Question of 3The safe way to build a ratio measure:
That way it holds at every level someone slices to.
Question of 3A measure differs between total and region level. That means:
A correct measure recalculates consistently at every level.
Question of 3Dividing by a column that can be zero needs:
Otherwise the whole visual errors on a quiet day.
Question of 3Where should a measure be defined?
Defining it once keeps every report consistent.
Practice complete.
3. Apply at work
Take a ratio in your reporting and check it at total level and at one slice. If they disagree, rebuild it.
Common mistake
Checking the measure only at the top level. Test it on one region and one month too.
Remember this: Sum the parts, then divide. Then check it at two levels.
Practise this module
Star Schema Builder
Join facts to dimensions and watch a wrong join break the totals.
Decide who it is for and what they will do with it, then put the figure that answers that question top left. Two or three filters, not every field. A title that says what it covers, and the refresh time on the page.
One audienceHeadline top leftTwo or three filters
For example: A weekly operations dashboard needs today at a glance, not twelve months of history.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3The headline figure belongs:
The eye lands top left, so put the answer there.
Question of 3How many filters should a report offer?
Every extra filter is another way to read the wrong number.
Question of 3A dashboard for everyone usually:
Different audiences need different decisions supported.
Question of 3The refresh time belongs:
It prevents most awkward meeting moments.
Question of 3A good report title includes:
Scope in the title stops half the misreadings.
Practice complete.
3. Apply at work
Take a report you own and write one sentence naming its audience and the decision it supports.
Common mistake
Building one dashboard for everyone. It ends up serving nobody and nobody can delete it.
When a number moves, break it down by one dimension at a time and look for the value that fell or rose most as a share of itself, not in absolute terms. Then slice inside it. Stop when you can say it in one sentence, and say what would change your mind.
One dimension at a timeShare, not sizeStop at one sentence
For example: Revenue fell 18%. Partner channel fell 48% while everything else held, and almost all of it was one region.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3Which value most likely explains a fall?
Big categories always fall most in pounds. Shares show where it broke.
Question of 3After finding the dimension, the next step is:
The detail is what makes the explanation actionable.
Question of 3Two dimensions explain the drop equally well. That means:
Overlapping explanations need a test or more evidence.
Question of 3What should you deliver to the person who asked?
The breakdown is your working, not the answer.
Question of 3Saying what would change your mind:
It is the difference between an analyst and a spreadsheet.
Practice complete.
3. Apply at work
Take a change in your own reporting and write the one-sentence explanation, with the number that supports it.
Common mistake
Presenting the breakdown instead of the answer. People asked why, not for a pivot table.
Remember this: Slice, compare shares, slice again, then say it in a sentence.
Before you move on, write down what it counts, where the data comes from, how often it refreshes, who owns it and when it should next be reviewed. Then have someone else open it and try to change one thing.
Definitions written downOwner and review dateSomeone else can change it
For example: Every report should name a person who can answer a question about it in six months.
2. Practise
Three questions, drawn from a larger set. Answer all three to complete the lesson.
Question of 3A report with no named owner:
Ownership is what keeps it alive after you move on.
Question of 3The best test of documentation is:
Anything else is a guess.
Question of 3A review date exists so that:
Most reports outlive their usefulness quietly.
Question of 3What should be written down about the source?
Freshness and origin are the two questions people ask.
Question of 3Handing over well mostly protects:
You are the one who gets the question at 5pm on a Friday.
Practice complete.
3. Apply at work
Pick a report you own and write its five facts: definition, source, refresh, owner, review date.
Common mistake
Leaving the only explanation in your own head, then going on holiday.
