Converting to date and performing a count 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
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;