I have created the following query:
WITH cte1 AS(SELECT Sender trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3), cte2 AS(SELECT Receiver trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3) SELECT trader, cte2.price - cte1.price profit FROM cte1 JOIN cte2 USING(trader, TokenAddress, TokenId) WHERE cte2.price - cte1.price > 1000000000000000000 ORDER BY profit LIMIT 5000
It works during development but after testing it on the production server (which is uses MySQL 5.7) I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘cte1 AS(SELECT Sender trader, TokenAddress, TokenId, SUM(Price) Price ‘ at line 2
I have a hard time figuring out the cause, any help would be greatly appreciated.
Advertisement
Answer
This way it will run on MySql 5.7:
SELECT trader, cte2.price - cte1.price profit FROM (SELECT Sender trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3) AS cte1 JOIN (SELECT Receiver trader, TokenAddress, TokenId, SUM(Price) price FROM OpenSeaTrade GROUP BY 1, 2, 3) AS cte2 USING(trader, TokenAddress, TokenId) WHERE cte2.price - cte1.price > 1000000000000000000 ORDER BY profit LIMIT 5000;