1
SELECT a.UserID, a.SignUpDate, DATE_DIFF(CURRENT_DATE, a.SignUpDate, day) As DaysActive 
FROM (SELECT u.Userid, CAST(MIN(u.SessionLogin)AS DATE) AS SignUpDate
      FROM database.data_source.user_session_table as a)

The above code pulls the session login time from the user_session table performs a min function to collect their initial login, whilst converting it from a timestamp to a date format. DaysActive shows how many days since they have logged in.

What i am trying to do is find how many days they have logged into the application since their initial sign in.(Days Active) SessionLogin field captures a timestamp each time they login, i am trying to convert that to a date, whilst also adding it to the above table as a count.

Current Output

UserID  | SignUpDate| DaysActive | 
--------+-----------+-------------
001     |2017-12-04 | 35         |
002     |2017-12-08 | 31         |
003     |2017-12-05 | 34         |

user_session_table

UserID  |SessionLogin         | 
--------+---------------------|
001     |2017-12-04 01:37:49  | 
001     |2017-12-08 05:47:04  | 
001     |2017-12-05 04:40:00  | 
002     |2017-12-08 12:25:39  | 
002     |2017-12-09 01:37:49  | 
003     |2017-12-05 05:47:04  | 
003     |2017-12-05 08:40:00  | 

Desired Output

UserID  | SignUpDate | DaysActive| DaysEngaged
--------+-----------+------------+-----------
001     |2017-12-04 | 35         |3
002     |2017-12-08 | 31         |2
003     |2017-12-05 | 34         |1

Code: MSSQL / Bigquery

3

Don't know about big query but for SQL Server you could do it like this.

select S.UserID,
       min(cast(S.SessionLogin as date)) as SignUpDate,
       datediff(day, min(cast(S.SessionLogin as date)), getdate()) as DaysActive,
       count(distinct cast(S.SessionLogin as date)) as DaysActive
from dbo.SessionLogin as S
group by S.UserID;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.