-
Notifications
You must be signed in to change notification settings - Fork 7
/
2072. The Winner University.py
155 lines (120 loc) · 3.27 KB
/
2072. The Winner University.py
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
2072. The Winner University
Easy
36
2
Add to List
Share
SQL Schema
Table: NewYork
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id | int |
| score | int |
+-------------+------+
student_id is the primary key for this table.
Each row contains information about the score of one student from New York University in an exam.
Table: California
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id | int |
| score | int |
+-------------+------+
student_id is the primary key for this table.
Each row contains information about the score of one student from California University in an exam.
There is a competition between New York University and California University. The competition is held between the same number of students from both universities. The university that has more excellent students wins the competition. If the two universities have the same number of excellent students, the competition ends in a draw.
An excellent student is a student that scored 90% or more in the exam.
Write an SQL query to report:
"New York University" if New York University wins the competition.
"California University" if California University wins the competition.
"No Winner" if the competition ends in a draw.
The query result format is in the following example.
Example 1:
Input:
NewYork table:
+------------+-------+
| student_id | score |
+------------+-------+
| 1 | 90 |
| 2 | 87 |
+------------+-------+
California table:
+------------+-------+
| student_id | score |
+------------+-------+
| 2 | 89 |
| 3 | 88 |
+------------+-------+
Output:
+---------------------+
| winner |
+---------------------+
| New York University |
+---------------------+
Explanation:
New York University has 1 excellent student, and California University has 0 excellent students.
Example 2:
Input:
NewYork table:
+------------+-------+
| student_id | score |
+------------+-------+
| 1 | 89 |
| 2 | 88 |
+------------+-------+
California table:
+------------+-------+
| student_id | score |
+------------+-------+
| 2 | 90 |
| 3 | 87 |
+------------+-------+
Output:
+-----------------------+
| winner |
+-----------------------+
| California University |
+-----------------------+
Explanation:
New York University has 0 excellent students, and California University has 1 excellent student.
Example 3:
Input:
NewYork table:
+------------+-------+
| student_id | score |
+------------+-------+
| 1 | 89 |
| 2 | 90 |
+------------+-------+
California table:
+------------+-------+
| student_id | score |
+------------+-------+
| 2 | 87 |
| 3 | 99 |
+------------+-------+
Output:
+-----------+
| winner |
+-----------+
| No Winner |
+-----------+
Explanation:
Both New York University and California University have 1 excellent student.
/* Write your T-SQL query statement below */
WITH N AS(
SELECT COUNT(student_id) AS nyc
FROM NewYork
WHERE score >=90),
C AS (
SELECT COUNT(student_id) AS ca
FROM California
WHERE score >= 90)
SELECT
CASE
WHEN nyc > ca THEN 'New York University'
WHEN nyc < ca THEN 'California University'
ELSE 'No Winner'
END AS winner
FROM N,C