Microsoft Interview Question for Software Engineer / Developers


Country: United States
Interview Type: Phone Interview




Comment hidden because of low score. Click to expand.
8
of 8 vote

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:

CREATE TABLE input (userid tinyint, logintime time);
INSERT INTO input VALUES (1, '9:00am');
INSERT INTO input VALUES (2, '9:10am');
INSERT INTO input VALUES (1, '9:25am');
INSERT INTO input VALUES (30, '12:34pm');
INSERT INTO input VALUES (23, '3:00am');
INSERT INTO input VALUES (1, '9:45am');
INSERT INTO input VALUES (1, '11:45am');

CREATE VIEW input2 AS SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input;

(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:

WITH CTE (userid, logintime, previousEventTime, currentLevel) AS
(SELECT userid, logintime, logintime, 1 FROM input2 WHERE rownumber=1
UNION ALL
SELECT a.userid, a.logintime, 
CASE 
   WHEN datediff(mi, b.logintime, a.logintime) < 30 THEN b.previousEventTime
   ELSE a.logintime
END,
b.currentLevel+1
FROM input2 as a INNER JOIN CTE as b ON a.userid = b.userid WHERE rownumber = b.currentLevel+1)
SELECT userid, logintime, DENSE_RANK() OVER (ORDER BY previousEventTime) as SessionID FROM CTE;

Result table:

userid	logintime	        SessionID
23	    03:00:00.0000000	1
1	    09:00:00.0000000	2
1	    09:25:00.0000000	2
1	    09:45:00.0000000	2
2	    09:10:00.0000000	3
1	    11:45:00.0000000	4
30	    12:34:00.0000000	5

The stand alone query (no view required) is as follows:

WITH CTE (userid, logintime, previousEventTime, currentLevel) AS
(SELECT userid, logintime, logintime, 1 FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input) c WHERE rownumber=1
UNION ALL
SELECT a.userid, a.logintime, 
CASE 
   WHEN datediff(mi, b.logintime, a.logintime) < 30 THEN b.previousEventTime
   ELSE a.logintime
END,
b.currentLevel+1
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input) as a INNER JOIN CTE as b ON a.userid = b.userid WHERE rownumber = b.currentLevel+1)
SELECT userid, logintime, DENSE_RANK() OVER (ORDER BY previousEventTime) as SessionID FROM CTE;

- leo.bioeng December 15, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
2
of 2 vote

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

- kapil gurjar October 10, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select UserId,LoginTime,
CASE
	WHEN ((prevlogintime ='') OR (currenttime-prevlogintime) >30 min) 

 		THEN sessionid.nextval
	ELSE
		prevsessionid
	
END
from usertable

for new user prevlogintime,prevsessionid should be empty.

- sv July 16, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

{select *,
sum(diff) over(order by UserID, LoginTime)
from (
select *,
case when datetdiff(minute, LoginTime, avg(LoginTime)) over(partition by UserID order by UserID, LoginTime rows between 1 preceding and 1 preceding) < 30 then 0 else 1 end diff
from Input;}

- Anonymous August 04, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select *,
sum(diff) over(order by UserID, LoginTime)
from (
select *,
case when datediff(minute, LoginTime, avg(LoginTime)) over(partition by UserID order by UserID, LoginTime rows between 1 preceding and 1 preceding) < 30 then 0 else 1 end diff
from (Input);

- Kosta August 04, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

{select *,
sum(diff) over(order by UserID, LoginTime)
from (
select *,
case when datediff(minute, LoginTime, avg(LoginTime)) over(partition by UserID order by UserID, LoginTime rows between 1 preceding and 1 preceding) < 30 then 0 else 1 end diff
from (Input);}

- Kosta August 04, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Hongjie October 23, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select UserId,LoginTime,
CASE
WHEN ((ISNULL(prevlogintime,'') ='') OR Datediff(Minute, prevlogintime, currenttime) > 30)
THEN sessionid.nextval
ELSE
prevsessionid
END
from usertable

- Manis Mishra November 16, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- HC December 02, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- rp.arunachalam May 15, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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
;

- monika.m.srivastava June 15, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

/*
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

- Mayand Tiwari September 16, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

-- 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

- mayand.tiwari3 September 16, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

; 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

- NehaaVishwa January 24, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- rank.rao.7.n February 05, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

;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

- pnain101 July 13, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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)

- Harry May 29, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Hamd Mazhar September 10, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Hamd Mazhar September 10, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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
)
);

- Sriharsha July 28, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Anonymous September 20, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Anonymous September 20, 2019 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More