select
    ticket_category
    , ticket_subcategory
    , result_mentioned_by_user
    , 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 retention_users_max_ticket_activity_start_dt
    , count(distinct item_user_id) filter (where max_fact_reaction_dt < max_item_start_time) as retention_users_fact_reaction_dt
from
(
select
    *
from

(
select
    user_id as ticket_user_id
    , ticket_category
    , ticket_subcategory
    , result_mentioned_by_user
    , max(activity_start_dt) as max_ticket_activity_start_dt
    , max(fact_reaction_dt) as max_fact_reaction_dt
from
    support_tickets left join users_evaluation_of_satisfaction on support_tickets.request_id = users_evaluation_of_satisfaction.request_id
group by
    user_id
    , result_mentioned_by_user
    , ticket_category
    , ticket_subcategory
) as tickets_stat

left 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
    , ticket_subcategory
    , result_mentioned_by_user
order by
    distinct_users desc