check if record exists in previous date and not in current date in the same table and return count of records matched and unmatched

I am trying to get the count of records that were there in one date and also in another date. What would be the most efficient way?

id	date
AB	6/11/2021
AB	6/11/2021
BC	6/04/2021
BC	6/04/2021
AB	6/04/2021
AB	6/04/2021

This should return True =2 (Ab is present in 04/21) and False=2

Answer

Per ID if it’s in more than one other date is a COUNT DISTINCT

SELECT id, count(distinct date) > 1
FROM VALUES
    ('AB', '6/11/2021'),
    ('AB', '6/11/2021'),
    ('BC', '6/04/2021'),
    ('BC', '6/04/2021'),
    ('AB', '6/04/2021'),
    ('AB', '6/04/2021')
    t(id,date)
group by 1

gives:

ID	COUNT(DISTINCT DATE) > 1
AB	TRUE
BC	FALSE

thus the number of things in this state is:

select count_if(c)
from (
    SELECT id, count(distinct date) > 1 as c
    FROM VALUES
        ('AB', '6/11/2021'),
        ('AB', '6/11/2021'),
        ('BC', '6/04/2021'),
        ('BC', '6/04/2021'),
        ('AB', '6/04/2021'),
        ('AB', '6/04/2021')
        t(id,date)
    group by 1
)

giving:

COUNT_IF(C)
1

and if you like to see that in a form where there is a table:

create table data as 
select * FROM VALUES
        ('AB', '6/11/2021'),
        ('AB', '6/11/2021'),
        ('BC', '6/04/2021'),
        ('BC', '6/04/2021'),
        ('AB', '6/04/2021'),
        ('AB', '6/04/2021')
        t(id,date);

select count_if(c)
from (
    SELECT id, count(distinct date) > 1 as c
    FROM data
    group by 1
)

the selecting ID can be dropped on that:

select count_if(c)
from (
    SELECT count(distinct date) > 1 as c 
    FROM data 
    group by id
);

Ah, I think your comment you are asking for:

select c, count(*)
from (
    SELECT count(distinct date) > 1 as c 
    FROM data 
    group by id
)
group by 1;

C	COUNT(*)
TRUE	1
FALSE	1

Another way to think about it:

select id, 
    date,
    c as count_same,
    count(date) over (partition by id) > 1 as in_many
from (
SELECT id, date, count(*) as c
FROM data 
group by 1,2
)

gives:

ID	DATE	COUNT_SAME	IN_MANY
AB	6/11/2021	2	TRUE
BC	6/04/2021	2	FALSE
AB	6/04/2021	2	TRUE

but if you are want just one row per ID, and to know if they are in many dates that is simple, but what to make of the fact you want the id/date count across those many rows, when folded up.

The description of desired output is rather vague..