Changes in book and DVD checkouts over a decade
MAT 259, 2019
Sandy Schoettler
Jupyter Notebook
Please see the Jupyter Notebook
Query
USE spl_2016;
SELECT
tslice,
SUM(CASE WHEN day=0 THEN qty ELSE 0 end) Monday,
SUM(CASE WHEN day=1 THEN qty ELSE 0 end) Tuesday,
SUM(CASE WHEN day=2 THEN qty ELSE 0 end) Wednesday,
SUM(CASE WHEN day=3 THEN qty ELSE 0 end) Thursday,
SUM(CASE WHEN day=4 THEN qty ELSE 0 end) Friday,
SUM(CASE WHEN day=5 THEN qty ELSE 0 end) Saturday,
SUM(CASE WHEN day=6 THEN qty ELSE 0 end) Sunday
FROM (SELECT Count(*) AS qty,
day,
tslice
FROM (SELECT checkout,
itemtype,
Weekday(checkout) AS day,
floor(Hour(checkout) * 4 + (Minute(checkout)/15)) AS tslice
FROM transactions,
itemType
WHERE checkout BETWEEN '2016-01-01' AND '2017-01-01'
AND transactions.itemnumber = itemType.itemnumber) sample
-- "sample" table
-- filter input data by checkout time
--
-- [cout, type, day, tslice]
-- [tx, it, tx, tx]
WHERE tslice != 0
AND ( itemtype = 'acdvd'
OR itemtype = 'ardvd'
OR itemtype = 'bcbk'
OR itemtype = 'bcdvd'
OR itemtype = 'jcdvd'
OR itemtype = 'jrdvd'
OR itemtype = 'scmed')
GROUP BY day, tslice) daytime
-- "daytime" table
--
GROUP BY tslice;
SELECT
tslice,
SUM(CASE WHEN day=0 THEN qty ELSE 0 end) Monday,
SUM(CASE WHEN day=1 THEN qty ELSE 0 end) Tuesday,
SUM(CASE WHEN day=2 THEN qty ELSE 0 end) Wednesday,
SUM(CASE WHEN day=3 THEN qty ELSE 0 end) Thursday,
SUM(CASE WHEN day=4 THEN qty ELSE 0 end) Friday,
SUM(CASE WHEN day=5 THEN qty ELSE 0 end) Saturday,
SUM(CASE WHEN day=6 THEN qty ELSE 0 end) Sunday
FROM (SELECT Count(*) AS qty,
day,
tslice
FROM (SELECT checkout,
itemtype,
Weekday(checkout) AS day,
floor(Hour(checkout) * 4 + (Minute(checkout)/15)) AS tslice
FROM transactions,
itemType
WHERE checkout BETWEEN '2016-01-01' AND '2017-01-01'
AND transactions.itemnumber = itemType.itemnumber) sample
-- "sample" table
-- filter input data by checkout time
--
-- [cout, type, day, tslice]
-- [tx, it, tx, tx]
WHERE tslice != 0
AND ( itemtype = 'acdvd'
OR itemtype = 'ardvd'
OR itemtype = 'bcbk'
OR itemtype = 'bcdvd'
OR itemtype = 'jcdvd'
OR itemtype = 'jrdvd'
OR itemtype = 'scmed')
GROUP BY day, tslice) daytime
-- "daytime" table
--
GROUP BY tslice;
Final result
Code