getting distinct values in groups that excludes results from previous dates


id     email                    created_at
1     [email protected]           2018-01-01
2     [email protected]           2018-01-01
3     [email protected]           2018-01-01
4     [email protected]           2018-01-01
5     [email protected]           2018-01-02
6     [email protected]           2018-01-02
7     [email protected]           2018-01-02
8     [email protected]           2018-01-03
9     [email protected]           2018-01-03

i want to get distinct email values on each day that are not exist in previous dates.

if i group by date i will get results like below.

select count(distinct email) as count from test_table group by created_at

count     created_at
3         2018-01-01
2         2018-01-02
2         2018-01-03


But i need result like below

count     created_at
3         2018-01-01
1         2018-01-02
1         2018-01-03
</code>

please provide solution to above.

bobbybouwmann
8 years ago

Level 88

You almost have to do two queries here to compare this. I think it can be done in one query, but that would be a massive query which would be unreadable and understandable


What you also can do is just one query with the first results you had and then use the collection methods to sort out the correct count based on the previous result and so on.

Please or to participate in this conversation.