Google Interview Question
AnalystsCountry: United States
with no stop:
select f.id, f.fligh_starttime
from FlightTable f
where f.fligh_starttime > now() and f.start = 'A' and f.end = 'B'
order by f.flight_endtime desc
with one stop:
select f1.id as id1, f1.fligh_starttime start, f2.id as id2, f2.fligh_endtime as endtime
from FlightTable f1, FlightTable f2
where f1.fligh_starttime > now() and
f1.start = 'A' and
f1.end = f2.start and
f1.fligh_endtime < f2.fligh_starttime
f2.end = 'B'
order by f2.flight_endtime desc
1. First solution:
select src,dst, min(time) from table group by 1,2;
This solution is limited, you cannot add id to the projection list.
2. Self left outer join with reverse matched condition, then select only unmatched rows:
select t1.id, t1.time from table as t1 left outer join on table as t2 where t1.src = t2.src and t1.dst = t2.dst and t1.time >= t2.time where t2.src is NULL;
#Assuming table structure as below:
create table Flight
(
FlightNo int
, DepartTime datetime
)
Go
select getdate()
insert Flight
values (1, '2017-01-23 20:53:41.733')
,(2, '2017-01-23 21:53:41.733')
,(3, '2017-01-23 23:53:41.733')
,(4, '2017-01-24 21:53:41.733')
,(2, '2017-01-22 21:53:41.733')
select top(1) FlightNo from(
select *, datediff(minute, getdate(),DepartTime) time
from Flight)A
where sign(time)=1
order by time
Go
From a user prospective, by the earliest flight, it means the earliest she could reach the destination. So, the answer should be one out of the available non-stop flights that reach the destination first.
Considering the flights table with columns and data for only 1 source and destination:
insert into flights values(1, 'NY', 'FL', '2017-01-01 01:00:00', 2);
insert into flights values(2, 'NY', 'FL', '2017-01-01 01:00:00', 3);
insert into flights values(3, 'NY', 'FL', '2017-01-01 01:30:00', 1);
select flight_id, departure_time +
interval (flight_duration_hours || ' hour') reach_time
from flights
order by reach_time
limit 1
FLIGHT_ID REACH_TIME
3 2017-01-01 02:30:00
IF OBJECT_ID('tempdb..#Flight', 'U') IS NOT NULL
DROP TABLE #Flight;
CREATE TABLE #Flight
(
FlightNo INT ,
DepartTime DATETIME
);
GO
INSERT #Flight
VALUES ( 1, '2018-01-23 20:53:41.733' ),
( 2, '2018-01-23 21:53:41.733' ),
( 3, '2018-01-23 23:53:41.733' ),
( 4, '2018-01-24 21:53:41.733' ),
( 2, '2018-01-22 21:53:41.733' );
WITH CTE
AS ( SELECT * ,
DATEDIFF(MINUTE, CURRENT_TIMESTAMP, DepartTime) times_take
FROM #Flight
)
SELECT *
FROM CTE
WHERE CTE.times_take = ( SELECT MIN(CTE.times_take)
FROM CTE
);
Assuming table -
create table flight(id int, depart text, dest text, time_ datetime);
insert into flight values (1, 'A', 'B', '2017-01-01 11:30:00');
insert into flight values (2, 'A', 'B', '2017-01-01 12:30:00');
insert into flight values (3, 'A', 'B', '2017-01-01 10:30:00');
Query -
select id, depart, dest, min(time_) as flight_time from flight
What is the table structure.
- Harish February 23, 2016