Did rain stop the buns?
A bakery has its sales. A weather site has the rain. The owner asks if rain hurts sales.
The answer sits across two tables. One writes dates day first. The other writes year first. Until they agree, nothing can be joined.
Key words
| Word | What it means | Example |
|---|---|---|
| Blend | To join data from different sources into one unified table. | Sales plus weather |
| Join | A query step that matches rows from two tables on a shared field. | Match on the day |
| Join key | The field both tables share and the join uses. | day |
| Standardise | To write values the same way everywhere. | All dates as year-month-day |
| Data type | The kind of value a field holds, such as text or a date. | Text or date |
| Compatible | Able to work together without conflict. | Two date fields |
| Query | A question asked of a database. | Show sales with rain |
| Database | A store of organised data a program can search. | The sales table |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can prepare two sources, standardise them, blend them into one table and test the result.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
Analyse the two sources
| Source | Join field | Its type and format | Fix needed |
|---|---|---|---|
| Sales | day | Text, day first | Parse to a date |
| Weather | day | Date, year first | None |
The cells students fill in are left blank in this preview.
Try it: standardise and blend
select strptime(s.day, '%d/%m/%Y')::date as day, s.total, w.rain_mm
from sales s
join weather w on w.day = strptime(s.day, '%d/%m/%Y')::date
order by day;
-- Try it: change join to left join and see which days have no weather.Try it: test the outcome
-- Three sales days in. How many came out? Which has no weather?
select s.day, w.rain_mm
from sales s
left join weather w on w.day = strptime(s.day, '%d/%m/%Y')::date
where w.rain_mm is null;