How to find duplicate records in a row by attribute?

Question

SQL

Good afternoon,
There is such a table structure orders

1. id
   
2. user_id
   
3. product_id
   

Entries:

id        user_id    product_id
1          1             2
2          2             2
3          2             3
4          2             4
5          1             2
6          1             5
7          1             5
8          1             5

We want to select only posts where previous posts with the same user_id have an equivalent product_id .
Those. in this case, they will be pulled out only with id 7 and 8

Answer 1

Something like this ... unfortunately there is no way to check) But the meaning is this.

select * from your_table
INNER JOIN   (
      SELECT user_id, product_id
     FROM your_table
     GROUP BY user_id, product_id
    HAVING COUNT(id) > 1
) as m ON m.user_id = your_table.user_id AND m.product_id = your_table.product_id

Answer 2

More or less like this:

SELECT  * FROM 
(
select 
  ID
, LEAD(id) OVER (ORDER BY user_id)  lead_id
from your_table
) tab
WHERE tab.id= tab.lead_id