Basic SQL questions. Describe a join to a non-technical person. How do you handle a query that does not perform quickly? They want to know that you can use 'explain plans', which I currently do not use (I'm still entry level). Select all customers who purchased at least two items on two separate days. Given a table with a combination of flight paths, how would you identify unique flights if you don't care which city is the destination or arrival location.

Question

Basic SQL questions.  Describe a join to a non-technical person.  How do you handle a query that does not perform quickly?  They want to know that you can use 'explain plans', which I currently do not use (I'm still entry level).  Select all customers who purchased at least two items on two separate days.  Given a table with a combination of flight paths, how would you identify unique flights if you don't care which city is the destination or arrival location.

PewPewPew · Accepted Answer

Customer problem:

select customerId 
from orders 
group by customerId
having count(distinct date(orderDate)) > 1; -- Assuming the orderDate has time associated with it.

Flights problem:

select arrival, departure from flights
union
select departure, arrival from flights;

Anonymous · Answer

Select all customers who purchased at least two items on two separate days. 

select cust, count(distint item)  as ‘uitem’, count(distinct date) as `udate`
from purchase_list
group by cust
having `udate` >=2 and uitem >=2;


Given a table with a combination of flight paths, how would you identify unique flights if you don't care which city is the destination or arrival location. 

select distinct flight id from flight_path t1
where arrival||destination in ( select distinct arrival||destination from flight_path t2);

Interview Answer · Answer

Hi, for the question on table with flights data, can we do a distinct to pick unique flights from the table. I am confused what else could be the complex logic here

Anonymous · Answer

How do you handle a query that does not perform quickly?

Tune the SQL. Run explain plan. Take decision accordingly (Read explain plan).  Make sure proper indexes are being used.

Select all customers who purchased at least two items on two separate days. · Answer

select distinct(b.id) from (
select a.id, a.d, a.#items, row_number() over (partition by id order by d) as rn
from
(
select id, d, sum(q) as #items
from cust
group by id, d
having sum(q) >=2
) a)b
where b.rn>2

Anonymous · Answer

CREATE TABLE test_flights (
    origin VARCHAR(255),
    destination VARCHAR(255)
);

INSERT INTO test_flights (origin, destination)
VALUES
('Boston', 'Los Angeles'),
('Los Angeles', 'Boston'),
('New York', 'Pittsburgh'),
('Pittsburgh', 'New York')

SELECT * FROM test_flights
WHERE origin < destination

Anonymous · Answer

For the Customer buying items SQL problem, this is my solution :
select V.CustomerID
 from 
(select c.id as customerID, count(o.Itemid) ItemCount,o.OrderDate as OrderDate, lead(I.orderDate) over(partition by c.id order by o.orderDate) as NextOrderDate 
from customers c inner join Orders o 
on c.id = o.customerid
)v
where v.orderDate!=v.NextOrderDate and  v.ItemCount>=2

Given a table with a combination of flight paths, how would you identify unique flights if you don' · Answer

select flight_id, arr, dep from table
union
select flight_id, dep, arr from table

Ravi Srivastava · Answer

Given a table with a combination of flight paths, how would you identify unique flights if you don't care which city is the destination or arrival location?

select DISTINCT least(source,destination) as X1 ,greatest(source,destination) as X2 from Airplane;

Farheen · Answer

select * from `unique flights`
where DepArrivalID not in (
select u1.DepArrivalID from `unique flights` u1, `unique flights` u2
where
u1.ArrivalCity = u2.﻿DepartureCity and u1.﻿DepartureCity = u2.ArrivalCity and u1.DepArrivalID > u2.DepArrivalID
)

Anonymous · Answer

Hi can you please share the data tables for the problems?

Amazon

Amazon Interview Question

Interview Answers

Want the inside scoop on your own company?

Bowls

Followed companies

Job searches