Counting entries in SQL which meet a selective criteria

Question

I have a tables which stores the type of account users create for a product. There are 3 possible paths.

1. They can either create a trial account, then create a full account.
2. They can create a full account only.
3. They can create trial account oly.

In the first case, 2 entries would be created, while the other cases would create 1 entry. example source table below:

+------+--------------+
| user | account_type |
+------+--------------+
| 1    | trial        |
+------+--------------+
| 1    | full         |
+------+--------------+
| 2    | full         |
+------+--------------+
| 3    | full         |
+------+--------------+
| 4    | trial        |
+------+--------------+
| 4    | full         |
+------+--------------+
| 5    | trial        |
+------+--------------+
| 5    | full         |
+------+--------------+
| 6    | trial        |
+------+--------------+
| 7    | full         |
+------+--------------+

I would like to calculate the number of users who created a trial account followed by a full account, as well as those who went straight to create a full one. envisioned final table below:

+-------------------+------------------------+
| full_account_only | trial_and_full_account |
+-------------------+------------------------+
| 124               | 256                    |
+-------------------+------------------------+

My query so far is :

select sum(case
             when account_type_cnt = 1 then
              1
           end) as "full_account_only",
       sum(case
             when account_type_cnt = 2 then
              1
           end) as "trial_and_full_account "
  from (select user, count(distinct(account_type)) as account_type_cnt
          from tbl
         group by user)

but I realize it will not fulfill the criteria of counting users who only made a full account. Can somebody kindly help solve this?

Answer 1

Similar approach to Gordon but slightly different implementation, partly because I prefer using count when counting things, rather than sum. You can get counts as flags for which account types exist for each user:

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

and then use that as an inner query with another layer of conditional aggregation:

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

db<>fiddle

Answer 2

You can use two levels of aggregation. I would put the counts in separate rows, like this:

select num_trial, num_full, count(*) as num_users
from (select user,
             sum(case when account_type = 'trial' then 1 else 0 end) as num_trial,
             sum(case when account_type = 'full' then 1 else 0 end) as num_full
      from t
      group by user
     ) u
group by num_trial, num_full;

You can easily modify this to put the values in columns instead:

select sum(case when num_trial > 0 and num_full = 0 then 1 else 0 end) as only_trial,
       sum(case when num_full > 0 and num_trial = 0 then 1 else 0 end) as only_full,
       sum(case when num_full > 0 and num_trial > 0 then 1 else 0 end) as both
from (select user,
             sum(case when account_type = 'trial' then 1 else 0 end) as num_trial,
             sum(case when account_type = 'full' then 1 else 0 end) as num_full
      from t
      group by user
     ) u
group by num_trial, num_full;