Snowflake SQL finding each users Monthly login events from daily login events

Question

I have the following table of daily user logins

I need to write a query that will display the following monthly logins by each user as shown below:

The query I have is ... (BUT THIS GIVES ME DUPLICATE ROWS AND INCORRECT COUNTS)

select 
TO_DATE(Timestamp), 
User,
count(Transactions) over (partition by User)
from
(
//(0)
select Timestamp, User, Transactions 
from
MyTable
where
Transactions = 'Login' and
event_timestamp BETWEEN '2019-01-01' AND '2019-10-31' 
order by Timestamp, User 
) 

Appreciate any help in putting together the right query for this.

Answer 1

If I understand correctly, this is basically an aggregation query:

select user, date_trunc('month', timestamp), count(*), 
from MyTable
where Transactions = 'Login' and
       event_timestamp BETWEEN '2019-01-01' AND '2019-10-31' 
group by user, date_trunc('month', timestamp)
order by user, min(timestamp);