CareerCup

  • Questions
  • Forum
  • Resume Tips
  • RSS
  • Sign In

Amazon Interview Question


Country: United States
Interview Type: Phone Interview



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

SELECT student_id,
department,
start_date,
LEAD(START_DATE) OVER (ORDER BY STUDENT_ID) END_DATE
FROM STUDENT

- sudarsan March 15, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

WITH stud as
(SELECT student_id, department, start_date, row_number() over(partition by student_id order by start_date) as start_rank from student)
SELECT a.student_id, a.department, a.start_date, b.start_date as end_date FROM stud a
INNER JOIN stud b ON (a.student_id = b.student_id AND a.start_rank = b.start_rank-1);

- GJ April 04, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

select student_ID, department , Start_Date , LEAD(Start_Date) OVER ( ORDER BY Start_Date) AS End_Date from student;

- arpigupta7 September 15, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

with cte_student as
(
select student_id, department, start_date, rank() over (partition by student_id order by Start_date) as 'Ranking' from Student
)

select a.student_id, a.department, a.start_date, b.start_date as End_Date from cte_student a
join cte_student b on a.student_id = b.student_id
and a.Ranking = b.ranking - 1

- Mahi March 09, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote
{{{ update A set A.end_date=B.start_date from student A left outer join student B on A.studentId=B.studentId and A.start_date>=B.start_date and A.dept_id<>B.dept_id - mdeepthi.89 March 28, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

I tested Mahi's answer and Sudarsan's answer, but results are not correct.

here is my SQL, tested correct.

Select a.student_id, a.department_id, a.start_dt , b.start_dt as End_Date
From student a left join student b on a.student_id = b.student_id
and b.start_dt = (select min(start_dt) from student c where c.student_id = a.student_id and c.start_dt > a.start_dt )
order by 1,3

- Maggie April 01, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select [studentid],[Dept],[start_date],(select min(start_date) from Students S2 where S1.studentid =S2.studentid and s2.start_date >s1.start_date)
from [dbo].[Students] S1

- Anonymous April 09, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select [studentid],[Dept],[start_date],(select min(start_date) from Students S2 where S1.studentid =S2.studentid and s2.start_date >s1.start_date)
from [dbo].[Students] S1

- sqlwrite April 09, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

WITH NEW_CASE AS (SELECT STUDENT_ID, DEPARTMENT,START_DATE, RANK () OVER (ORDER BY START_DATE) AS RANK1 FROM STUDENT)

SELECT
A.STUDENT_ID,
A.DEPARTMENT,
A.START_DATE,
B.START_DATE
FROM
(SELECT STUDENT_ID, DEPARTMENT,START_DATE, RANK () OVER (ORDER BY START_DATE) AS RANK1 FROM STUDENT) A,
NEW_CASE B
WHERE
A.STUDENT_ID = B.STUDENT_ID (+) AND A.RANK1+1 = B.RANK1 (+)

- SUJIT SINGH April 13, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

WITH NEW_CASE AS (SELECT STUDENT_ID, DEPARTMENT,START_DATE, RANK () OVER (ORDER BY START_DATE) AS RANK1 FROM STUDENT)
   
   SELECT 
   A.STUDENT_ID,
   A.DEPARTMENT,
   A.START_DATE,
   B.START_DATE
   FROM
   (SELECT STUDENT_ID, DEPARTMENT,START_DATE, RANK () OVER (ORDER BY START_DATE) AS RANK1 FROM STUDENT) A,
   NEW_CASE B
   WHERE
   A.STUDENT_ID = B.STUDENT_ID (+) AND A.RANK1+1  = B.RANK1 (+)

- SUJIT SINGH April 13, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Select Id,Department,StartDate,
(Select Min(StartDate) FROM student t WHERE s.StartDate<t.StartDate) as EndDate
from student s

- Karthik April 27, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

It is important to not to forget to include the current department in the output. Follwoing are the three solutions:

--Using rank()
with stud as
(
select student_id, department, start_date,
rank() over (partition by student_id order by Start_date) as ranking from Student
)
select a.student_id, a.department, a.start_date, b.start_date as End_Date from stud a
left join stud b on a.student_id = b.student_id
and a.Ranking = b.ranking - 1

--Using row_number()
WITH stud as
(SELECT student_id, department, start_date, row_number() over(partition by student_id order by start_date) as start_rank from student)
SELECT a.student_id, a.department, a.start_date, b.start_date as end_date FROM stud a
LEFT JOIN stud b ON (a.student_id = b.student_id AND a.start_rank = b.start_rank-1);

--Using aggregate function

SELECT student_id,
department,
start_date,
LEAD(START_DATE) OVER (partition by student_id ORDER BY department) END_DATE
FROM STUDENT

- dhiraj May 14, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select s1.student_id,
s1.department,
s1.start_date,
(select min(s2.start_dt) from student s2 where s1.student_id=s2.student_id and s1.start_date < s2.start_date) as end_date
from student s1;

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

select s1.student_id, s1.department, s1.start_date,
          (select min(start_date)  from student s2 where s1.student_id=s2.student_id and s1.start_date < s2.start_date) as end_date
from student s1

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

Select a.id, a.dept, a.start_dt , b.start_dt as End_Date
From student a left join student b on a.id = b.id
and b.start_dt = (select min(start_dt) from student c where c.id = a.id and c.start_dt >= a.start_dt and c.dept>a.dept)
and a.dept <> b.dept
order by 1,3

- rohinibasuu June 15, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select x.student_id, x.dept_name, x.start_dt, (min(y.start_dt ))as end_dt
from student x left join student y on (x.student_id = y.student_id and x.dept_name != y.dept_name and y.start_dt >= x.start_dt)
group by 1,2 , 3 order by x.student_id , x.start_dt

- shopatlemo June 20, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

With the Sql Server's Lead function:

SELECT StudentID, Department, StartDate, LEAD(StartDate) OVER (ORDER BY StartDate) EndDate FROM Student

Without any function.

SELECT a.studentid, a.department, a.startdate, 
	(SELECT TOP 1 b.startdate FROM student b WHERE a.startdate < b.startdate
		ORDER BY b.startdate) AS EndDate
		FROM student a

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

with stud as
(select studid,dpt,STARTDT, row_number() over(partition by studid order by STUDID,startdt) as start_rank from student )
select a.*,b.STARTDT-1 as enddt from stud a left join stud b on a.studid = b.studid and a.start_rank = b.start_rank-1
order by a.studid,a.startdt;

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

This a solution using sqlite3 on python so the queries can be checked

import sqlite3

SQL_QUERY = '''
 SELECT a.student_id, a.department_id, a.start_dt , b.start_dt as End_Date
 FROM student a
 LEFT JOIN student b ON a.student_id = b.student_id
 WHERE b.start_dt = (SELECT min(start_dt) FROM student c WHERE c.student_id = a.student_id AND c.start_dt > a.start_dt )
 ORDER BY 1,3;
'''

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute('''CREATE TABLE Student
             (student_id int, department_id text, start_dt text)''')
c.execute("INSERT INTO Student VALUES ('1', 'A', '2017-01-1')")
c.execute("INSERT INTO Student VALUES ('1', 'B', '2017-07-1')")
c.execute("INSERT INTO Student VALUES ('1', 'C', '2017-12-1')")

conn.commit()

for row in c.execute(SQL_QUERY):
    print row

conn.close()

- Fernando August 03, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT A.Student_ID ,
A.Department ,
A.Starts_Date ,
( SELECT MIN(B.Starts_Date)
FROM #Students AS B
WHERE B.Student_ID = A.Student_ID
AND B.Starts_Date > A.Starts_Date
) AS End_Date
FROM #Students AS A;

- Mahabubul Islam November 20, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

There are quite a few wrong answers on here so just be cautious. It helps if you can run a few test cases w/ real sql to verify. Here are two simple validated answers (one w/ window function, one without):

1.
select *, lead(start_date) over (partition by student_id order by start_date) as end_date
from student

2.
select s.*,
(select min(start_date) from student where student_id=s.student_id and start_date > s.start_date)
from student s

- pepsi March 18, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select student_id,department,start_date,(select min(start_date) from student s1 
where s1.student_id=s2.student_id and s1.start_date>s2.start_date ) end_date from student s2;

- Anonymous July 19, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select student_id,department,start_date,(select min(start_date) from student s1 
where s1.student_id=s2.student_id and s1.start_date>s2.start_date ) end_date from student s2;

- akansha July 19, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

I dont think we need to order by or partition by any columns because the question says that they need start_date of the next record as end_date of first record irrespective of its value.

This is what I came up with -

with lead function -

select student_id,department,start_date,lead(start_date,1) over() as "End_date" from student;


without lead function -

SELECT a.student_id, a.department, a.start_date,
(SELECT b.start_date FROM student b WHERE a.start_date < b.start_date
ORDER BY b.start_date limit 1) AS End_Date
FROM student a

Please let me know if this seems correct.

- itsmeashishsingh November 17, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select student_id, department, start_date, LEAD(start_id,1) over(partition student_id order by start_date) as End_date
from Student

- heavenchild.88 November 28, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT ID, SUBSTRING_INDEX(ST,',',1) AS STARTDATE, SUBSTRING_INDEX(ST,',',-1) AS ENDDATE FROM
(SELECT ID,GROUP_CONCAT(START_DATE) AS ST FROM STUDENT GROUP BY ID) T;

- Sudhanshu Sharma December 20, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select q1.*, q2.start_date as end_date from 
(select row_number() over (partition by t1.student_id order by t1.start_date) as int_id,* from student t1)q1
left join
(select row_number() over (partition by t1.student_id order by t1.start_date) as int_id,* from student t1)q2
on
q1.int_id =q2.int_id-1
and q1.student_id = q2.student_id

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

select q1.*, q2.start_date as end_date from
(select row_number() over (partition by t1.student_id order by t1.start_date) as int_id,* from student t1)q1
left join
(select row_number() over (partition by t1.student_id order by t1.start_date) as int_id,* from student t1)q2
on
q1.int_id =q2.int_id-1
and q1.student_id = q2.student_id

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

select Student_id, Department, s1.start_date,
(
select count(s2.start_date)
from
(
select distinct start_date
from student
)s2 where s1.start_date < s2.start_date ) + 1 as 'End_date'
from student s1
order by end_date

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

Select student_id, department, start_date,
lead(start_date) over (partition by student_id order by start_date asc) as end_date from student

- PG September 01, 2020 | 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