SQL query to fetch best bid for auction and number of bidders who have submitted that bid-CodePudding

I am working on a project where I want to fetch from my postgres database for each auction the best bid (best bid being the bid with least amount) and the number of bidders who have submitted that bid with the least amount. I am trying to write the query for this. The schema is as follows -

Auction Table

auction_id (PK)	auction_data
A1	a1_data
A2	a2_data
A3	a3_data

Bids Table

bid_id (PK)	auction_id (FK)	user_id	bid_price
B1	A1	U1	P1
B2	A2	U1	P2
B3	A1	U2	P3
B4	A2	U2	P4
B5	A2	U3	P4

The constraint is that for each auction any user can submit only a single bid. So if the query is run on the above data the output should be like assuming (P1 > P3 and P2 > P4)-

auction_id	best_bid_price	best_bid_submitted_times	auction_data
A1	P3	1	a1_data
A2	P4	2	a2_data
A3	null	null	a3_data

I have tried the following query to get the best bid value but I am unable to come up with their count in the same query. Any help would be welcome, I am a newbie to SQL and hence struggling to grasp how to write this query.

select bidRanks.bid_price, bidRanks.auction_id, bidRanks.auction_data
    from (
        select bids.bid_price, bids.auction_id, auction_auction_data rank() over(partition by bids.auction_id order by bids.bid_price asc) as bidRank
        from auctions left join bids on auction.auction_id = bids.auction_id
    ) as bidRanks where bidRanks.bidRank = 1

CodePudding user response：

Group by auction and price to get the counts. Then pick the best price per auction. I am doing this in below query with PostgreSQL's DISTINCT ON.

selects
  a.auction_id,
  b.bid_price as best_bid_price,
  b.bid_count as best_bid_submitted_times,
  a.auction_data
from auction a
left outer join
(
  select distinct on (auction_id) auction_id, bid_price, count(*) as bid_count
  from bids
  group by auction_id, bid_price
  order by auction_id, bid_price
) b on b.auction_id = a.auction_id
order by a.auction_id;

CodePudding user response：

Check this. I'm first getting the least bid price for each auction and then counting them

SELECT 
auction_id,
auction_data,
min(bid_price) as best_bid_price,
count(bid_price) as best_bid_submitted_times
FROM (
    select 
    a.auction_id, 
    a.auction_data,
    b.bid_price,
    rank() over (partition by a.auction_id order by b.bid_price asc) as rnk
    from auctions a
    left join bids b
    on (a.auction_id = b.auction_id)) a
WHERE rnk = 1
GROUP BY 
auction_id,
auction_data

Alternatively, you can also get the Users (U1/U2...) in the sub query and count the distinct users in the outer query to get best_bid_submitted_times