Amazon Interview Question
Software Engineer / Developers1. select * from Order where OrderDate = (date() - 1);
2. select count(*) from OrderItem where OrderID in (select OrderID from Order where OrderDate = (date() - 1));
3. select orderid from (select Order.OrderID as orderid, count(*) as count from OrderItem INNER JOIN Order on OrderItem.OrderID = Order.OrderID where Order.OrderDate = (date() - 1) group by Order.OrderID) where count > 3;
4. select count(*) from (select Order.OrderID as orderid, count(*) as count from OrderItem INNER JOIN Order on OrderItem.OrderID = Order.OrderID where Order.OrderDate = (date() - 1) group by Order.OrderID) where count > 3;
I think an easier way to 3) would be something like:
select orderID from order where OrderDate = (date()-1)
Intersect
select orderID from OrderItem group by orderID having count(orderID)>3
Im unsure of how correct that is..
For 2nd may be we can do in 1 step only --
select count(OrderID) from Order where OrderDate = (date() - 1)
select * from OrderTab where DATEDIFF(d, OrderDate, getdate()) = 1
select count(*) from OrderTab where DATEDIFF(d, OrderDate, getdate()) = 1
select * from OrderTab inner join OrderItem
on OrderTab.OrderId = OrderItem.OrderId
where DATEDIFF(d, OrderDate, getdate()) = 1
group by OrderTab.OrderId, OrderDate, ItemId, OrderItem.OrderId
having Count(ItemId) > 3
- dog_faced_bastard January 24, 2011