Adobe Interview Question | Customers

Question

You have a table called with Cust_ID, Order_Date, Order_ID, Tran_Amt. How would you select the top 100 customers with the highest spend over a year-long period?

in progress 0
Dhruv2301 2 years 2 Answers 601 views Great Grand Master 0

Answers ( 2 )

  1. SELECT Cust_id, order_date, order_id, tran_amt
    FROM Orders
    WHERE DATEDIFF(day, order_date, get_date()) > 365
    ORDER BY tran_amt DESC;

  2. In Oracle:-
    SELECT cust_id,total_spend FROM (SELECT cust_id, sum(tran_amount) total_spend FROM customer
    WHERE ORDER_DATE >= add_months(sysdate,-12)
    GROUP BY cust_d
    ORDER BY 2 desc
    ) WHERE rownum <101;

  3. In Oracle:-

    SELECT cust_id,total_spend FROM (SELECT cust_id, sum(tran_amount) total_spend FROM customer
    WHERE ORDER_DATE >= add_months(sysdate,-12)
    GROUP BY cust_d
    ORDER BY 2 desc
    ) WHERE rownum <101;

Leave an answer

Browse
Browse