Note:
- Sort the output in the descending order of no_of_subscribers, and then in the ascending order of channel_name.
- If there are no subscribers for a channel is 0, then keep the no_of_subscribers as 0.
Expected Output Format:
channel_id | channel_name | no_of_subscribers |
…. | …. | …. |
Solution
SELECT
channel.channel_id AS channel_id,
channel.name AS channel_name,
COUNT(user_id) AS no_of_subscribers
FROM
channel
LEFT JOIN channel_user ON channel.channel_id = channel_user.channel_id
GROUP BY
channel.channel_id
ORDER BY
no_of_subscribers DESC,
channel_name ASC;