select
   distinct users.user_id, nested_query.nested_count as 'Кол-во услуг', payout_orders.order_sum,
   s.service_id, s.dname, s.servtype, s.subtype,
   s.state, s.expiration_date
from
    users
left join
    payout_orders
on
    users.user_id=payout_orders.user_id
left join
    services s
on
    s.user_id = users.user_id
left join
    user_prefs
on
    user_prefs.user_id = users.user_id
left join
(
    select
        subquery.user_id, count(*) as nested_count
    from
    (
        select
           distinct users.user_id, s.service_id
        from
            users
        left join
            payout_orders
        on
            users.user_id=payout_orders.user_id
        left join
            services s
        on
            s.user_id = users.user_id
        left join
            user_prefs
        on
            user_prefs.user_id = users.user_id
        where
            pref_code='referer_rules_changed_email_sent'
        and
            order_id is not null
        and
            order_type='referral'
        and
            s.state IN ('A', 'S')
        and
            s.expiration_date is not null
        and
            s.expiration_date !='0000-00-00'
        and
            s.subtype !='free'
    ) subquery
    group by
        subquery.user_id
)
nested_query
on
    nested_query.user_id = users.user_id
where
    pref_code='referer_rules_changed_email_sent'
and
    order_id is not null
and
    order_type='referral'
and
    s.state IN ('A', 'S')
and
    s.expiration_date is not null
and
    s.expiration_date !='0000-00-00'
and
    s.subtype !='free'
order by
    nested_query.nested_count desc, payout_orders.order_sum desc, users.user_id, s.expiration_date desc