How to use correct count distinct - Snowflake-CodePudding

I have the below query were I would like to get the number of distinct users who have logged in within a period of a year.

Im trying the below:

select 
    'Spain' as "COUNTRY"
    , 'Snowflake' as "TECHNOLOGY"
    , year(event_timestamp) as "YEAR"
     , month(event_timestamp) as "MONTH"
     , 'USERS_LOGIN' as "KIND"
     , distinct count(user_name) as "AMOUNT" 
from snowflake.acount_usage.login_history 
group by year(event_timestamp), month(event_timestamp);

However, if I try this query I don't get the result I want because the distinct clause and the event_timestamp kind of data seems to not allow this kind of execution.

Does anyone have any advice, please? Thank you in advance.

CodePudding user response：

distinct count(user_name) will just give you one result, the count, the distinct here is irrelevant. if you want a count of distinct usernames change the code to:

select 
    'Spain' as "COUNTRY"
    , 'Snowflake' as "TECHNOLOGY"
    , year(event_timestamp) as "YEAR"
     , month(event_timestamp) as "MONTH"
     , 'USERS_LOGIN' as "KIND"
     , count(distinct user_name) as "AMOUNT" 
from snowflake.acount_usage.login_history 
group by year(event_timestamp), month(event_timestamp);

then the group by will play ball.