Watch the video solution for The Winner University, a easy level problem involving Database. This walkthrough by Everyday Data Science has 2,280 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: NewYork
+-------------+------+ | Column Name | Type | +-------------+------+ | student_id | int | | score | int | +-------------+------+ In SQL, 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 | +-------------+------+ In SQL, 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:
The 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.
Problem Overview: Two university tables store student scores. You need to count how many students scored at least 90 in each university and return which university has the higher count. If both counts are equal, return No Winner.
Approach 1: SQL Aggregation + CASE Comparison (O(n) time, O(1) space)
The solution scans both tables and counts qualifying students using COUNT with a filtering condition (score >= 90). Each count represents the number of high-performing students in that university. After computing the two counts, a CASE expression compares them and returns the winner: New York University, California University, or No Winner if the counts match.
This works because relational databases handle aggregation efficiently. Each table is scanned once to compute the filtered count. The comparison step is constant time and does not require joins or additional structures.
In MySQL, this is typically implemented using two subqueries that compute counts and a CASE statement to determine the result. Since the dataset is small and operations are straightforward aggregations, the query remains efficient even with large tables.
Conceptually, this problem tests understanding of database queries, conditional logic in SQL, and aggregation patterns like COUNT-based summaries. These patterns appear frequently in analytics-style interview questions where multiple datasets must be compared.
Recommended for interviews: The aggregation + CASE approach is the expected solution. It demonstrates that you know how to filter rows, compute counts, and derive conditional results directly in SQL. There is no meaningful brute-force alternative because SQL engines already optimize table scans and aggregations.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation with COUNT and CASE | O(n + m) | O(1) | Standard SQL approach when comparing filtered counts from two tables |
| Subqueries with Derived Counts | O(n + m) | O(1) | Useful when computing counts separately and comparing them in a final SELECT |
