I am new to
psql 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
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.