Verified Creators With Over One Lakh Followers — SQL Problem
The partnerships team wants a list of creators to invite to a brand-collab programme: accounts that are verified, currently active, and have at least…
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #423
Problem statement
The partnerships team wants a list of creators to invite to a brand-collab programme: accounts that are verified, currently active, and have at least 1,00,000 (100000) followers. A NULL follower_count means the count has not been computed yet and never qualifies.
Return the columns user_id, handle and follower_count, ordered by follower_count descending, then by user_id ascending.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| handle | varchar |
| is_verified | bool |
| account_status | enum(active, suspended, deactivated) |
| follower_count | int |
Primary key: user_id.
One row per account. is_verified is 1 for a blue-tick account, 0 otherwise.
Examples
Example 1
users
| user_id | handle | is_verified | account_status | follower_count |
|---|---|---|---|---|
| 1 | aarav.eats | 1 | active | 245000 |
| 2 | diya_travels | 1 | active | 100000 |
| 3 | kabir.codes | 0 | active | 880000 |
| 4 | meera_fit | 1 | suspended | 510000 |
| 5 | rohan.memes | 1 | active | 99999 |
| 6 | zara_ootd | 1 | active | 245000 |
| 7 | ishaan.ipl | 1 | active | NULL |
Output
| user_id | handle | follower_count |
|---|---|---|
| 1 | aarav.eats | 245000 |
| 6 | zara_ootd | 245000 |
| 2 | diya_travels | 100000 |
How to solve Verified Creators With Over One Lakh Followers
This is a plain filter followed by a sort. Each condition the partnerships team named becomes one predicate: is_verified = 1, account_status = 'active' and follower_count >= 100000, combined with AND because an account must satisfy every one of them.
The boundary matters: "at least one lakh" means 100000 is in and 99999 is out, so the comparison is >= (or, equivalently for integers, > 99999). A NULL follower count makes the comparison unknown rather than false, and WHERE keeps only rows where the predicate is true, so uncomputed counts drop out on their own.
Two creators can have the same follower count, so the order needs a tie-breaker: user_id ascending makes it deterministic. The query reads the table once; an index on (is_verified, account_status, follower_count) would let the database skip non-qualifying rows entirely.
Reference solution (MySQL)
SELECT user_id, handle, follower_count
FROM users
WHERE is_verified = 1
AND account_status = 'active'
AND follower_count >= 100000
ORDER BY follower_count DESC, user_idAnother way
SELECT user_id, handle, follower_count FROM users WHERE follower_count > 99999 AND is_verified <> 0 AND account_status IN ('active') ORDER BY follower_count DESC, user_id ASCAnother way
SELECT user_id, handle, follower_count FROM users WHERE IF(is_verified = 1 AND account_status = 'active', follower_count, 0) >= 100000 ORDER BY 3 DESC, 1← MRR Movements: New, Expansion, Contraction and Churn · Posts With No Likes From Anyone but the Author →