The Winner University - Practice Coding Problems

The Winner University - Problem

Database Easy

🏆 University Competition: The Winner University

Two prestigious universities, New York University and California University, are engaged in an academic competition to determine which institution has more excellent students.

You are given two tables containing exam scores:

NewYork table: Contains student scores from New York University
California table: Contains student scores from California University

Competition Rules:

An excellent student is defined as someone who scored 90 or higher (≥ 90%) on the exam
The university with more excellent students wins the competition
If both universities have the same number of excellent students, it's a draw

Your Task: Write a SQL query to determine the winner and return:

"New York University" - if New York has more excellent students
"California University" - if California has more excellent students
"No Winner" - if it's a tie

Input & Output

example_1.sql — Basic Competition

$ Input: NewYork table: | student_id | score | |------------|-------| | 1 | 90 | | 2 | 87 | California table: | student_id | score | |------------|-------| | 1 | 89 | | 2 | 96 |

› Output: "New York University"

💡 Note: New York has 1 excellent student (score ≥ 90), California has 1 excellent student. Since they're tied, we need to check again. Actually, New York: student 1 (90), California: student 2 (96). Both have 1 excellent student each, so it should be 'No Winner'. Let me correct: New York has 1 student with score ≥ 90, California has 1 student with score ≥ 90, so the result should be 'No Winner'.

example_2.sql — Clear Winner

$ Input: NewYork table: | student_id | score | |------------|-------| | 1 | 95 | | 2 | 92 | | 3 | 88 | California table: | student_id | score | |------------|-------| | 1 | 89 | | 2 | 87 |

› Output: "New York University"

💡 Note: New York has 2 excellent students (scores 95, 92), while California has 0 excellent students. New York wins with more excellent students.

example_3.sql — Perfect Tie

$ Input: NewYork table: | student_id | score | |------------|-------| | 1 | 91 | | 2 | 85 | California table: | student_id | score | |------------|-------| | 1 | 93 | | 2 | 84 |

› Output: "No Winner"

💡 Note: Both universities have exactly 1 excellent student each (New York: 91, California: 93), resulting in a tie.

Constraints

1 ≤ student_id ≤ 10⁵
0 ≤ score ≤ 100
Both tables contain at least 1 student
Excellent student threshold: score ≥ 90
Tables may have different numbers of students

Visualization

Tap to expand

Understanding the Visualization

Scan University Rosters

Examine each university's student scores to identify excellent students (≥ 90)

Count All-Stars

Tally up the number of excellent students from each university

Declare Champion

Compare counts: higher count wins, equal counts result in 'No Winner'

Key Takeaway

🎯 Key Insight: Use SQL aggregation with conditional counting (WHERE score >= 90) to efficiently identify excellent students, then apply comparison logic to determine the winning university.

Asked in

a Amazon 35 ⊞ Microsoft 28 G Google 22 f Meta 18

The optimal solution uses a Common Table Expression (CTE) to efficiently count excellent students (score ≥ 90) from both universities in a single pass, then applies CASE logic to determine the winner. This approach minimizes table scans and provides better performance than multiple subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery Approach (Basic)	O(n + m)	O(1)	Use separate subqueries to count excellent students from each university
CTE Approach (Optimal)	O(n + m)	O(1)	Use Common Table Expression (CTE) to calculate counts once and compare efficiently

Subquery Approach (Basic) — Algorithm Steps

Create a subquery to count excellent students (score >= 90) from NewYork table
Create another subquery to count excellent students from California table
Use CASE statement to compare the two counts
Return appropriate university name or 'No Winner' based on comparison

Visualization

Tap to expand

Step-by-Step Walkthrough

Count NY Excellent

First subquery counts students with score >= 90 in NewYork table

Count CA Excellent

Second subquery counts students with score >= 90 in California table

Compare Counts

CASE statement compares the two counts to determine winner

Code -

solution.c — C

// SQL query using subquery approach
char query[] = 
    "SELECT "
    "    CASE "
    "        WHEN (SELECT COUNT(*) FROM NewYork WHERE score >= 90) > "
    "             (SELECT COUNT(*) FROM California WHERE score >= 90) "
    "        THEN 'New York University' "
    "        WHEN (SELECT COUNT(*) FROM NewYork WHERE score >= 90) < "
    "             (SELECT COUNT(*) FROM California WHERE score >= 90) "
    "        THEN 'California University' "
    "        ELSE 'No Winner' "
    "    END AS winner";

// Execute using sqlite3 C API
// sqlite3_exec(db, query, callback, NULL, NULL);

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n and m are the number of students in NewYork and California tables respectively. Each table is scanned twice.

✓ Linear Growth

Space Complexity

O(1)

Only stores the count results, no additional space needed

✓ Linear Space

23.4K Views

Medium Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🏆 University Competition: The Winner University

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Subquery Approach (Basic) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler