Блог

Наличие этой таблицы, представляющей подписки пользователей:

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

Может быть несколько записей с одним и тем же адресом электронной почты.

Как я могу получить количество новых подписок в месяц и количество продлений подписки в месяц?

Допустим, подписка была оформлена с помощью электронной почты «aaa@a.com » в июле 2018 года. Если в августе 2018 года будет выполнена другая подписка с «aaa@a.com «, это обновление. Все подписки, которые были сделаны в августе 2018 года, если электронное письмо не зарегистрировано в июле 2018 года, являются новыми подписками.

Вы можете сделать это, определив первый раз, когда подписчик просматривается:

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

Подсчитывая только продления и вычитая из общего количества подписок, вы можете получить также новые подписки:

 select t.cyear, t.cmonth, (t.total - t.renewals) newsubs, t.renewals
  from (
  select 
    year(s.created_at) cyear, month(s.created_at) cmonth, 
    count(*) total,
    sum(
      case when exists (
        select 1 from subscriptions
        where 
          email = s.email and 
          last_day(s.created_at - interval 1 month) = last_day(created_at))
        then 1
        else 0
      end
    ) renewals
  from subscriptions s
  group by year(s.created_at), month(s.created_at)
) t