41. Categorize Channels by Subscriber Size

Solution ↗

Hard

Tables: channels

+-------------------------+-------------+
| Column Name             | Type        |
+-------------------------+-------------+
| channel_id              | varchar     |
| channel_name            | varchar     |
| subscriber_count        | bigint      |
| view_count              | bigint      |
+-------------------------+-------------+
channels contains one row per YouTube channel.
channel_id is the primary key for this table.

Write a SQL query to categorize each channel into one of these groups based on subscriber_count: Mega for 10,000,000 and above, Large for 1,000,000 to 9,999,999, Medium for 100,000 to 999,999, and Small for below 100,000.

Hint

Tip: Use CASE to bucket numeric ranges.

Input:

channels table:
+------------+------------------+------------------+------------+
| channel_id | channel_name     | subscriber_count | view_count |
+------------+------------------+------------------+------------+
| C101       | Alpha Tech       | 2500000          | 92000000   |
| C102       | Data With Mira   | 1800000          | 71000000   |
| C103       | SQL Zone         | 950000           | 40000000   |
+------------+------------------+------------------+------------+

Output:

Output:
+----------------+------------------+--------------+
| channel_name   | subscriber_count | channel_size |
+----------------+------------------+--------------+
| Alpha Tech     | 2500000          | Large        |
| Data With Mira | 1800000          | Large        |
| SQL Zone       | 950000           | Medium       |
+----------------+------------------+--------------+

Explanation:

Each channel is assigned to a labeled bucket based on subscriber_count.