How can I remove identical (but negative) rows that are also in some days of each other from a SELECT on a table

Question

I have a MySQL table with two fields: date and value. date is of type date, and value is decimal(10,2).

There are "pairs" of rows (non-consecutive) whose values are of equal magnitude but opposite sign, within 7 days of each other.

date        value
...
2020-06-20  123.45
...
2020-06-25  -123.45
...

The above is a "pair" because they are negatives of each other and their dates are within 7 days of each other. There may be any number of intervening rows. There may be any number of pairs in the table. Pairs of differing values may overlap 7 day periods. For simplicity assume there are no other equal-value rows also with 7 days (ie, no triples).

I'm trying to find a SELECT statement that gives me all the table's rows EXCEPT "pairs". Could someone please help?

Answer 1

SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM TABLE t2
                   WHERE t2.`value` = -t1.`value`
                     AND t2.`date` BETWEEN t1.`date` - INTERVAL 7 DAY 
                                       AND t1.`date` + INTERVAL 7 DAY )