Interview went as follows: You're guided to a room on the basement floor (where there's a futon and a futon bed along with a conference table with chairs) and given 4 papers stapled together. The first two papers have a question each. First, you have to write a query that will return the top 5 products sold by any given sales representative in the last six months, with the total sales amounts listed in descending order.
Second, you are asked to identify the data issues confronting a consulting firm hired to build the a coin collector that is going to be installed at the entrance to the UN. The coin collector can take all currencies. Every time $100 USD has been collected from any country, a bell will ring and someone will come and remove the coins. For 10 minute period each day the coin collector will be off line and connected to the internet.
The query to satisfy the first part of the two part question was:
--query
;with cte as
(
select
sum(salesamt) over (partition by salesrep, product order by salesrep) as sumOfSalesAmt,
salesrep,
product
from sales s
join salesrep sr on s.sid=sr.sid
join product p on p.pid=s.pid
where s.qty >= 1
group by salesrep, product, salesamt
), cte1 as
(
select
rank() over (partition by salesrep order by sumOfSalesAmt desc) as rank,
sumOfSalesAmt, salesrep, product
from cte
)
select *
from cte1
where rank <=5;
---query alternative (other alternatives possible including cross apply)
select salesrep, product, sum(salesamt) as SumSalesAmt
from sales s
join product p on s.pid = p.pid
join salesrep sr on sr.sid = s.sid
where s.qty >= 1
group by product, salesrep
order by salesrep, sum(salesamt) desc;