Postgresql – Counting unique (distinct) users per day

countdistinctpostgresql

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.

Best Answer

Because your form is off, what you want is

SELECT count(DISTINCT x)
FROM generate_series(1,10) AS gs(x);

Or, in your case,

SELECT start_time::date, count(DISTINCT user_id) 
FROM mytable 
GROUP BY start_time::date;
Related Question