My Python 3.6 script to detect stamp card redeem fraud and prepare a csv report
The description of the problem in the post says that the case to be detected is redeeming "over 3 time" but what the code actually detects is 3 times or more. Which is right?
The description of the problem in the post says that the case to be detected is redeeming:


  on same stamp card within 10 mins


But what the code actually detects is redeeming:


  on same stamp card by the same user within 10 mins


Which of these is right? It seems to me that the description in the post would be better, because otherwise someone could avoid detection by switching to a new user account after redeeming twice. (Maybe the software ensures that each stamp card is redeemable only by one user, but in that case why check the user at all?)
The code assumes that the database will return records for each same stamp card id in increasing stamp time order, otherwise this won't work:

g.tail(1).stamp_time.values - g.head(1).stamp_time.values


But the SQL query only orders them by stamp card id, not by stamp time. Perhaps it happens to be the case at the moment that records are added to the table in stamp time order. But it is a bad idea to depend on this, because a change to the way the database is updated could break this assumption. It would be more robust to be explicit and write:

ORDER BY stamp_card_id, stamp_time

Here's what I suspect is the main performance problem:

for i in range(0, len(unique_users)):
    user = unique_users[i]
    new_df = df[df.user_id == user]


The expression df[df.user_id == user] looks innocent, but actually it has to loop over all the records in the dataframe twice: once to test user_id == user, and a second time to select the matching records. And this has to be done for every user. So if there are 36,000 records and (say) 10,000 unique users, then the user_id == user test gets run 360,000,000 times!

Instead of iterating over the unique users and then finding the records for each user, use Panda's groupby method to efficiently group the records by user.

But looking at my point §2 above, is this step really necessary? Perhaps what we really need to do is to ignore the users altogether and go straight to the stamp card ids.
The same anti-pattern occurs in the stamp card processing code:

sid = new_df.stamp_card_id.unique()
for i in sid:
    fdf = new_df[new_df.stamp_card_id == i]


This needs to become:

for fdf in df.groupby(['stamp_card_id']):


If you really do care about the users as well as the stamp cards, then you should order the query results by both:

ORDER BY user_id, stamp_card_id, stamp_time


and group by both:

for fdf in df.groupby(['user_id', 'stamp_card_id']):

The code creates a multiprocessing pool, but does not use it! You have to call the pool's methods, for example multiprocessing.Pool.apply, to take advantage of the pool—it doesn't work by magic.

But actually I suspect that there's no need to use multiprocessing here. Once you have eliminated the quadratic runtime behaviour as discussed above, then it should run in an acceptable amount of time in a single process.