select createdLang,
       case when email is not null then 'Есть почта' else 'Нет почты' end hasemail,
       case when domain = 'gmail.com' then 'gmail.com'
            when domain regexp '(^outlook)|(^live)|(^hotmail)' then 'hotmail'
            when domain = 'icloud.com' then 'icloud'
            when domain regexp '(^bk\.ru)|(^inbox\.ru)|(^list\.ru)|(^mail\.(ru|ua))' then 'mail'
            when domain regexp '(^rambler\.ru)|(^ro\.ru)' then 'rambler'
            when domain = 'ukr.net' then 'ukr.net'
            when domain regexp '(^yahoo\.(.*))' or domain = 'yahoomail.com' then 'yahoo'
            when domain regexp '(^ya\.ru)|(^yandex\.(by|com|kz|ru|ua))|(^narod\.ru)' then 'yandex'
            when domain is null then 'no email'
            else 'others'
       end emailservice, -- разбивает по сервисам
       case when isconfirm = 1 then 'Подтверждён' when isconfirm = 0 then 'Не подтверждён' end isconfirm,
       count(distinct userid) regs, -- кол-во регистрации
       count(distinct if(createdsites > 0, userid, null)) completeregs -- кол-во завершённых регистраций,
       sum(paymentcount) paymentcount

from (
select u._id userid,
       u.email,
       u.created ucreated,
       substring_index(u.email,'@',-1) domain,
       count(distinct(if(s._id is not null and s.status > -1, s._id, null))) createdsites,
       count(distinct if(b.status = 'paid' and b.amount >= 100 and b.type in ('site') and b.invoicepaidtype = 1, b._id, null)) paymentcount,
       u.createdLang,
       u.isConfirm,
       u.issocialregister
from users u
left join sites s on (u._id = s.userid)
left join billings b on (u._id = b.userid)
where u.isadmin = 0
  and u.role = 'guest'
group by u._id
) sub
group by createdLang, hasemail, emailservice, isconfirm