MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Bids (`bid_id` INTEGER, `user_id` INTEGER, `bid_volume` INTEGER, `bid_price` INTEGER);

INSERT INTO Bids (`bid_id`, `user_id`, `bid_volume`, `bid_price`) VALUES
  ('101', '1', '100.0000', '300.0000'),
  ('102', '2', '200.0000', '20.0000'),
  ('103', '3', '50.0000', '40.0000'),
  ('104', '4', '60.0000', '100.0000'),
  ('105', '5', '75.0000', '90.0000');

CREATE TABLE Offers (`offer_id` INTEGER, `user_id` INTEGER, `offer_volume` INTEGER, `offer_price` INTEGER);

INSERT INTO Offers (`offer_id`, `user_id`, `offer_volume`, `offer_price`) VALUES
  ('91', '6', '60.0000', '100.0000'),
  ('92', '7', '75.0000', '85.0000'),
  ('93', '8', '100.0000', '200.0000'),
  ('94', '9', '75.0000', '1000.0000'),
  ('95', '10', '100.0000', '300.0000');

Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


SELECT  
    b.user_id AS bidder_user_id,
    o.user_id AS dealer_user_id,
    b.bid_volume,
    o.offer_volume,
    b.bid_price, 
    o.offer_price
FROM  Bids b 
INNER JOIN (
  SELECT o1.* FROM Offers o1
  WHERE NOT EXISTS (
    SELECT 1 FROM Offers o2 
    WHERE o2.offer_volume = o1.offer_volume AND o2.offer_price < o1.offer_price
  )
) o
ON b.bid_volume = o.offer_volume AND b.bid_price >= o.offer_price
ORDER BY b.bid_price DESC;


bidder_user_id
dealer_user_id
bid_volume
offer_volume
bid_price
offer_price


1
8
100
100
300
200

4
6
60
60
100
100

5
7
75
75
90
85


SELECT  
    b.user_id AS bidder_user_id,
    o.user_id AS dealer_user_id,
    b.bid_volume,
    o.offer_volume,
    b.bid_price, 
    o.offer_price
FROM  Bids b 
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY offer_volume ORDER BY offer_price) rn 
  FROM Offers 
) o
ON b.bid_volume = o.offer_volume AND b.bid_price >= o.offer_price
WHERE o.rn = 1
ORDER BY b.bid_price DESC;


bidder_user_id
dealer_user_id
bid_volume
offer_volume
bid_price
offer_price


1
8
100
100
300
200

4
6
60
60
100
100

5
7
75
75
90
85