I am new topsql
and am interested in counting the number of unique first time users per day.
My table only has two columns- user_id
(there can be multiple uses by one user per day) and start_time
which is a timestamp that indicates the time of use. The count should only include users whose min(start_time) fell on that day.
Why does the following query not work? Shouldn't it be possible to select distinct on two variables at once?
SELECT COUNT (DISTINCT min(start_time::date), user_id),
start_time::date as date
FROM mytable
GROUP BY date
produces
ERROR: function
count(date, integer)
does not exist
The output would look like this
date count
1 2017-11-22 56
2 2017-11-23 73
3 2017-11-24 13
4 2017-11-25 91
5 2017-11-26 107
6 2017-11-27 33...
Any suggestions about how to count distinct min Date and user_id and then group by date in psql would be appreciated.