SQL/challenge-011/4.2.sql at main · wmdev0808/SQL · GitHub

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
-- Routine Checkups
SELECT 	*
FROM	routine_checkups;


/*
  Return an animal's species, name, checkup time, heart rate, and a Boolean column that is TRUE only for animals which all of their heart rate measurements were either equal to, or larger than the average heart rate for their species.
*/
-- Average species heart rates
SELECT 	species,
		name,
		checkup_time,
		heart_rate,
		CAST (
				AVG (heart_rate)
				OVER (PARTITION BY species)
			 AS DECIMAL (5, 2)
			 ) AS species_average_heart_rate -- CAST to DECIMAL for prettier presentation
FROM	routine_checkups
ORDER BY 	species ASC,
			checkup_time ASC;

-- Nesting attempt
/*Use EVERY Boolean aggregate function
  Boolean aggregate window functions elements are logical predicates
*/
SELECT 	species,
		name,
		checkup_time,
		heart_rate,
		EVERY (Heart_rate >= 	AVG (heart_rate)
								OVER (PARTITION BY species)
				)
		OVER (PARTITION BY species, name) AS consistently_at_or_above_average
FROM	routine_checkups
ORDER BY 	species ASC,
			checkup_time ASC; /*Error: window function calls cannot be nested*/

-- Split with CTE
WITH species_average_heart_rates
AS
(
SELECT 	species,
		name,
		checkup_time,
		heart_rate,
		CAST (
					AVG (heart_rate)
					OVER (PARTITION BY species)
				AS DECIMAL (5, 2)
			 ) AS species_average_heart_rate
FROM	routine_checkups
)
SELECT	species,
		name,
		checkup_time,
		heart_rate,
		EVERY (heart_rate >= species_average_heart_rate)
		OVER (PARTITION BY species, name) AS consistently_at_or_above_average
FROM 	species_average_heart_rates
ORDER BY 	species ASC,
			checkup_time ASC;

-- Use as filter attempt
WITH species_average_heart_rates
AS
(
SELECT 	species,
		name,
		checkup_time,
		heart_rate,
		AVG (heart_rate)
		OVER (PARTITION BY species) AS species_average_heart_rate
FROM	routine_checkups
)
SELECT	species,
		name,
		checkup_time,
		heart_rate
FROM 	species_average_heart_rates
WHERE 	EVERY (heart_rate >= species_average_heart_rate)
		OVER (PARTITION BY species, name)
ORDER BY 	species ASC,
			checkup_time ASC;/* Error: windows functions are not allowed in WHERE */

-- Separate into CTEs
WITH species_average_heart_rates
AS
(
SELECT 	species,
		name,
		checkup_time,
		heart_rate,
		CAST (	AVG (heart_rate)
				OVER (PARTITION BY species)
			 AS DECIMAL (5, 2)
			 ) AS species_average_heart_rate
FROM	routine_checkups
),
with_consistently_at_or_above_average_indicator
AS
(
SELECT	species,
		name,
		checkup_time,
		heart_rate,
		species_average_heart_rate,
		EVERY (heart_rate >= species_average_heart_rate)
		OVER (PARTITION BY species, name) AS consistently_at_or_above_average
FROM 	species_average_heart_rates
)
SELECT 	DISTINCT species,
		name,
		heart_rate,
		species_average_heart_rate
FROM 	with_consistently_at_or_above_average_indicator
WHERE 	consistently_at_or_above_average
ORDER BY 	species ASC,
			heart_rate DESC;