Count Same IDs in two different Date Periods-CodePudding

Let's say I want the ids that are counted as a result of my query, and checked if the same ids appear in a different month.

Here I join 2 tables via distinct id's and count the returning rows to know how many of the matching id's I have. Here that is for the month June. I'd like:

eg. in June 100 distinct ids eg. in July 90 of the same ids left

Please help!

I am stuck as my Sql is not very advanced,...

with total as (
select distinct(transactions.u_id), count(*)
    from transactions 
    join contacts using (u_id) 
    join table using (contact_id)
    where transactions.when_created between '2020-06-01' AND '2020-06-30'
    group by transactions.u_id
    HAVING COUNT(*) > 1 
)
SELECT
   COUNT(*)
FROM
  total

CodePudding user response：

Let's say that you are interested about the query of the like of

select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-06-01' and '2020-06-30'
group by transactions.u_id;

You are also interested in

select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-08-01' and '2020-08-30'
group by transactions.u_id;

And you want to get:

the ids which can be found by both
the minimum total

Then, you can do something of the like of

select t1.u_id, 
       case
           when t1.total > t2.total then t2.total
           else t1.total
       end as total
from (
    select transactions.u_id, count(*) as total
    from transactions
    join contacts using (u_id)
    join table using (contact_ud)
    where transactions.when_created between '2020-06-01' and '2020-06-30'
    group by transactions.u_id) t1
join (
    select transactions.u_id, count(*) as total
    from transactions
    join contacts using (u_id)
    join table using (contact_ud)
    where transactions.when_created between '2020-06-01' and '2020-06-30'
    group by transactions.u_id) t2
on t1.u_id = t2.u_id