SQL/challenge-011/6.3.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
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
-- Frame offset functions

SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	LAG (weight)
					OVER (PARTITION BY species, name
						  ORDER BY checkup_time ASC
						 )
		) AS weight_gain
FROM 	routine_checkups
ORDER BY 	species ASC,
			name ASC,
			checkup_time ASC;

/* Show weight gain over the past 3 months. */
SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
					OVER (PARTITION BY species, name
						  ORDER BY checkup_time ASC
						  RANGE BETWEEN 	'3 months' PRECEDING
						  					AND
						  					CURRENT ROW
						 )
		) AS weight_gain_since_up_to_3_months_ago
FROM 	routine_checkups
ORDER BY 	species ASC,
			name ASC,
			checkup_time ASC;/*Incorrect*/

SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
					OVER (PARTITION BY species, name
						  ORDER BY checkup_time ASC
						  RANGE BETWEEN 	'3 months' PRECEDING
						  					AND
						  					'3 months' PRECEDING
						 )
		) AS weight_gain_from_exactly_3_mon ths_ago
FROM 	routine_checkups
ORDER BY 	species ASC,
			name ASC,
			checkup_time ASC;/*Incorrect*/

SELECT (current_timestamp - interval '3 months') AS three_months_ago__before_Covid19__;

SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
					OVER(	PARTITION BY species, name
							ORDER BY CAST (checkup_time AS DATE) ASC
							RANGE BETWEEN 	'3 months' PRECEDING
											AND
											'3 months' PRECEDING
						)
		) AS weight_gain_from_3_months_ago_to_the_day
FROM 	routine_checkups
ORDER BY 	species ASC,
			name ASC,
			checkup_time ASC; /*Incorrect*/

SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
					OVER (PARTITION BY species, name
						  ORDER BY CAST (checkup_time AS DATE) ASC
						  RANGE BETWEEN 	'3 months' PRECEDING
											AND
											'1 day' PRECEDING
						 )
		) AS weight_gain_in_3_months
FROM 	routine_checkups
ORDER BY 	species ASC,
			name ASC,
			checkup_time ASC;

SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
					OVER (PARTITION BY species, name
						  ORDER BY CAST (checkup_time AS DATE) ASC
						  RANGE BETWEEN 	'3 months' PRECEDING
											AND
											'1 day' PRECEDING
						 )
		) AS weight_gain_in_3_months
FROM 	routine_checkups
ORDER BY ABS (weight_gain_in_3_months) DESC;/* Error: cannot use an alias in a function in the same query */

WITH
weight_gains
AS
(
SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
						OVER (PARTITION BY species, name
							  ORDER BY CAST (checkup_time AS DATE) ASC
							  RANGE BETWEEN 	'3 months' PRECEDING
												AND
												'1 day' PRECEDING
							 )
		) AS weight_gain_in_3_months
FROM 	routine_checkups
)
SELECT 	*
FROM 	weight_gains
ORDER BY ABS (weight_gain_in_3_months) DESC NULLS LAST;

WITH weight_gains
AS
(
SELECT	species,
		name,
		checkup_time,
		weight,
		(weight - 	FIRST_VALUE (weight)
						OVER (PARTITION BY species, name
							  ORDER BY CAST (checkup_time AS DATE) ASC
							  RANGE BETWEEN 	'3 months' PRECEDING
												AND
												'1 day' PRECEDING
							 )
		) AS weight_gain_in_3_months
FROM 	routine_checkups
),
include_percentage
AS
(
SELECT 	*,
		CAST (100 * weight_gain_in_3_months / weight
			 AS DECIMAL (5, 2)
			 ) AS percent_change
FROM 	weight_gains
)
SELECT 	*
FROM 	include_percentage
WHERE 	percent_change IS NOT NULL
ORDER BY ABS (percent_change) DESC;