Retrieving the three highest values in each group

Question

There is a table with the name 'purchase_history' that contains data as shown below:

ID          product           price
123           abcd          1000
123           aaaa          2000
123           aaaa          3000
456           yyyy          50
456           bbbb          6000
456           cccc          450

I'd like to extract the product name and the amount for the highest 3 prices for each user. I want to have exactly one output row for each user (even if the made less than 3 purchases) with fields user_id, product_name_1, amount_1, product_name_2, amount_2, product_name_3, amount_3

Any ideas how I can achieve this result?

Answer 1

Use ROW_NUMBER() window function to filter the top 3 prices for each user and then use conditional aggregation:

select t.id,
  max(case when t.rn = 1 then product end) product_name_1, 
  max(case when t.rn = 1 then price end) amount_1,
  max(case when t.rn = 2 then product end) product_name_2, 
  max(case when t.rn = 2 then price end) amount_2,
  max(case when t.rn = 3 then product end) product_name_3, 
  max(case when t.rn = 3 then price end) amount_3
from (
  select *, row_number() over (partition by id order by price desc) rn 
  from tablename
) t
where t.rn <= 3
group by t.id 

See the demo.
Results:

| id  | product_name_1 | amount_1 | product_name_2 | amount_2 | product_name_3 | amount_3 |
| --- | -------------- | -------- | -------------- | -------- | -------------- | -------- |
| 123 | aaaa           | 3000     | aaaa           | 2000     | abcd           | 1000     |
| 456 | bbbb           | 6000     | cccc           | 450      | yyyy           | 50       |