Verified Creators With Over One Lakh Followers

Easy
423.

Verified Creators With Over One Lakh Followers

EasyBasics

MySQL

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

ColumnTypeValues
user_idint
handlevarchar
is_verifiedbool
account_statusenumactive, suspended, deactivated
follower_countint

Primary key: user_id.

One row per account. is_verified is 1 for a blue-tick account, 0 otherwise.

Example

The rows must come back in the order shown.

users

user_idhandleis_verifiedaccount_statusfollower_count
1aarav.eats1active245000
2diya_travels1active100000
3kabir.codes0active880000
4meera_fit1suspended510000
5rohan.memes1active99999
6zara_ootd1active245000
7ishaan.ipl1activeNULL

Output

user_idhandlefollower_count
1aarav.eats245000
6zara_ootd245000
2diya_travels100000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
handlevarchar
is_verifiedbool
account_statusenum(active, suspended, deactivated)
follower_countint

Primary key: user_id.

One row per account. is_verified is 1 for a blue-tick account, 0 otherwise.

Examples

Example 1

users

user_idhandleis_verifiedaccount_statusfollower_count
1aarav.eats1active245000
2diya_travels1active100000
3kabir.codes0active880000
4meera_fit1suspended510000
5rohan.memes1active99999
6zara_ootd1active245000
7ishaan.ipl1activeNULL

Output

user_idhandlefollower_count
1aarav.eats245000
6zara_ootd245000
2diya_travels100000

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_id

Another 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 ASC

Another 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 →