I am trying to attain the count of users that ordered at least 1 product on multiple days.
Transactions Table
usr_id|transt_id|product_id|spend| transaction_date
4 8 32 40 2020-05-08 17:54:59
4 7 31 20 2020-05-01 17:54:59
4 7 31 40 2020-05-01 17:54:59
4 6 20 30 2020-05-02 17:54:59
4 6 19 20 2020-05-02 17:54:59
4 6 18 10 2020-05-02 17:54:59
3 5 17 20 2020-05-04 17:54:59
3 5 16 10 2020-05-04 17:54:59
2 3 14 30 2020-05-04 18:54:59
2 3 13 50 2020-05-04 18:54:59
1 2 12 30 2020-05-05 20:54:59
1 2 12 40 2020-05-05 20:54:59
1 2 12 40 2020-05-04 20:54:59
1 1 11 20 2020-05-05 21:54:59
1 1 10 40 2020-05-05 21:54:59
3 4 10 60 2020-05-06 17:54:59
Through my code I have been able to reach to a point where the output is:
select user_id, count(*)
from (
select user_id, date(transaction_date)
from transactions
group by user_id, date(transaction_date)) as abc
group by user_id
having count(user_id)>1;
user_id | count
1 2
3 2
4 3
I want to write a code without writing another subquery to get the count of users having count(*)>1;
The output should be: 3.
In other words, I don't want the following code; I want to write one less subquery or a completely new query
select count(*)
from (
select user_id, count(*)
from (
select user_id, date(transaction_date)
from transactions
group by user_id, date(transaction_date)) as abc
group by user_id
having count(user_id)>1) as bcd;
The query that you already have could be written without a subquery:
select user_id, count(distinct date(transaction_date)) count
from transactions
group by user_id
having count(distinct date(transaction_date))>1;
So what you need now can be written with only 1 subquery:
select count(*) count
from (
select user_id
from transactions
group by user_id
having count(distinct date(transaction_date))>1
) t
You can get the same result with EXISTS:
select count(distinct t.user_id) count
from transactions t
where exists (
select 1
from transactions
where user_id = t.user_id and date(transaction_date) <> date(t.transaction_date)
)
See the demo.