Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl (usr, account_type) as 
select 1, 'trial' from dual
union all
select 1, 'full' from dual
union all
select 2, 'full' from dual
union all
select 3, 'full' from dual
union all
select 4, 'trial' from dual
union all
select 4, 'full' from dual
union all
select 5, 'trial' from dual
union all
select 5, 'full' from dual
union all
select 6, 'trial' from dual
union all
select 7, 'full' from dual;
10 rows affected

select usr,
  count(case when account_type = 'trial' then account_type end) as trial_cnt,
  count(case when account_type = 'full' then account_type end) as full_cnt
from tbl
group by usr


USR
TRIAL_CNT
FULL_CNT


6
1
0

2
0
1

4
1
1

3
0
1

5
1
1

7
0
1

1
1
1


select
  count(case when trial_cnt > 0 then usr end) as trial,
  count(case when full_cnt > 0 then usr end) as full,
  count(case when full_cnt > 0 and trial_cnt = 0 then usr end) as full_only,
  count(case when full_cnt > 0 and trial_cnt > 0 then usr end) as trial_and_full
from (
  select usr,
    count(case when account_type = 'trial' then usr end) as trial_cnt,
    count(case when account_type = 'full' then usr end) as full_cnt
  from tbl
  group by usr
)


TRIAL
FULL
FULL_ONLY
TRIAL_AND_FULL


4
6
3
3