First let me say that I love your book! It is a nice overview. It would be nice if you could give some follow up reading for each DB.
Regarding the home work: I started of with this ` SELECT * FROM crosstab(‘ SELECT extract(week from starts) as week, extract( dow from starts) as day, count(*) from events GROUP BY week, day’, ‘SELECT * FROM generate_series(0, 6)’ ) AS ( week int, SUN int, MON int, TUE int, WED int, THU int, FRI int, SAT int) ORDER BY week; `
But somehow one week 7 is giving me trouble:
week | sun | mon | tue | wed | thu | fri | sat
7 | | 1 | | 1 | | |
7 | | | 1 | | | |
13 | 1 | | | | | |
14 | | | 1 | | | |
15 | | 1 | | | | |
18 | | | | | 1 | |
25 | | | | | | | 1 52 | | | 1 | | | |
After reading the assignment again I saw that you want ONE month. Thus I used WHERE and extracted all events in feb.
week | sun | mon | tue | wed | thu | fri | sat ——+—–+—–+—–+—–+—–+—–+—– 7 | | 1 | 1 | 1 | | |
Oddly enough now week 7 shows up perfectly. Why? I haven’t change a thing but the WHERE bit.