Query the last 30 days last 60 days "true"-CodePudding

Given table login_logs with columns UserName (Varchar), LoginDate (Datetime), was successful (Boolean). Write a query that will return UserName, # of times user successfully logged in last 30 days, # of times user successfully logged in last 60 days

SELECT UserName, LoginDate, was_successful
FROM login_logs
WHERE LoginDate >= DATEADD(day,-30,GETDATE())
AND LoginDate >= DATEADD(day,-60,GETDATE())
AND was_successful = 'true';

What am I doing wrong?

CodePudding user response：

The where clause needs to filter for dates greater than today - 60 days which also covers today - 30 days. Then use conditional aggregation:

select username
     , last_60 = count(*)
     , last_30 = count(case when logindate >= dateadd(day, -30, cast(current_timestamp as date)) then 1 end)
from login_logs
where logindate >= dateadd(day, -60, cast(current_timestamp as date)) and was_successful = 'true'
group by username