how to create fraud detection with SQL?

I have a scenario where:

• 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. In this sample example there are 3 users. if I have 100 users! the query will be very long which will have 99 layers.

*Note: I do not want to Update B and D to be a fraudster. they are just shared attributes with fraud users. So If I decided to change user A to be not a fraudster. No changes with the other users.

Answer

To be honest Question is pretty abstract, so need to make few assumptions as follows,

1. Assuming database server as MySQL
2. Considering this that data of fraudster accounts is already present in table.
3. The duplication of fields MobileNo OR DeviceId OR EmailId OR IPAddress decides fradster.

So to answer your question,

1. Create a AfterUpdate trigger on your table.
2. In that trigger Fetch and Update rows those having duplicate value for either MobileNo OR DeviceId OR EmailId OR IPAddress.

Fire Update query only if, NEW.IsFraudsterStatus = 1,

if (NEW.IsFraudsterStatus = 1) THEN
     UPDATE tableUser
     SET IsFraudsterStatus = 1
     WHERE 
          (tableUser.MobileNo = NEW.MobileNo
          OR
          tableUser.DeviceId = NEW.DeviceId
          OR
          tableUser.EmailId = NEW.EmailId
          OR
          tableUser.IPAddress = NEW.IPAddress)
         AND
          IsFraudsterStatus = 0; 
ELSE
     UPDATE tableUser
     SET IsFraudsterStatus = 0
     WHERE 
          (tableUser.MobileNo = NEW.MobileNo
          OR
          tableUser.DeviceId = NEW.DeviceId
          OR
          tableUser.EmailId = NEW.EmailId
          OR
          tableUser.IPAddress = NEW.IPAddress)
         AND
          IsFraudsterStatus = 1;
END IF;

In above query you may add as much as conditions you need, please note by ORing them so that account is fraudster if any of the condition is true.

3. Once above query is fired it will repeatedly update all such accounts as Fraudster. And Please note the ANDed condition it will prevent endless recursive triggering.

And besides this I would recommend a BeforeInsert trigger, that will validate and restrict fraudster account getting created.