Facebook Interview Question for Data Engineers


Country: United States
Interview Type: In-Person




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

as mysql query

SELECT id, amount, supp_id FROM Invoice 
WHERE year(date) = 2016 and amount = (select amount FROM Invoice WHERE year(date) = 2016 ORDER BY amount LIMIT 1);

and the DDL to create the schema

CREATE TABLE Supplier
(
	id INT8 NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
);

CREATE TABLE Invoice
(
	id INT8 NOT NULL AUTO_INCREMENT,
	supp_id INT8,
    date datetime,
    amount decimal(6, 2),
    -- payment_date datetime,
    -- paid_amount decimal(6, 2),
    PRIMARY KEY (id)
);

INSERT INTO Supplier(name) VALUES('chris');
INSERT INTO Supplier(name) VALUES('frank');
INSERT INTO Supplier(name) VALUES('jane');

INSERT INTO Invoice(supp_id, date, amount) VALUES ((select id from Supplier WHERE name = 'chris'), STR_TO_DATE('2017-2-21 16:00','%Y-%m-%d %H:%i'), 110.5);
INSERT INTO Invoice(supp_id, date, amount) VALUES ((select id from Supplier WHERE name = 'chris'), STR_TO_DATE('2016-5-11 16:35','%Y-%m-%d %H:%i'), 95.15);
INSERT INTO Invoice(supp_id, date, amount) VALUES ((select id from Supplier WHERE name = 'chris'), STR_TO_DATE('2016-4-09 10:19','%Y-%m-%d %H:%i'), 85.95);
INSERT INTO Invoice(supp_id, date, amount) VALUES ((select id from Supplier WHERE name = 'chris'), STR_TO_DATE('2016-7-09 9:00','%Y-%m-%d %H:%i'), 95.15);
INSERT INTO Invoice(supp_id, date, amount) VALUES ((select id from Supplier WHERE name = 'jane'), STR_TO_DATE('2016-1-09 08:15','%Y-%m-%d %H:%i'), 95.15);

- Chris July 01, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

It should return $95.15. Your query is returning $85.95. For the year 2016 there are 4 lines and highest amount is $95.15. In the subselect order by should be desc. Code tested in SQL Server.

id supp_id date amount

2 1 2016-05-11 16:35:00.000 95.15
4 1 2016-07-09 09:00:00.000 95.15
5 3 2016-01-09 08:15:00.000 95.15

- Ram January 26, 2019 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

I want to know if my query is correct. Please advise.

select id,amount,vdate,rnk
from
(
select id,amount,vdate,rank() OVER (order by amount desc) as rnk
,to_CHAR(vdate,'YYYY')
from invoice
where to_CHAR(vdate,'YYYY')='2016'
)
where rnk=1

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

{select top 1 s.supp_name, i.* from supplier s join invoice i on s.supp_id = i.supp_id where year(i.inv_date) = 2016 order by i.inv_amt desc}

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

Tell the interviewer what he's looking for - a self join to find all the invoices with the highest value. Also tell him that doing a self-join is a very expensive operation, esp when the amount field is not indexed. The most efficient way is to scan the table once and write a client program to find the answer.

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

-- using SQL Server T-SQL. I'm not sure why Suppliers are even listed. You didn't mention that we want them in the result. So I'll assume the Suppliers are there, but we don't need them.
-------code-----
DECLARE @max_paid_amt int

-- this will get the highest amount, but if there's more than one with the same, we won't get the rest.
SET @max_paid_amt = (
SELECT TOP 1 I.paid_amt FROM Invoices I WHERE YEAR(I.inv_date) = 2016 ORDER BY I.paid_amt DESC
)

-- select all of them. All fields in the table.
SELECT I.*
FROM Invoices WHERE year(I.inv_date) = 2016 AND I.paid_amt = @max_paid_amt

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

--using SQL SERVER T-SQL. I'm assuming that the 2 tables are already created and populated. Question doesn't mention that we actually will return anything in Suppliers table, so I'm not.

---code---
DECLARE @max_paid_amt int

-- this will get the highest amount, but if there's more than one with the same, we won't get the rest.
SET @max_paid_amt = (
SELECT TOP 1 I.paid_amt FROM Invoices I WHERE YEAR(I.inv_date) = 2016 ORDER BY I.paid_amt DESC
)

-- select all of them.
SELECT I.*
FROM Invoices WHERE year(I.inv_date) = 2016 AND I.paid_amt = @max_paid_amt

- jerotas2005@yahoo.com July 01, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT DISTINCT inv_id
FROM Invoice AS i1
WHERE 0 = (
	SELECT COUNT(inv_amount)
	FROM Invoice AS i2
	WHERE i2.inv_amount > i1.inv_amount
	AND YEAR(inv_date) = '2016');

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

select inv_id,to_date(inv_date,"YYYY") as year ,inv_amt,
rank() over(partition by inv_id order by inv_amt desc) rank from invoice_table
where to_date(inv_date,"YYYY")='2016'

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

@nikhilesh.singampalli:
looks neat, except of the to_CHAR(...) construct, because, if that column was indexed, you would do a full table scan instead of using the index...

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

select inv_id,sum(amount)
from invoice
group by inv_id
order by 2 desc
limit 1

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

SQL Server only answer - the WITH TIES allows for all the records matching highest invoice amount (I understand this solution is not generic, but for shake of completeness):

SELECT TOP(1) WITH TIES * FROM Invoice WHERE year(inv_date) = 2016 ORDER BY inv_amt DESC

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

Different table but can use this approach

select student_id,marks from student_marks s1 where not exists (select marks from student_marks s2 where s2.marks > s1.marks)

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

select * from (select inv_id, rank() over (order by paid_amt desc) as 'rank' from invoice) a where a.rank=1

- Selvaram October 28, 2018 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

I dont think there will be a single invoice so anything with limit 1 or TOP wont be the right solution, We cant group by either because then we need to use aggregate functions ( max, min) which we are not allowed to use.

This is what I came up with, Let me know if this is wrong -

select inv_id,supp_id,supp_name,inv_amt from (
select i.inv_id,i.supp_id,s.supp_name,i.inv_amt, rank() over(partition by i.inv_id order by i.inv_amt desc) as "Rank" from invoice i
left outer join Supplier s
on i.supp_id=s.supp_id
where year(i.payment_date)='2016' ) A
where A.Rank = 1

Please note that I am joining supplier table to get supp_name, if we dont need supplier name then that join can be removed.

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

Can someone check and confirm if this is correct -

select inv_id,supp_id,inv_date,inv_amt from (
select i.id as inv_id,s.id as supp_id,i.inv_date,i.inv_amt,dense_rank() over(partition by i.id,s.id,i.inv_date order by i.inv_amt desc) as ranks from invoice i
left outer join supplier s
on i.supp_id=s.id
where date_part('year',i.inv_date)='2016' ) A
where A.ranks=1

- itsmeashishsingh February 04, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

MYSQL version

select id,
from
(select id,amount,rank() over (order by amount desc) as Ranking
from Invoice
where year(date) = 2016) t
where t.Ranking = 1;

- Arjit Lamba June 14, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 1 vote

select top 1 s.supp_name, i.* from supplier
join invoice i on i.supp_id = s.supp_id
where year(i.inv_date) = 2016
order by i.inv_amt desc

- Steven July 01, 2017 | 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