5

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.

1
  • 1
    You don't show data, so it's a bit hard to understand what you mean when you say "first time"? If a user visits a site on two distinct days, do they only get tallied once on the first day, or on every day they visit once. Mar 19, 2018 at 5:23

1 Answer 1

8

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.