Clash Royale CLAN TAG#URR8PPP

Postgres query issue with price matching with two tables

I have two tables, one is buy table and second is sell table.

Buy Table

id price qty in_status dt_added_at
1 10 15 1 2018-08-10 12:00:00+05:30
2 10 20 1 2018-08-10 13:00:00+05:30
3 12 15 1 2018-08-10 14:00:00+05:30
4 15 20 1 2018-08-10 15:00:00+05:30


Sell Table

id price qty in_status dt_added_at
1 15 20 1 2018-08-10 12:00:00+05:30
2 12 15 1 2018-08-10 13:00:00+05:30
3 10 15 1 2018-08-10 14:00:00+05:30
4 10 20 1 2018-08-10 15:00:00+05:30
5 10 15 1 2018-08-10 16:00:00+05:30


Applied query of price matching table

SELECT buy.*, sell.* FROM buy
LEFT JOIN sell ON
buy.price = sell.price AND buy.qty = sell.qty
WHERE buy.price = 10 AND buy.in_status = 1 AND sell.price = 10 AND
sell.in_status = 1
GROUP BY buy.id, sell.id, buy.dt_added_at, sell.dt_added_at
ORDER BY buy.id, sell.id


Actual Output

id price qty in_status dt_added_at id price qty in_status dt_added_at
1 10 15 1 2018-08-10 12:00:00+05:30 3 10 15 1 2018-08-10 14:00:00+05:30
1 10 15 1 2018-08-10 12:00:00+05:30 5 10 15 1 2018-08-10 16:00:00+05:30
2 10 20 1 2018-08-10 13:00:00+05:30 4 10 20 1 2018-08-10 15:00:00+05:30


So here the 1st record of buy table is matched with the 3rd and 5th record of sell table but the 5th record should get omit because I need to implement FIFO and one buy record matched with one sell entry only and 2nd record of buy table is matched with the 4th record.

Expected Output

id price qty in_status dt_added_at id price qty in_status dt_added_at
1 10 15 1 2018-08-10 12:00:00+05:30 3 10 15 1 2018-08-10 14:00:00+05:30
2 10 20 1 2018-08-10 13:00:00+05:30 4 10 20 1 2018-08-10 15:00:00+05:30


You can see in below image.

Edited at 18thAug2018 - Tried with this data and it not works
As the entry of sell table is repeated.

CREATE TABLE Buy(
id int,
price int,
qty int,
in_status int,
dt_added_at timestamp
);

insert into Buy values (1,10,15,1,'2018-08-10 12:00:00+05:30');
insert into Buy values (2,10,20,1,'2018-08-10 13:00:00+05:30');
insert into Buy values (3,10,15,1,'2018-08-10 14:00:00+05:30');
insert into Buy values (4,10,20,1,'2018-08-10 15:00:00+05:30');

CREATE TABLE sell(
id int,
price int,
qty int,
in_status int,
dt_added_at timestamp
);

insert into sell values (1,15,20,1 ,'2018-08-10 12:00:00+05:30');
insert into sell values (2,12,15,1 ,'2018-08-10 13:00:00+05:30');
insert into sell values (3,10,15,1 ,'2018-08-10 14:00:00+05:30');
insert into sell values (4,10,20,1 ,'2018-08-10 15:00:00+05:30');
insert into sell values (5,10,15,1 ,'2018-08-10 16:00:00+05:30');
insert into sell values (6,10,20,1 ,'2018-08-10 17:00:00+05:30');


3 Answers
3

You can try to write a subquery with Row_number with window function to make row_number by price and qty, then you can get rn = 1 means the earlier
buy time by dt_added_at.

window function

price

qty

rn = 1

dt_added_at

CREATE TABLE Buy(
id int,
price int,
qty int,
in_status int,
dt_added_at timestamp
);


insert into Buy values (1,10,15,1,'2018-08-10 12:00:00+05:30');
insert into Buy values (2,10,20,1,'2018-08-10 13:00:00+05:30');
insert into Buy values (3,12,15,1,'2018-08-10 14:00:00+05:30');
insert into Buy values (4,15,20,1,'2018-08-10 15:00:00+05:30');

CREATE TABLE sell(
id int,
price int,
qty int,
in_status int,
dt_added_at timestamp
);


insert into sell values (1,15,20,1 ,'2018-08-10 12:00:00+05:30');
insert into sell values (2,12,15,1 ,'2018-08-10 13:00:00+05:30');
insert into sell values (3,10,15,1 ,'2018-08-10 14:00:00+05:30');
insert into sell values (4,10,20,1 ,'2018-08-10 15:00:00+05:30');
insert into sell values (5,10,15,1 ,'2018-08-10 16:00:00+05:30');


Query 1:

SELECT buy.*, s.*
FROM buy
LEFT JOIN (
select s.*,Row_number() over(PARTITION BY price,qty ORDER BY dt_added_at) rn
from sell s
) s ON
buy.price = s.price AND buy.qty = s.qty and rn = 1
WHERE
buy.price = 10
AND
buy.in_status = 1
AND
s.price = 10
AND
s.in_status = 1


Results:

| id | price | qty | in_status | dt_added_at | id | price | qty | in_status | dt_added_at | rn |
|----|-------|-----|-----------|----------------------|----|-------|-----|-----------|----------------------|----|
| 1 | 10 | 15 | 1 | 2018-08-10T12:00:00Z | 3 | 10 | 15 | 1 | 2018-08-10T14:00:00Z | 1 |
| 2 | 10 | 20 | 1 | 2018-08-10T13:00:00Z | 4 | 10 | 20 | 1 | 2018-08-10T15:00:00Z | 1 |


rn=1

I would use a DISTINCT ON

DISTINCT ON

SELECT DISTINCT ON ( s.price, s.qty) b.*,
s.*
FROM buy b
LEFT JOIN sell s
ON b.price = s.price
AND b.qty = s.qty
WHERE b.price = 10
AND b.in_status = 1
AND s.price = 10
AND s.in_status = 1
ORDER BY s.price,
s.qty,
s.dt_added_at


Demo

Aggregate function min() help to you to pick FIFO based data

select t.*,s1.* from
(
select b.id,b.dt_added_at,
s.price,s.qty, min(s.dt_added_at) as sdt_added_at from
Buy b inner join
sell s on
b.price = s.price AND b.qty = s.qty
where s.in_status = 1 and b.price=10
group by s.price,s.qty,b.id,b.dt_added_at
) t
left join sell s1 on t.sdt_added_at =s1.dt_added_at


http://sqlfiddle.com/#!17/b543e3/9

id dt_added_at price qty sdt_added_at id price qty in_status dt_added_at
1 2018-08-10T12:00:00Z 10 15 2018-08-10T14:00:00Z 3 10 15 1 2018-08-10T14:00:00Z
2 2018-08-10T13:00:00Z 10 20 2018-08-10T15:00:00Z 4 10 20 1 2018-08-10T15:00:00Z


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.