Google Interview Question for Analysts


Country: United States




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

What is the table structure.

- Harish February 23, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id, diff(u.flightTimestamp - currentTimestamp) as t from flightTable u where u.src = 'A' and u.dst = 'B' order by t limit 1;

- keshy February 23, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id, (u.flightTime - TIMESTAMP(now) as d from FlightTable u where u.src = 'A' and u.dst = 'B' and d > 0 order by d limit 1;

The record id can be used to provide the info on the earliest available flight.

- keshy February 23, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id, (u.flightTime - TIMESTAMP(now) as d from FlightTable u where u.src = 'A' and u.dst = 'B' and d > 0 order by d limit 1;


The record id can be used to provide the info on the earliest available flight.

- keshy February 23, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select id, (u.flightTime - TIMESTAMP(now) as d from FlightTable u where u.src = 'A' and u.dst = 'B' and d > 0 order by d limit 1;

The record id can be used to provide the info on the earliest available flight.

- keshy February 23, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

//mongodb
db.collection.findOne({
    '$query': {
        from: 'A',
        to: 'B',
        departure: {
            '$gt': new Date()
        }
    },
    '$orderby': {departure: 1}
});

- kyduke February 24, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- Nick Jin February 24, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Too many variables.

- Noobie February 24, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

How about?

SELECT TOP 1 * FROM  FlightTable as F where F.Departure = 'A' AND F.Destination= 'B' ORDER BY F.DepDate,F.DepTime ASC;

- n_h February 25, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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;

- evgueni.f February 25, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT flight_name FROM flight_db WHERE flight_departure = "A" AND flight_destination = "B" ORDER BY departure_time ASC LIMIT 1;

If a datatable is not prepared departure_time, let's deal it with python, php or shell to clean them as I want.

- careercup_beginner April 29, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

#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

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

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

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

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

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

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

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

select f.id as flight_id from flights f
where f.start_time > current_time and f.source='A' and f.dest='B'
order by (f.start_time - current_time) desc limit 1;

- itsmeashishsingh February 04, 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