Get rows that exist exactly once per day for a given period I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to get that data individually per day (where date = X), but I need a query with where date between X and Y.

Example data:

DID date     
A   2019-01-01
A   2019-01-01
A   2019-01-02
A   2019-01-03
B   2019-01-01
B   2019-01-02
B   2019-01-03
C   2019-01-01
C   2019-01-02
C   2019-01-02
C   2019-01-03
D   2019-01-01
D   2019-01-02
D   2019-01-03


The query should return only B &amp; D because those exists once for each day from 01 to 03.
I also wish to get the count, which would be 2 in this case
select did from foobar2 where 
    date between '2019-01-01' and '2019-01-03' 
    group by did 
    having count(distinct date)=3 and count(date)=3;


If it has at least one of each date, but more than one of some, then count(date) will be > 3.  If it has less than one for some date, then count(distinct date) will be &lt; 3.


  I also wish to get the count, which would be 2 in this case


Figuring out that B and D have a cardinality of 2 seems pretty easy.  Does that really need to be done inside the query?  If so, use a window function

select did, count(*) over () from foobar2 where 
    date between '2019-01-01' and '2019-01-03' 
    group by did 
    having count(distinct date)=3 and count(date)=3;