Query the last 30 days + last 60 days + "true"

March 10, 2022

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?

>Solution :

The where clause needs to filter by last 60 days which also covers last 30 days. Then use conditional aggregation:

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