Biggest Single Number | LeetCode 619 | Crack SQL Interviews in 50 Qs

This comprehensive study note covers LeetCode Problem 619 ("Biggest Single Number"), breaking down the problem requirements, constraints, edge cases, a subquery-based MySQL solution, and important SQL execution fundamentals like the distinction between WHERE and HAVING.

1. Problem Overview & Status Quo

• Problem Statement: Given a table named my_numbers containing a single integer column num, find the largest number that appears only once (i.e., a "single number").
• Edge Case / No Match Condition: If there is no single number present in the table, the query must return NULL.
• Duplicate Presence: The table may contain multiple duplicate values (pairs, triplets, or higher frequencies).

2. Core Mental Model & Analogy

Real-Life Analogy: The Roll Call and Frequency Check

Imagine organizing a classroom roster where students sign their names every time they enter a room. Some students sign multiple times because they enter and exit repeatedly, while others sign only once.

1. Frequency Count: First, you go through the sign-in sheet and count how many times each unique name appears.
2. Filtering Singles: You cross out anyone who appears 2 or more times, leaving only the students who signed in exactly once.
3. Finding the Maximum: Among the remaining single students, you look for the one with the highest numerical ID or alphabetical rank. If nobody is left on the list, you report NULL.

3. Walkthrough of Examples

Let's examine how the logic applies to different input datasets.

Example 1: Valid Single Numbers Exist

• Input Table (my_numbers):
  TEXT

  Copy

  num
  ---
  8
  8
  3
  3
  1
  4
  5
  6
  

• Step-by-step frequency breakdown:
  • 8 appears 2 times (Duplicate)
  • 3 appears 2 times (Duplicate)
  • 1 appears 1 time (Single)
  • 4 appears 1 time (Single)
  • 5 appears 1 time (Single)
  • 6 appears 1 time (Single)
• Filtered Singles: {1, 4, 5, 6}
• Max Calculation: The largest value among {1, 4, 5, 6} is 6.
• Output: 6

Example 2: No Single Numbers Exist

• Input Table (my_numbers):
  TEXT

  Copy

  num
  ---
  8
  8
  7
  7
  3
  3
  3
  

• Step-by-step frequency breakdown:
  • 8 appears 2 times
  • 7 appears 2 times
  • 3 appears 3 times
• Filtered Singles: {} (Empty set)
• Max Calculation: Since no single number exists, aggregate functions over empty sets yield NULL.
• Output: NULL

4. Naive vs. Production-Ready SQL Strategy

When approaching this problem, we need to handle two distinct logical constraints simultaneously:

1. Filtering condition: Keep only numbers with a frequency count equal to 1.
2. Aggregation condition: Find the maximum value among those filtered numbers.

Strategy Comparison

5. Step-by-Step Solution Implementation

Here is the clean MySQL query demonstrated in the lecture:

SELECT MAX(num) AS num
FROM (
    SELECT num
    FROM my_numbers
    GROUP BY num
    HAVING COUNT(num) = 1
) AS single_numbers;

6. Detailed Flow Walkthrough

Let's trace how the database engine executes this nested query step-by-step:

7. Crucial SQL Concept: WHERE vs. HAVING

A common pitfall in SQL interviews is confusing when to use WHERE versus HAVING when working with aggregate functions like COUNT().

• WHERE Clause:

  • Executed before grouping (GROUP BY).
  • Used to filter raw individual rows from the table.
  • Cannot contain aggregate functions (e.g., WHERE COUNT(num) = 1 results in a compilation error).

• HAVING Clause:

  • Executed after grouping (GROUP BY).
  • Designed specifically to filter aggregated groups.
  • Can contain aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN().

8. Summary and Key Takeaways

• Problem Core: The challenge requires isolating items that appear exactly once (COUNT = 1) and extracting the maximum value among them.
• Handling NULL Outputs: Standard SQL aggregate functions like MAX() naturally return NULL if evaluated against an empty dataset, satisfying the edge case requirement automatically.
• Subquery Architecture: Breaking the problem into an inner query (for grouping and filtering) and an outer query (for maximum calculation) keeps queries modular and easy to debug.
• Grouping Rules: Always remember that GROUP BY collapses duplicate rows into single unique keys per group.
• Aggregation Filtering: Use HAVING instead of WHERE whenever your filtering condition relies on an aggregate metric (like COUNT(*) or COUNT(num)).
• Interview Readiness: This is a classic easy-level LeetCode problem frequently used to test basic proficiency with grouping, aggregation, and subquery nesting in SQL interviews.