mysql - Unable to get count of logins for different time spans -
hi facing issue while implementing query calculate login counts different time spans(3 months, 6 months, 1 year etc)
i have 2 tables :
table x
main lastlogin
table y
main userid lastlogin
i want return number of logins last 3 months, last 6 months, last 1 year based on user id.
result table
userid logincount(3months) logincount(6months) logincount(1year)
i able implement following query gives login counts last 3 months. how other 2 columns of 6 months , 1 year.
my query :
select y.userid, count(x.lastlogin) count_3months x inner join y on x.main = y.main x.lastlogin > current_date - interval '90' day group 1;
use conditional aggregation:
select y.userid, sum(x.lastlogin > current_date - interval 3 month) count_3months, sum(x.lastlogin > current_date - interval 6 month) count_6months, sum(x.lastlogin > current_date - interval 1 year) count_1year x inner join y on x.main = y.main group userid;
Comments
Post a Comment