1 Introduction
rows
columns
table
e.g. “retailers”
table
e.g. “retailer_products”
Slide 6
Slide 6 text
1 Introduction
Sequel is your language
to read data from
a relational database.
Slide 7
Slide 7 text
1 Introduction
SELECT * FROM retailers
Slide 8
Slide 8 text
1 Introduction
SELECT * FROM retailers
Slide 9
Slide 9 text
aka fancy graphs
2 Tools
Slide 10
Slide 10 text
aka fast forward 30 years
2 Tools
Slide 11
Slide 11 text
You need some query editor to play around.
2 Tools
Slide 12
Slide 12 text
database connection
2 Tools
Slide 13
Slide 13 text
2 Tools
your laptop database
THE Internet
Slide 14
Slide 14 text
2 Tools
Slide 15
Slide 15 text
You need a visualisation interface.
2 Tools
https://dashboard.hsnb.co
Metabase
Slide 16
Slide 16 text
2 Tools
Slide 17
Slide 17 text
3 Hands-On
SELECT * FROM retailers
Selecting columns from a table
Slide 18
Slide 18 text
3 Hands-On
SELECT id, name, website FROM retailers
Selecting columns from a table
Slide 19
Slide 19 text
3 Hands-On
SELECT * FROM retailers
WHERE created_at >= NOW() - INTERVAL '1 week'
Selecting specific rows
Slide 20
Slide 20 text
3 Hands-On
WHERE condition
AND condition
OR condition
WHERE clause
Slide 21
Slide 21 text
3 Hands-On
name = 'Ferry-Olson'
website IS NULL
created_at >= CAST('2017-05-01' AS date)
column operator column or value
conditions
Slide 22
Slide 22 text
3 Hands-On
WHERE (A OR B) AND (X OR Y)
Parentheses control condition precedence
Slide 23
Slide 23 text
3 Hands-On
SELECT COUNT(*) FROM products
Aggregating data from a table
Slide 24
Slide 24 text
3 Hands-On
SELECT AVG(sales_price) / 100 AS average_price_eur
FROM retailer_products
Aggregating data from a table
Slide 25
Slide 25 text
3 Hands-On
…but CHARTS?
Slide 26
Slide 26 text
3 Hands-On
From table to visualisation
day count
1 345
2 300
3 399
0
100
200
300
400
day 1 day 2 day 3
Slide 27
Slide 27 text
3 Hands-On
SELECT CAST(created_at AS date) AS day, COUNT(*)
FROM products
GROUP BY CAST(created_at AS date)
Slicing data aggregates
Slide 28
Slide 28 text
3 Hands-On
SELECT '2017-05-24 12:00', CAST('2017-05-24 12:00' AS date)
Playing around with date types
Slide 29
Slide 29 text
3 Hands-On
SELECT '2017-05-24 12:00', DATE '2017-05-24 12:00'
Playing around with date types
Slide 30
Slide 30 text
3 Hands-On
SELECT '2017-05-24 12:00', CAST('2017-05-24 12:00' AS timestamptz)
Playing around with date types
Slide 31
Slide 31 text
3 Hands-On
SELECT '2017-05-24 12:00', TIMESTAMPTZ '2017-05-24 12:00'
Playing around with date types
Slide 32
Slide 32 text
3 Hands-On
SELECT date_trunc('month', DATE '2017-05-24')
Truncating date types
Slide 33
Slide 33 text
3 Hands-On
SELECT date_trunc('hour', TIMESTAMP '2017-05-24 13:47')
Truncating date types
Slide 34
Slide 34 text
3 Hands-On
SELECT CAST(created_at AS date) AS day, COUNT(*)
FROM products
GROUP BY date_trunc('hour', created_at)
Slicing aggregates by specific time ranges