0

I have a table called bills with the following columns: id, product – product id, product_group – product_group_id, Revenue – revenue in some currency

I would like to get top-2 products from top-5 product groups as of their revenue. Any help is appreciated.

This is i have gone so far: 

select t.sum_product_group,
    t.sum_product
from (
    select bills.product_group,
        bills.product,
        bills.revenue,
        sum(bills.revenue) over (partition by bills.product_group) as sum_product_group,
        sum(bills.revenue) over (partition by bills.product) as sum_product,
    from bills
    ) as t
order by t.sum_product_group desc,
    t.sum_product desc limit 2
Andrey
  • 1
  • 2
  • Do you want a global top-5 product, which is also in the top-7 group, or top-5 products **per** the top-7 groups? Also, how do you want to handle ties? (I.e. when multiple products/groups have the same `sum(revenue)`.) -- Also, in your first sentence, the limits are the exact opposite: please clarify. – pozs May 08 '17 at 14:02
  • Thank you for your comments. That is right I made a mistake in the description. I would like to get top-7 products out of top-5 product groups as of their sum revenue (not top 7 products for each top product group). If there are ties we can use minimum id in order to obtain a limited selection of top-5 product groups and top-7 products within those 5 product groups. – Andrey May 08 '17 at 16:05
  • I have changed the condition of my task. Now i would like to get top-2 products in each of the top-5 product groups as of their revenue. I have found this information but can not integrate it my solution: http://charlesnagy.info/it/postgresql/group-by-limit-per-group-in-postgresql – Andrey May 10 '17 at 16:48
  • Can you post some example data and the output you expect? – Andreas May 10 '17 at 17:04
  • Here is a link to initial dataset https://www.dropbox.com/s/avvw9u215fdm84u/1.jpg?dl=0 – Andrey May 12 '17 at 06:26
  • Blue columns - initial column. Green - computed columns (I suppose using window functions etc.) – Andrey May 12 '17 at 06:27
  • And here is a link to desired output: https://www.dropbox.com/s/avvw9u215fdm84u/1.jpg?dl=0 – Andrey May 12 '17 at 06:28

0 Answers0