Find Top Products by Dollar Sale and Top Products by inventory

Database

Find Top Products by Dollar Sale and Top Products by inventory

I have following data in my table

Customer IDProduct IDProduct GroupTransctionsItem SoldDollar Value 
12345226901740chemicals11 $                                    4.60
123451390262490hardware45 $                                  75.79
123452873209146plastic caps1316 $                                121.82
123452564261926Work Boats66 $                                286.64
1234530894722771tape_rolls1010 $                                193.54

and i need the out put like following

Customer IDTop product by Dollar ValueTotal Sale Top product by item soldTotal Item Soldtop product by transactionTotal Transaction
12345Work Boats $      286.64Plastic Caps16Plastic Caps13

 

can anybody help me with SQL, I tried bunch of ways but cant think a correct and most eficient way of getting the results

Thanks in Adv.


Accepted Solutions
Senior Apprentice

Re: Find Top Products by Dollar Sale and Top Products by inventory

You need multiple OLAP funcions with different ORDER BY (resulting in three STATS-steps in Explain) like this:

select CustomerID,
   ProductGroup,
   Transactions,
   max(ItemSold) over (partition by CustomerId),
   first_value(ProductGroup) over (partition by CustomerId order by ItemSold desc)
   max(DollarValue) over (partition by CustomerId),
   first_value(ProductGroup) over (partition by CustomerId order by DollarValue desc)
from tab
qualify row_number () over (partition by CustomerId order by Transactions desc) = 1

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Find Top Products by Dollar Sale and Top Products by inventory

You need multiple OLAP funcions with different ORDER BY (resulting in three STATS-steps in Explain) like this:

select CustomerID,
   ProductGroup,
   Transactions,
   max(ItemSold) over (partition by CustomerId),
   first_value(ProductGroup) over (partition by CustomerId order by ItemSold desc)
   max(DollarValue) over (partition by CustomerId),
   first_value(ProductGroup) over (partition by CustomerId order by DollarValue desc)
from tab
qualify row_number () over (partition by CustomerId order by Transactions desc) = 1

 

 

Re: Find Top Products by Dollar Sale and Top Products by inventory

you are awesome! that is exactly what i wanted.

First_VALUE() is new to me.

Thanks