0

I have a case study which is similar to the one discussed here: Count active users using login timestamp in MySQL

However I would like to solve it in SQL Server language (not mySQL) and I have 2 slightly different requests: Given the table containing User_id and login_timestamp calculate:

1) the Ratio of people who logged every day in the last week 2) the monthly frequency of access of people, given their latest login (e.g. assuming their last login was yesterday, what has been the monthly frequency starting from yesterday and looking 1 month back?)

For the first question I have the answer, but I would like to ask if someone can validate it for me? 

select sum(A.access)/count(A.access) as ratio_weekly_access
from
(select user_id,
case when
    count(distinct format(timestamp_login,'YYYY-MM-DD')) =7 then 1
    else 0 end as access
from xxx
where FORMAT(timestamp_login,'YYYY-MM-DD') between dateadd(day,-7, cast(getdate() as date)) and cast(getdate() as date)
group by user_id
) A

Thank you in advance for your help!

Community
  • 1
  • 1
Elly
  • 129
  • 2
  • 12
  • Your question is not clear. What do you mean by _the Ratio of people how logged every day in the last week_? A **RATIO** requires **TWO** references (for instance, _the percent of registered users that logged into the system every day during the last week_ would mean a comparison against the total number of registered users). Besides, you need to check that there was at least one login each of the last 7 days; it is not enough to count them (could be 7 logins in the same day). – FDavidov Oct 16 '16 at 08:52
  • Sorry for not being clear. So by ratio I mean "proportion of users who logged-in 7 times in a week out of the total number of users who logged in at least once). – Elly Oct 16 '16 at 09:01
  • I did "count(distinct format(timestamp_login,'YYYY-MM-DD')) =7 " where I have transformed timestamp into date format so if I do "Distinct" I expect to get the count of unique dates. Also I already filtered in the "where statement" only the timestamp between today and one week before, so this should give me just the timestamps of interest. – Elly Oct 16 '16 at 09:03
  • OK, so you need to compare the number of users that logged at least once every day in the last 7 against those that logged at least once in the last 7 days. I think your query has two problems: (1) I don't see how you count those that logged in at least once (by the way, should this include the users that logged every day too?), (2) in the `case`, the `count (distinct...)`, I don't think it will work since the case would examine each record of the results set matching the `where` condition. – FDavidov Oct 16 '16 at 09:14
  • let me try to exaplain my logic – Elly Oct 16 '16 at 09:30
  • I have doubts about the `CASE...COUNT`, but don't have access to a DB to verify it. If you tried and it worked, then it looks OK. – FDavidov Oct 16 '16 at 09:37

0 Answers0