Amazon Interview Question: You have a table called Order... | Glassdoor

Interview Question

Business Intelligence Analyst Interview Seattle, WA

You have a table called Order with Cust_ID, Order_Date

 , Order_ID, Tran_Amt. Select the top 100 customers with high spend over a year long period.
Answer

Interview Answer

4 Answers

45

SELECT Cust_ID FROM (
SELECT Cust_ID, SUM (Tran_Amt) FROM Order
WHERE Order_Date between DATE_SUB( CURDATE() , INTERVAL 1 YEAR) AND CURDATE()
GROUP BY (Cust_ID)
ORDER BY SUM (Tran_Amt) DESC LIMIT 100) ;

PS - I will suggest to replace the function CURDATE() and DATE_SUB( CURDATE() , INTERVAL 1 YEAR) with a date in number format for query performance. I don't think that we will executing the query more often.

kimo on Jun 17, 2012
13

Select *
FROM(
Select Cust_id, Year, Totals, RANK() OVER (PARTITION by a.Year ORDER by a.Totals DESC) AS TopLimits
FROM (
Select DISTINCT Cust_Id, DatePart(yy,Order_date) AS Year, SUM(Tran_Amt) OVER (Partition by a.Cust_id, DATEPART(yy,a.Order_date)) Totals
FROM dbo.[Order] a ) a) b
WHERE b.TopLimits <= 100

This is performs the top 100 for all the years

Srikanth on Oct 6, 2013

This post has been removed.
Please see our Community Guidelines or Terms of Service for more information.

1

select customer_id
from (select customer_id,sum(trans_amt) as a,DATE
     from orders
     WHERE Date between
     DATE_SUB( CURDATE() , INTERVAL 1 Year) AND CURDATE()
     group by (customer_id)
     order by (a) DESC
     LIMIT 100) AS B;

Juhi on Jan 25, 2018

Add Answers or Comments

To comment on this, Sign In or Sign Up.