How to get the number of new subscriptions and the number of subscription renewals in SQL?

Having this table representing users subscriptions:

CREATE TABLE `subscriptions` (
  `id` int(11) NOT NULL,
  `email` varchar(255) DEFAULT NOT NULL,
  `created_at` datetime NOT NULL
);

There can be multiple records with the same email.

How can I get the number of new subscriptions per month and the number of subscription renewals per month?

Let’s say a subscription was made with email “[e-mail]” in July 2018. If another subscription is made in August 2018 with “[e-mail]”, it’s a renewal. All subscriptions that was made in August 2018 where the email is not registered in July 2018 are new subscriptions.

Answer

You can do this by determining the first time a subscriber is seen:

select year(created_at), month(created_at),
       count(*) as num_subscribers,
       sum( min_ca = created_at ) as num_new_subscribers,
       sum( min_ca > created_at ) as num_renewals
from subscriptions s join
     (select email, min(created_at) as min_ca
      from subscriptions s
      group by email
     ) ss
     on s.email = ss.email
group by  year(created_at), month(created_at);