I am trying to write an SQL statement that uses an INNER JOIN to match bids and offers to each other.
I have a bids table which looks like the following
+-------------------------+-------------------+--------------------------------+--------------------------------+
| bid_id | user_id | bid_volume | bid_price |
+-------------------------+-------------------+--------------------------------+--------------------------------+
| 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 |
+-------------------------+-------------------+--------------------------------+--------------------------------+
I have an offers table which looks like the following
+---------------------------+-------------------+----------------------------------+----------------------------------+
| offer_id | user_id | offer_volume | offer_price |
+---------------------------+-------------------+----------------------------------+----------------------------------+
| 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 |
+---------------------------+-------------------+----------------------------------+----------------------------------+
I am trying to match bids to offers, there should be a one to one relationship i.e only one bid can match with one offer. The highest bid should also be the winner in the case if two or more parties are bidding.
A bid should be a winning bid if the bid_volume matches the offer_volume, the bid_price >= offer_price AND it is the highest bid_price of all the bids.
I am ordering the bids by bid_price high to low so the highest bid is processed first. The problem occurs when one bid matches multiple offers.
Taking the data above bid 101 will satisfy offer 93 and 95. It should really only match with offer 93 as it is the lower price.
The start of my SQL is below but it is limiting the matches I am really stuck on so there is a one to one relationship of bids to offers.
SELECT
Bids.user_id AS bidder_user_id,
Offers.user_id AS dealer_user_id,
Bids.bid_volume,
Offers.offer_volume,
Bids.bid_price,
Offers.offer_price
FROM
Bids
INNER JOIN
Offers
ON
/* Volume - Volume must be equal*/
Bids.bid_volume =
Offers.offer_volume AND
/* Price - Bid price greater than or equal to offer price*/
Bids.bid_price >=
Offers.offer_price
ORDER BY
bid_price DESC;
The output of the above is shown below (Notice there are two matches for bidder_user_id = 1 trying to get only one)
+----------------+----------------+------------+--------------+-----------+-------------+
| bidder_user_id | dealer_user_id | bid_volume | offer_volume | bid_price | offer_price |
+----------------+----------------+------------+--------------+-----------+-------------+
| 1 | 8 | 100.0000 | 100.0000 | 300.0000 | 200.0000 |
| 1 | 10 | 100.0000 | 100.0000 | 300.0000 | 300.0000 |
| 4 | 6 | 60.0000 | 60.0000 | 100.0000 | 100.0000 |
| 5 | 7 | 75.0000 | 75.0000 | 90.0000 | 85.0000 |
+----------------+----------------+------------+--------------+-----------+-------------+
The desired output would be as shown below, so user 1 bid is not matched with two offers but I am not sure how to limit the inner join to only match on first occurrence only.
+----------------+----------------+------------+--------------+-----------+-------------+
| bidder_user_id | dealer_user_id | bid_volume | offer_volume | bid_price | offer_price |
+----------------+----------------+------------+--------------+-----------+-------------+
| 1 | 8 | 100.0000 | 100.0000 | 300.0000 | 200.0000 |
| 4 | 6 | 60.0000 | 60.0000 | 100.0000 | 100.0000 |
| 5 | 7 | 75.0000 | 75.0000 | 90.0000 | 85.0000 |
+----------------+----------------+------------+--------------+-----------+-------------+
If your version of MySql is 8.0+ you can use ROW_NUMBER() window function in the table Offers to get the row with the lowest offer_price for each offer_volume and then join to Bids:
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;
For previous versions, you could use NOT EXISTS:
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;
See the demo.