I have the following tables:
users (id, first_name, last_name)
category (id, name)
rank(id, user_id, rank)
Each user can belong to several categories. And all users are in the rank table and have a value between 0.0 and 1.0, where 0 is the lowest rank and 1 is the highest. I’d like to setup additional tables to create the following webpage:
A visitor to the page (identified by either one of the recorded ids in the user table, or a numeric representation of their ip address) chooses a category and is presented with two randomly chosen users from the users table such that:
1) the visiting user_id has not seen this pairing in a period of 24 hours
2) the two users belong to the chosen category
3) the two users are within 1 rank value of each other. Let me explain that last criteria - if all the ranks were sorted, the two chosen users would have adjacent ranks.
This is a hard one and I can’t for the life of me figure it out how to design my tables and query to answer this efficiently
I truly appreciate any help on this front.