Search code examples
sqlpostgresqlmedian

How to extract data in PostgresSQL?

I have a basic database, I am trying to extract the fund_name and amount for the largest 3 investment buy orders for each user. I basically wait to have one output row which consists of: user_id, fund_name_1, amount_1, fund_name_2, amount_2, fund_name_3, amount_3 etc.

I have attached an image of the database.

example database made:

I tried the following but it is not working

SELECT
user_id,
top_fund(1)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY fund_name
        ORDER BY amount) AS top_funds
        FROM investment_buy_order) 
WHERE top_funds IN (1,3)

Any help would be great! (I am a newbie at SQL)

Solution

  • This has nothing to do with medians. Just use conditional aggregation:

    SELECT user_id,
       max(case when seqnum = 1 then fund_name end),
       max(case when seqnum = 1 then amount end),
       max(case when seqnum = 2 then fund_name end),
       max(case when seqnum = 2 then amount end),
       max(case when seqnum = 3 then fund_name end),
       max(case when seqnum = 3 then amount end)
FROM (SELECT ibo.*
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount) AS seqnum
      FROM investment_buy_order ibo
     ) ibo
GROUP BY user_id;