SQL select the entity with most instances of another entity

Question

So I have these two tables 'Subscription' and 'Show' which are linked via many-to-many link to the ER model

How would I go about writing a SQL script that would tell me which Subscription has the most Shows included. Obviously subscriptions have different ammounts of shows included depending on the price.

Answer 1

I didn't try this but I think it might work

SELECT * from Subscription
WHERE id=(SELECT subscription_id
FROM Show_has_Subscription 
GROUP BY subscription_id
ORDER BY COUNT(subscription_id) DESC
LIMIT 1);