I make a chat analyzer with statistics on used abusive words and the corresponding rating.
Table structures:
Messages: id is the unique ID of the message, from_id is the ID of the sender, date is the date of sending, text is the text.
Offensive:
id is the unique ID of the mat found in the message, type is a numeric value associated with the root of the obscene word, from_id is the sender's ID (the same as in Messages), in_message is the message ID in which this mat is used, text is the mat itself .
Next, I used the following query to build the rating of the matershinnikov:
SELECT from_id, COUNT(*) FROM `Offensive` GROUP BY from_id ORDER BY `COUNT(*)` DESC
However, cheaters began to write messages like “fucking fucking fucking fucking” and wind up their rating. I think that such cheating can be eliminated if you build a rating with a query that does not take into account more than a certain number of mats in the Nth period of time, but, unfortunately, I won’t use my mind how to do it ...
Thanks in advance for your help.
