Microsoft Interview Question
Software Engineer / DevelopersCountry: United States
Interview Type: Phone Interview
select uid,time,dense_rank() over(order by new_time) session_id from(
select *,case when diff=0 or diff>30 then time else prev_time end as new_time from (
select uid,time,lag(time,1) over (partition by uid order by time) prev_time,isnull(datediff(mi,lag(time,1) over (partition by uid order by time),time),0) diff
from INPUT) A)B order by time
SELECT userID, LoginTime,(
CASE WHEN @prevUserID!=a.userID THEN @SessionID:=@SessionID+1
WHEN @prevUserID=a.userID AND
TIMEDIFF(@prevLogin, LoginTime)>'00:30:00') THEN @SessionID:=@SessionID+1
ELSE @SessionID) AS sessionID, @prevLogin:=LoginTime, @prevUserID:=userId
FROM
(SELECT * FROM input ORDER BY LoginTime ASC) a,
(SELECT @prevUserID:=NULL),
(SELECT @prevLogin:=NULL),
(SELECT @sessionID:=0)
ORDER BY a.LoginTime
SELECT UserId, LoginTime, SessionTime
FROM
(SELECT UserId, LoginTime,
@d:=CASE WHEN @u!=UserID Then @d +1
WHEN TIMEDIFF(LoginTime, @t)>='00:30:00') THEN @d +1
Else @sessionId END AS SessionID,
@t := LoginTime,
@u :=UserID
FROM
(SELECT UserId, LoginTime from input
ORDER BY UserId, LoginTime) a, (SELECT @d := 0) b, (SELECT @t := NULL) c, (SELECT @u := -1) d) e
SET NOCOUNT ON
IF OBJECT_ID('dbo.LoginDetail') IS NOT NULL
DROP TABLE LoginDetail
CREATE TABLE LoginDetail(UserId INT, LoginTime TIME)
insert into LoginDetail values (2, '09:35:00'), (3, '10:20:00') , (1, '09:45:00'), (1, '10:25:00')
insert into LoginDetail values (2, '09:55:00'), (2, '10:10:00') , (2, '11:10:00'), (2, '11:20:00')
insert into LoginDetail values (25, '07:35:00'), (25, '07:40:00') , (25, '08:45:00'), (25, '09:05:00')
-- SELECT * FROM #tmpLoginDetail ORDER BY UserId, LoginTime
IF OBJECT_ID('tempdb..#tmpLoginDetail') IS NOT NULL
DROP TABLE #tmpLoginDetail
SELECT *, ROW_NUMBER() OVER(ORDER BY LoginTime) AS [Row_Id], 0 AS [SessionId], 0 AS [IsProcessed] INTO #tmpLoginDetail FROM LoginDetail ORDER BY LoginTime, UserId
DECLARE @Counter INT, @SessionId INT, @UserId INT, @LoginTime TIME, @RowId INT, @prevUserSessionId INT, @prevUserLoginTime TIME
SET @SessionId = 1
SELECT @Counter = COUNT(1) FROM #tmpLoginDetail
WHILE (@Counter >= 1)
BEGIN
SELECT TOP 1 @UserId = UserId, @LoginTime = LoginTime, @RowId = Row_Id FROM #tmpLoginDetail WHERE IsProcessed = 0 ORDER BY LoginTime
--PRINT 'UserId - ' + CONVERT(VARCHAR(10), @UserId) + ', LoginTime - ' + CONVERT(VARCHAR(10), @LoginTime) +', RowId - ' + CONVERT(VARCHAR(10), @RowId)
IF (@SessionId = 1 AND @RowId = 1)
BEGIN
UPDATE #tmpLoginDetail SET IsProcessed = 1, SessionId = @SessionId WHERE Row_Id = @RowId
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM #tmpLoginDetail WHERE IsProcessed = 1 AND UserId = @UserId)
BEGIN
SELECT @prevUserLoginTime = MAX(LoginTime) FROM #tmpLoginDetail WHERE IsProcessed = 1 AND UserId = @UserId
--PRINT '@prevUserLoginTime - ' + CONVERT(VARCHAR(50), @prevUserLoginTime) + ', @LoginTime - ' + CONVERT(VARCHAR(50), @LoginTime)
IF (DATEDIFF(MI, @prevUserLoginTime, @LoginTime) < 30)
BEGIN
SELECT @prevUserSessionId = SessionId FROM #tmpLoginDetail WHERE IsProcessed = 1 AND UserId = @UserId AND LoginTime = @prevUserLoginTime
UPDATE #tmpLoginDetail SET IsProcessed = 1, SessionId = @prevUserSessionId WHERE Row_Id = @RowId
END
ELSE
BEGIN
SET @SessionId += 1
UPDATE #tmpLoginDetail SET IsProcessed = 1, SessionId = @SessionId WHERE Row_Id = @RowId
END
END
ELSE
BEGIN
SET @SessionId += 1
UPDATE #tmpLoginDetail SET IsProcessed = 1, SessionId = @SessionId WHERE Row_Id = @RowId
END
END
SET @Counter -= 1
END
SELECT * FROM #tmpLoginDetail
table : xxtest_input columns: username, logon_time
WITH T as (
select username, logon_time, ROUND(to_number(logon_time - (lag(logon_time) over (partition by username order by username, logon_time ))) * 24 * 60,2) time_diff_min
from xxtest_input
order by username, logon_time)
select tt1.username, logon_time , rank() over (partition by username order by username, rec_category) sess_id from
(
select tt.* , case when time_diff_min is null then 1 when time_diff_min > 30 then 2 else 1 end rec_category
from t tt ) tt1
order by username, logon_time
;
/*
takes userid as input, returns new/ existing session id as output
*/
select i.*,
(case when sysdate - i.logintime > 30
then
select max(o.sessionid) + 1 from output o
case when sysdate - i.logintime <= 30
then
select max(o.sessionid) from output o where o.userid = i.userid
else
select max(o.sessionid) + 1 from output o
end) as retrievedsessionid
from input i
where i.userid = :InputUserId
and rownum = 1 -- take the row with latest login time as input
order by i.logintime desc
-- takes userid as input, retrieves new/ existing session id as o/p
select i.*,
(case when sysdate - i.logintime > 30
then
select to_number(max(o.sessionid)) + 1 from output o
case when sysdate - i.logintime <= 30
then
select max(o.sessionid) from output o where o.userid = i.userid
else
select to_number(max(o.sessionid)) + 1 from output o
end) as retrievedsessionid
from input i
where i.userid = :InputUserId
and rownum = 1
order by i.logintime desc
; with e as
(
select *, 50 as a from(
select *, row_number() over(partition by UID order by LoginTime) rr from UserSession
)
A where rr=1
union all
select A.*,datediff(minute, e.LoginTime, A.LoginTime) --dateadd(minute,30,e.LoginTime)
from(
select *, row_number() over(partition by UID order by LoginTime) rr from UserSession
)
A inner join e on e.UID=A.UID and e.rr+1=A.rr --where datediff(minute, e.LoginTime, A.LoginTime)>=30 --A.LoginTime>=dateadd(minute,30,e.LoginTime)
)
select UID, LoginTime, row_number() over(partition by UID order by UID) UserSession
from e where a>=30
create table Input
(userid int,
Logintime datetime)
create table Output
(userid int,
Logintime datetime,
SessionId int)
select getdate()
insert into Input values(1,'2017-02-05 09:00:00.000')
insert into Input values(2,'2017-02-05 09:10:00.000')
insert into Input values(1,'2017-02-05 09:25:00.000')
insert into Input values(30,'2017-02-05 12:34:00.000')
insert into Input values(23,'2017-02-05 15:09:00.000')
declare @userid int,@Logintime datetime
declare Rank_Cursor Cursor For
select userid,Logintime from Input
open Rank_Cursor
fetch next from Rank_Cursor into @userid,@logintime
while @@FETCH_STATUS=0
begin
if exists (select 1 from Output where userid=@userid and Logintime>=dateadd(mi,-30,@logintime) and Logintime<@logintime)
insert into Output
select @userid,@logintime,isnull(max(SessionId),1)
from Output
where userid=@userid and Logintime>=dateadd(mi,-30,@logintime) and Logintime<@logintime
else
insert into Output
select @userid,@logintime,isnull(max(SessionId),0)+1
from Output
fetch next from Rank_Cursor into @userid,@logintime
end
close Rank_Cursor
deallocate Rank_Cursor
select * From output
;WITH CTE_UserInput
AS
(
SELECT 1 as UserId, CAST('09:00' as time) AS LoginTime
UNION ALL
SELECT 2 as UserId, CAST('09:10' as time) AS LoginTime
UNION ALL
SELECT 1 as UserId, CAST('09:25' as time) AS LoginTime
UNION ALL
SELECT 30 as UserId, CAST('12:34' as time) AS LoginTime
UNION ALL
SELECT 23 as UserId, CAST('15:09' as time) AS LoginTime
UNION ALL
SELECT 2 as UserId, CAST('15:10' as time) AS LoginTime
UNION ALL
SELECT 1 as UserId, CAST('15:20' as time) AS LoginTime
)
,CTE_Scount
AS
(
SELECT
UserId
,LoginTime
,CASE WHEN DATEDIFF(minute,Lag(LoginTime) OVER (Partition by UserId Order by LoginTime),LoginTime) > 30 Then 1
Else 0 END AS SessionCounter
FROM CTE_UserInput
)
,CTE_AssignSessionID
AS
(
SELECT UserId,SessionCounter,ROW_NUMBER() OVER(Order BY UserId,SessionCounter) AS SessionID
FROM CTE_Scount
Group by UserId,SessionCounter
)
SELECT S.UserID,CONVERT(VARCHAR(15),S.LoginTime,100) AS LoginTime ,D.SessionID
FROM CTE_Scount S
INNER JOIN CTE_AssignSessionID D
ON S.UserId = D.UserId
AND S.SessionCounter = D.SessionCounter
Oracle Version:
CREATE TABLE input (userid number(2), logintime timestamp);
INSERT INTO input VALUES (1, to_date('2018-01-01 09:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (2, to_date('2018-01-01 09:10:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (1, to_date('2018-01-01 09:25:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (30, to_date('2018-01-01 12:34:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (23, to_date('2018-01-01 03:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (1, to_date('2018-01-01 09:45:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO input VALUES (1, to_date('2018-01-01 11:45:00','YYYY-MM-DD HH24:MI:SS'));
commit;
with input_sess
as
(select userid,logintime,case when ( logintime-lag(logintime,1) over(partition by userid order by logintime asc)) >'000000000 00:30:00.000000' then 1 else 0
end SessionCounter from input),
x as (
select userid,SessionCounter,row_number() over( order by userid,SessionCounter ) sessionid from input_sess
group by userid,SessionCounter)
select input_sess.userid,input_sess.logintime,sessionid from x inner join input_sess
on (x.userid=input_sess.userid and x.SessionCounter=input_sess.SessionCounter)
SELECT *, SUM(CONVERT(INT,x.Track)) OVER (PARTITION BY x.userid ORDER BY x.LoginTime) FROM
(SELECT *,CASE
WHEN LaggedTime IS NULL THEN '1'
WHEN (LoginTime - LaggedTime)>30 THEN '1' ELSE '0' END AS Track FROM
(SELECT *,LAG(LoginTime) OVER (PARTITION BY userid Order By LoginTime) AS LaggedTime FROM input) t ) x
SELECT *, SUM(CONVERT(INT,x.Track)) OVER (PARTITION BY x.userid ORDER BY x.LoginTime) FROM
(SELECT *,CASE
WHEN LaggedTime IS NULL THEN '1'
WHEN (LoginTime - LaggedTime)>30 THEN '1' ELSE '0' END AS Track FROM
(SELECT *,LAG(LoginTime) OVER (PARTITION BY userid Order By LoginTime) AS LaggedTime FROM input) t ) x
SELECT userid,logintime,diff,
case
when to_number(substr(diff,1,2)) = 0 and to_number(substr(diff,4,2)) = 0 then userid
when (to_number(substr(diff,1,2)) > 0 or to_number(substr(diff,1,2)) = 0) and to_number(substr(diff,4,2)) > 30 then userid + 1
when to_number(substr(diff,1,2)) > 0 and to_number(substr(diff,4,2)) >= 0 then userid + 1
when to_number(substr(diff,1,2)) = 0 and to_number(substr(diff,4,2)) < 30 then userid
end
as sessionid
from
(
select userid, logintime, pre,trim(nvl(substr(to_char(to_timestamp(logintime,'hh:mi:ss') - to_timestamp(pre,'hh:mi:ss')),11,9),'00:00:00')) diff
FROM
(
select userid, logintime,lag(logintime) over (PARTITION by userid order by userid, logintime) as pre from logindetail
)
);
WITH times AS (
SELECT
user_id,
some_time,
lag(some_time,1) OVER (PARTITION BY user_id ORDER BY some_time) AS prev_time
FROM log_times)
, time_diff AS (
SELECT
user_id,
some_time,
coalesce(datediff('min',prev_time,some_time),0) AS min_diff
FROM times)
SELECT
user_id,
some_time,
1 AS rn
FROM time_diff
WHERE min_diff >=30
UNION ALL
SELECT
user_id,
some_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY some_time) AS rn
FROM time_diff
WHERE min_diff <30
ORDER BY 1,2,3
WITH times AS (
SELECT
user_id,
some_time,
lag(some_time,1) OVER (PARTITION BY user_id ORDER BY some_time) AS prev_time
FROM log_times)
, time_diff AS (
SELECT
user_id,
some_time,
coalesce(datediff('min',prev_time,some_time),0) AS min_diff
FROM times)
SELECT
user_id,
some_time,
1 AS rn
FROM time_diff
WHERE min_diff >=30
UNION ALL
SELECT
user_id,
some_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY some_time) AS rn
FROM time_diff
WHERE min_diff <30
ORDER BY 1,2,3
This following solution uses recursion, so that it can properly track a user that connects multiple times, every time being within the 30-min window from the last login.
This is the schema and data I'm using:
(I'm only using the view for clarity; see at the end for the final query that does not require additional commands).
Notice that there are three chained logins for userid 1: 9:00, 9:25 and 9:45. The following query will properly assign the IDs:
Result table:
The stand alone query (no view required) is as follows:
- leo.bioeng December 15, 2017