-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path6.5.sql
204 lines (199 loc) · 7.42 KB
/
6.5.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
SELECT EXTRACT('quarter' FROM CURRENT_TIMESTAMP),
EXTRACT('year' FROM CURRENT_TIMESTAMP);
WITH adoption_quarters
AS
(
SELECT Species,
MAKE_DATE ( CAST (DATE_PART ('year', adoption_date) AS INT),
CASE
WHEN DATE_PART ('month', adoption_date) < 4
THEN 1
WHEN DATE_PART ('month', adoption_date) BETWEEN 4 AND 6
THEN 4
WHEN DATE_PART ('month', adoption_date) BETWEEN 7 AND 9
THEN 7
WHEN DATE_PART ('month', adoption_date) > 9
THEN 10
END,
1
) AS quarter_start
FROM adoptions
)
-- SELECT * FROM adoption_quarters ORDER BY species, quarter_start;
,quarterly_adoptions
AS
(
SELECT COALESCE (species, 'All species') AS species,
quarter_start,
COUNT (*) AS quarterly_adoptions,
COUNT (*) - COALESCE (
-- For quarters with no previous adoptions use 0, not NULL
FIRST_VALUE (COUNT (*))
OVER (PARTITION BY species
ORDER BY quarter_start ASC
RANGE BETWEEN INTERVAL '3 months' PRECEDING
AND
INTERVAL '3 months' PRECEDING
)
, 0)
AS adoption_difference_from_previous_quarter,
-- COUNT (*) OVER (PARTITION BY quarter_start) AS quarter_total_all_species, -- use with GROUP BY quarter_start, species
CASE
WHEN quarter_start = FIRST_VALUE (quarter_start)
OVER (PARTITION BY species
ORDER BY quarter_start ASC
RANGE BETWEEN UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING
)
THEN 0
ELSE NULL
END AS zero_for_first_quarter
FROM adoption_quarters
GROUP BY GROUPING SETS ((quarter_start, species),
(quarter_start)
)
)
-- SELECT * FROM quarterly_adoptions ORDER BY species, quarter_start;
,quarterly_adoptions_with_rank
AS
(
SELECT *,
RANK ()
OVER ( PARTITION BY species
ORDER BY COALESCE (zero_for_first_quarter, adoption_difference_from_previous_quarter) DESC,
-- First quarters are 0, all others NULL
quarter_start DESC)
AS quarter_rank
FROM quarterly_adoptions
)
-- SELECT * FROM quarterly_adoptions_with_rank ORDER BY species, quarter_rank, quarter_start;
SELECT species,
CAST (DATE_PART ('year', quarter_start) AS INT) AS year,
CAST (DATE_PART ('quarter', quarter_start) AS INT) AS quarter,
adoption_difference_from_previous_quarter,
quarterly_adoptions
FROM quarterly_adoptions_with_rank
WHERE quarter_rank <= 5
ORDER BY species ASC,
adoption_difference_from_previous_quarter DESC,
quarter_start ASC;
-----------------
-- Alternative --
-----------------
/*
██╗███╗ ███╗██████╗ ██████╗ ██████╗ ████████╗ █████╗ ███╗ ██╗████████╗██╗
██║████╗ ████║██╔══██╗██╔═══██╗██╔══██╗╚══██╔══╝██╔══██╗████╗ ██║╚══██╔══╝██║
██║██╔████╔██║██████╔╝██║ ██║██████╔╝ ██║ ███████║██╔██╗ ██║ ██║ ██║
██║██║╚██╔╝██║██╔═══╝ ██║ ██║██╔══██╗ ██║ ██╔══██║██║╚██╗██║ ██║ ╚═╝
██║██║ ╚═╝ ██║██║ ╚██████╔╝██║ ██║ ██║ ██║ ██║██║ ╚████║ ██║ ██╗
╚═╝╚═╝ ╚═╝╚═╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝╚═╝ ╚═══╝ ╚═╝ ╚═╝
*/
----------------------------------------------------------------------------------
-- !!! The following solution contains an intentional bug !!! --------------------
-- Check the results carefully and compare with the previous solution. -----------
-- If you find the bug and know how to fix it, -----------------------------------
-- submit your solution on the chapter's GitHub repo at https://bit.ly/2wavOMH ---
----------------------------------------------------------------------------------
-- DO NOT post it on the Q&A section so you don't spoil the fun for others. ------
-- If you can't find it and want a hint, check this URL: https://bit.ly/3dVtFp6 --
----------------------------------------------------------------------------------
WITH adoption_quarters
AS
(
SELECT Species,
MAKE_DATE ( CAST( EXTRACT ('year' FROM adoption_date) AS INT),
CASE
WHEN EXTRACT ('month' FROM adoption_date) < 4
THEN 1
WHEN EXTRACT ('month' FROM adoption_date) BETWEEN 4 AND 6
THEN 4
WHEN EXTRACT ('month' FROM adoption_date) BETWEEN 7 AND 9
THEN 7
WHEN EXTRACT ('month' FROM adoption_date) > 9
THEN 10
END,
1
) AS quarter_start
FROM adoptions
)
-- SELECT * FROM adoption_quarters ORDER BY species, quarter_start;
,quarterly_adoptions
AS
(
SELECT species,
quarter_start,
COUNT (*) AS quarterly_adoptions,
COUNT (*) - COALESCE (
-- NULL could mean no adoptions in previous quarter, or first quarter of shelter
FIRST_VALUE ( COUNT (*))
OVER ( PARTITION BY species
ORDER BY quarter_start ASC
RANGE BETWEEN INTERVAL '3 months' PRECEDING
AND
INTERVAL '3 months' PRECEDING
)
, 0)
AS adoption_difference_from_previous_quarter,
CASE
WHEN LAG (quarter_start)
OVER (ORDER BY quarter_start ASC)
IS NULL
THEN TRUE
ELSE FALSE
END AS is_first_quarter
FROM adoption_quarters
GROUP BY species,
quarter_start
UNION ALL
SELECT 'All species' AS species,
quarter_start,
COUNT (*) AS quarterly_adoptions,
COUNT (*) - COALESCE (
-- NULL could mean no adoptions in previous quarter, or first quarter of shelter
FIRST_VALUE ( COUNT (*))
OVER ( ORDER BY quarter_start ASC
RANGE BETWEEN INTERVAL '3 months' PRECEDING
AND
INTERVAL '3 months' PRECEDING
)
, 0)
AS adoption_difference_from_previous_quarter,
CASE
WHEN LAG (quarter_start)
OVER (ORDER BY quarter_start ASC)
IS NULL
THEN TRUE
ELSE FALSE
END AS is_first_quarter
FROM adoption_quarters
GROUP BY quarter_start
)
-- SELECT * FROM quarterly_adoptions ORDER BY species, quarter_start;
,quarterly_adoptions_with_row_number
AS
(
SELECT *,
ROW_NUMBER ()
-- ROW_NUMBER and RANK will return the same result since quarter_start per species is unique
OVER ( PARTITION BY species
ORDER BY CASE
WHEN is_first_quarter THEN 0
-- First quarters should be considered as a 0
ELSE adoption_difference_from_previous_quarter
END DESC,
quarter_start DESC)
AS quarter_row_number
FROM quarterly_adoptions
)
-- SELECT * FROM quarterly_adoptions_with_row_number ORDER BY species, quarter_rank, quarter_start;
SELECT species,
CAST (DATE_PART ('year', quarter_start) AS INT) AS year,
CAST (DATE_PART ('quarter', quarter_start) AS INT) AS quarter,
adoption_difference_from_previous_quarter,
quarterly_adoptions
FROM quarterly_adoptions_with_row_number
WHERE quarter_row_number <= 5
ORDER BY species ASC,
adoption_difference_from_previous_quarter DESC,
quarter_start ASC;