How should I write a SQL queries to find all unique value of a column that are present in all date range.
+-------------+--------+------------+ | primary_key | column | date | +-------------+--------+------------+ | 1 | a | 2020-03-01 | | 2 | a | 2020-03-02 | | 3 | a | 2020-03-03 | | 4 | a | 2020-03-04 | | 5 | b | 2020-03-01 | | 6 | b | 2020-03-02 | | 7 | b | 2020-03-03 | | 8 | b | 2020-03-04 | | 9 | c | 2020-03-01 | | 10 | c | 2020-03-02 | | 11 | c | 2020-03-03 | | 12 | d | 2020-03-04 | +-------------+--------+------------+
In the above example if query date range is 2020-03-01 to 2020-03-04 output should be
a b
since only a and b are present for that range similarly if query date range is 2020-03-01 to 2020-03-03 output should be
a b c
I could do this in an python script by fetching all rows and using a set. Is is possible to write a SQL query yo achieve same result?
Advertisement
Answer
You may aggregate by column value and then assert the distinct date count:
SELECT col FROM yourTable WHERE date BETWEEN '2020-03-01' AND '2020-03-04' GROUP BY col HAVING COUNT(DISTINCT date) = 4;