Problem

Table: NewYork

1
2
3
4
5
6
7
8

+-------------+------+
| 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

1
2
3
4
5
6
7
8

+-------------+------+
| 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.

Return:

  • “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 result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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.

Solution

Method 1 - Using CTE

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

 WITH NewYorkExcellent AS (
     SELECT COUNT(*) AS count
     FROM NewYork
     WHERE score >= 90
 ),
 CaliforniaExcellent AS (
     SELECT COUNT(*) AS count
     FROM California
     WHERE score >= 90
 ),
 Results AS (
     SELECT 
         CASE 
             WHEN nx.count > cx.count THEN 'New York University'
             WHEN cx.count > nx.count THEN 'California University'
             ELSE 'No Winner'
         END AS Result
     FROM NewYorkExcellent nx, CaliforniaExcellent cx
 )
 SELECT Result FROM Results;