CODEWITHSUNDEEP
sqlsql-server
pacespring
pacespring

Reputation: 11

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

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?

Upvotes: 0

Views: 1068

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272256

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

Upvotes: 1

Related Questions