Create 4 user profiles based on user actions

UserProfiling.sql

-- quantitative profiling 
with profile_1 as (
    select user_id
    from (
            select p.user_id as user_id, count(distinct p.item_id) as purchased
            from user u 
            join tags t on t.id = u.tag_id 
            join purchase p on p.item_id = u.item_id
            where t.name = 'scarf'
            group by 1)
        where purchased between 1 and 50)

, profile_2 as (
    select user_id
    from (
        select p.user_id as user_id, count(distinct p.item_id) as purchased
        from user u
        join tags t on t.id = u.tag_id 
        join purchase p on p.item_id = u.item_id
        where t.name = 'scarf'
        group by 1)
    where purchased >= 51)

, profile_3 as (
    select user_id
    from (
        select p.user_id as user_id, count(distinct p.item_id) as purchased
        from purchase p 
        where p.item_id IN (7041398, 7042631, 1392499, 3374713, 1393173, 3523869)
        group by 1)
    where purchased between 1 and 50)

, profile_4 as (
    select user_id
    from (
        select p.user_id as user_id, count(distinct p.item_id) as purchased
        from purchase p 
        where p.item_id IN (7041398, 7042631, 1392499, 3374713, 1393173, 3523869)
        group by 1)
    where purchased >= 51)

select count(distinct a.user_id) as profile_1, count(distinct b.user_id) as profile_2, count(distinct c.user_id) as profile_3, count(distinct d.user_id) as profile_4
from users u 
left join profile_1 a on u.id = a.user_id
left join profile_2 b on u.id = b.user_id
left join profile_3 c on u.id = c.user_id
left join profile_4 d on u.id = d.user_id