Get the top 10 channels for which more number of users are subscribed in the year 2018.

Q. Get the top 10 channels for which more number of users are subscribed in the year 2018.

Note:

In case, if the no_of_subscribers are same, then sort the output in the ascending order of channel_name.

Expected Output Format:

channel_idchannel_nameno_of_subscribers

Answer

SELECT
  channel_user.channel_id,
  channel.name AS channel_name,
  COUNT(channel_user.user_id) AS no_of_subscribers
FROM
  channel_user
  INNER JOIN channel ON channel_user.channel_id = channel.channel_id
WHERE
  CAST(
    strftime('%Y', channel_user.subscribed_datetime) AS INTEGER
  ) = 2018
GROUP BY
  channel_user.channel_id
ORDER BY
  no_of_subscribers DESC,
  channel_name ASC
LIMIT
  10;