How can I find a certain group of people based on a table which they are part of in SQL?-CodePudding

I have the following table in SQL:

CREATE TABLE `Trade` (
            Sender BINARY(20) NOT NULL,
            Receiver BINARY(20) NOT NULL,
            Price DECIMAL(36,0) NOT NULL,
            Item VARBINARY(32) NOT NULL,
        ) COLLATE='utf8_general_ci' ENGINE=InnoDB;

Based upon that table I want to identify traders that bought and sold the same item and then order them by the total profit they made. (In short, I want to find profitable resellers).

Should I group the trades by Sender or Receiver and then try to make further queries on that query?

With the following query I try to get all trades where a trader bought and sold the same item:

SELECT * FROM (SELECT * FROM Trade GROUP BY Receiver) AS r WHERE r.Receiver = Sender;

But this query does not return any values.

Any help would be highly appreciated!

CodePudding user response：

It seems that you want this:

WITH 
cte1 AS ( SELECT sender customer, item, SUM(price) price
          FROM trade
          GROUP BY 1,2 ),
cte2 AS ( SELECT receiver customer, item, SUM(price) price
          FROM trade
          GROUP BY 1,2 )
SELECT customer, item, cte2.price - cte1.price profit
FROM cte1
JOIN cte2 USING (customer, item)

Of course, there is a problem. For example, customer may buy 3 items but sell only 2 of them... if item is a name, not unique identifier, I do not see the way to solve.

CodePudding user response：

You could use a self join approach:

SELECT
    t1.Sender,
    t1.Receiver,
    t1.Price - t2.Price AS Diff
FROM Trade t1
INNER JOIN Trade t2
    ON t2.Sender = t1.Receiver AND
       t2.Receiver = t1.Sender
ORDER BY
    Diff DESC;

The output depends on who you view as the real "sender" in the mutual trading relationship. You may change the sorting order if you want to view it in the other direction.