Search code examples
sqldatetimecountdistinctmin

Finding daily registered users

enter image description here

I have a table which includes the information of player_id, first_timestamp, last_timestamp, and date, etc. So I have initially 10 payers on 2020-07-08, and then 18 players on 2020-07-09, some of the players from previous day might appear on 2020-07-09 again. And I'm trying to find the new players registered on 2020-07-09 that did not appear on 2020-07-08, but I got stuck. Can someone give any suggestion?

Solution

  • I suspect that you want to count each user only once, on the first date when it appears. If so, use two levels of aggregation:

    select date, count(*) no_new_users
from (
    select min(date) date
    from mytable
    group by player_id
) t
group by date