Search code examples
mysqlsqlrecursive-query

Is it possible to do that using recursive CTE in SQL for this complicated scenario? Please suggest me any alternative way

My scenario:

enter image description here

  • User A is (fraudster).
  • User B is not (fraudster). However, the system will not allow user B to do any action. Because B and A are using the same Phone Number(Shared attribute with Fraud User).(1 layer).
  • User D is not (fraudster). But D is using the same Deviceid with B and B is sharing attribute with fraud User. Then block User D as well. In this case, there are 2 layers. D compare with B, B compares with A.

My code:

select ID,
       Email,
       MobileNo,
       DeviceId 
from   (select * from tableuser 
       order by ID, Email)  tableuser_sorted,
       (select @pv := '0122338737', @pc= 'DF1234') initialisation   
where  find_in_set(MobileNo,    @pv) 
and    length(@pv := concat(@pv, ',', ID)) 
OR find_in_set(DeviceId,       @pc) and length(@pc := concat(@pc,',', ID)

Output:

enter image description here

What I am looking for:

  1. If the User is a fraudster. I want to compare all the attributes with other Users.
  2. If there are any user sharing attributes with a fraudster user compare that user's attributes with the other users.

The flow will be like this:

User A is a fraudster. Okay, check User A attributes with other users. okay, I found B is sharing the Phone number with A. B will be in the second stage. ok now check User B attributes with other users. okay, I found D is sharing DeviceId with B and so on.

Solution

  • You can use a recursive CTE for this. If you only want fraudsters, something like this should work:

    with recursive cte as (
      select ID, Email, MobileNo, DeviceId, id as ids
      from tableuser
      where isfraudsterstatus = 1
      union all
      select u.id, u.email, u.mobileno, u.deviceid, concat_ws(',', cte.ids, u.id)
      from cte join
           tableuser u
           on u.email = cte.email or
              u.mobileno = cte.mobileno or
              u.deviceid = cte.deviceid
      where find_in_set(u.id, cte.ids) = 0
     )
select distinct id
from cte;