select
    ticket_category
    , count(distinct item_user_id) as distinct_users
    , count(distinct item_user_id) filter (where max_ticket_activity_start_dt > max_item_start_time) as count_churn_users
from
(
select
    *
from

(
select
    user_id as ticket_user_id
    , ticket_category
    , max(activity_start_dt) as max_ticket_activity_start_dt
from
    support_tickets
group by
    user_id
    , ticket_category
) as tickets_stat

join

(
select
    user_id as item_user_id
    , min(user_first_listing_date) as min_user_first_listing_date
    , min(user_registration_time) as min_user_registration_time
    , min(item_starttime) as min_item_start_time
    , max(item_starttime) as max_item_start_time
    , count(distinct item_id) as items_count
    , count(distinct item_category) as item_categories_count
from
    new_items_by_support_users
group by
    user_id
)as items_stat

on
    tickets_stat.ticket_user_id = items_stat.item_user_id

order by
    tickets_stat.ticket_user_id
) as big_query

group by
    ticket_category

order by
    distinct_users desc