365 Days of Daily Coding: Day 140

I tried downloading the Virtual Box to install windows in my mac. The first time it was ok but then I faced some blockers and have not been able to accomplish the task yet. I tried almost 3-4 hours yeterday. I am gonna give a last try today. I hope things go smoothly.

Puzzle #25 : Top Vendors

I tried the solution in the live oracle environment here.

WITH temp AS
(SELECT 'Ord195342' AS OrderID, 1001 AS CustomerID, 12 AS OrderCount, 'Direct Parts' AS Vendor FROM DUAL UNION ALL
SELECT 'Ord245532' AS OrderID, 1001 AS CustomerID, 54 AS OrderCount, 'Direct Parts' AS Vendor FROM DUAL UNION ALL
SELECT 'Ord344394' AS OrderID, 1001 AS CustomerID, 32 AS OrderCount, 'ACME' AS Vendor FROM DUAL UNION ALL
SELECT 'Ord442423' AS OrderID, 2002 AS CustomerID, 7 AS OrderCount, 'ACME' AS Vendor FROM DUAL UNION ALL
SELECT 'Ord524232' AS OrderID, 2002 AS CustomerID, 16 AS OrderCount, 'ACME' AS Vendor FROM DUAL UNION ALL
SELECT 'Ord645363' AS OrderID, 2002 AS CustomerID, 5 AS OrderCount, 'Direct Parts' AS Vendor FROM DUAL
)

SELECT CustomerID, Vendor
FROM(
SELECT CustomerID, Vendor, DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY SUM(OrderCount) DESC) AS rnk
FROM temp
GROUP BY CustomerID, Vendor)
WHERE rnk = 1;