Get the number of subscribers for each channel SQL Query

by

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_idchannel_nameno_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;