Query to find top attributes for each product
Posted on 2014-10-10
I have a product database. It contains hundreds of thousands of transaction history records for thousands of products over time. There 20-30 columns that store various attributes for each record e.g. productid, productname etc. and also columns for e.g. product size and various other product features, etc. as well as columns for customer data pertaining to the purchase.
What I would like to do is find the TOP n number of some given product feature for each product.
e.g. top 5 sizes for each product or top 5 zipcodes of customers who purchased the product.
Ideally I would like the result set to look like this:
e.g. Top 5 zipcodes per product - each zipcode in a column:
Product ID Zipcode Zipcode Zipcode Zipcode Zipcode
Top 5 zipcodes all in the same column:
ProductID Zipcode, Zipcode, Zipcode, Zipcode
What is the best approach to this sort of problem?
Thanks in advance.