SQL querty struggle - how to count user Logins per day for each user

Question

I have in a Snowflake DB, a table of timestamped user transactions the form (time-stamp, user-email, event-name) as shown below:

I would now like to get a count of User Login's per day, grouped by date, user as shown

I've setup the following query to get the Logins per user per day ...

select to_date(TIMESTAMP) as DATE, LOGINID,
   EVENTNAME from MYEVENTTABLE where
EVENTNAME ='Login' and
TIMESTAMP BETWEEN '2019-10-05T00:00:00.00' AND '2019-10-09T23:24:00.00'
order by DATE, LOGINID

With this I get, for each Day in my selected date range, a list of Logins for each user. Now I just want to count these as sessions for each user for each day as shown in the second table above.

Answer 1

Is this what you are looking for?

select to_date(TIMESTAMP) as DATE, LOGINID,
       count(*)
from MYEVENTTABLE
where eventname ='Login' and
      timestamp >= '2019-10-05' AND 
      timestamp < '2019-10-10'
group by by to_date(TIMESTAMP), LOGINID
order by to_date(TIMESTAMP), loginid;