I have a table with 3 columns (order_id, client_id, date_added). The content would look something like this:
order_id | client_id | date_added
-----------------------------------
14152 | NA4156 | 2019-03-01
14153 | EA4656 | 2019-03-02
14154 | EA4656 | 2019-03-02
14155 | CA4456 | 2019-03-03
14156 | DA4556 | 2019-03-03
14157 | EA4656 | 2019-03-03
14158 | FA4756 | 2019-03-06
14159 | GA4856 | 2019-03-06
and so on. As you can see on a certain day there might be no entries.
I am trying to obtain the following result:
date | no_of_rows
-----------------------------
2019-03-01 | 1
2019-03-02 | 2
2019-03-03 | 4
2019-03-04 | 4
2019-03-05 | 4
2019-03-06 | 6
I understood from here how to generate all dates that I am looking for, but I am not sure now how to count the unique clients based on client_id for each date.
At the moment I am doing this step by step and moving the data into an Excel and processing it from there using the query below:
get unique number of clients registered until and including 2019-03-01
SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-02' GROUP BY client_id
get unique number of clients registered until and including 2019-03-02
SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-03' GROUP BY client_id
and so on.
But this method seems to be a little bit exhaustive and I am pretty sure there is a way to do it in a single query, but not sure where to start from.
SELECT COUNT(DISTINCT client_id)
– Akina Mar 20 '19 at 12:16SELECT t1.date, COUNT(DISTINCT t2.client_id) FROM (subquery which generates dates list) t1, clients t2 WHERE t1.date >= t2.date GROUP BY t1.date
– Akina Mar 20 '19 at 12:38