SQL/challenge-011/2.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
-- OVER ()

SELECT 	species,
		name,
		primary_color,
		admission_date
FROM 	animals
ORDER BY admission_date ASC;

/*Total number of animals in our shelter ever. Before window function*/
SELECT 	species,
		name,
		primary_color,
		admission_date,
		(	SELECT COUNT (*)
			FROM animals
		) AS number_of_animals
FROM 	animals
ORDER BY admission_date ASC;
/*With window function*/
SELECT 	species,
		name,
		primary_color,
		admission_date,
		COUNT (*)
		OVER () AS number_of_animals
FROM 	animals
ORDER BY admission_date ASC;

-- FILTER
/* Total number of animals in our shelter since 2017 */
SELECT 	species,
		name,
		primary_color,
		admission_date,
		(	SELECT 	COUNT (*)
			FROM 	animals
			WHERE 	admission_date >= '2017-01-01'
		) AS number_of_animals
FROM 	animals
ORDER BY admission_date ASC; /*Not correct*/

SELECT 	species,
		name,
		primary_color,
		admission_date,
		(	SELECT 	COUNT (*)
			FROM 	animals
			WHERE 	admission_date >= '2017-01-01' -- duplicated predicate
		) AS number_of_animals
FROM 	animals
WHERE 	admission_date >= '2017-01-01' -- We need to duplicate the predicate
ORDER BY admission_date ASC;

/*With window function*/
SELECT 	species,
		name,
		primary_color,
		admission_date,
		COUNT (*)
		FILTER (WHERE admission_date >= '2017-01-01') -- Add FILTER clause
		OVER () AS number_of_animals
FROM 	animals
ORDER BY admission_date ASC;/*Fitlered count is 75, but the returned query is still 100*/

/*With window function, just adding WHERE is enough without duplicating the predicate*/
SELECT 	species,
		name,
		primary_color,
		admission_date,
		COUNT (*)
		OVER () AS number_of_animals
FROM 	animals
WHERE 	admission_date >= '2017-01-01'
ORDER BY admission_date ASC;