CareerCup

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

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

{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}

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.

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

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

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

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'

@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...

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

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

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)

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

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.

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

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;

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