users:id(PK),rank_id(FK),name
ranks:id(PK),name,annual_fee,min_purchases,discount_percentage
user_ranks:id(PK),rank_id(FK),acquired_date, expiration_date

■会員のランクを取得
SELECT users.name,ranks.name FROM users INNER JOIN ranks ON users.rank_id=ranks.id

■会員ランクが有効なユーザーを取得
SELECT users.name FROM user_ranks INNER JOIN users ON user_ranks.user_id=users.id WHERE acquired_date <= '2023-05-22' AND expiration_date >= '2023-05-22'

■特定の会員（user_id:1）への特典を取得
SELECT ranks.discount_percentage FROM ranks INNER JOIN users ON ranks.id=users.rank_id WHERE rand_id='1'

■そのランク（rank_id:1）になる条件を取得
SELECT annual_fee,min_purchases FROM ranks WHERE rand_id='1'

これで良くないか？SQLの実行数も少ないし、わかりやすいと思うんだが。
user_ranksはログみたいな扱いにして、最新のレコードをinsertした後に
usersのrank_idをupdateすればJOIN回数も減るし、サブクエリも必要ない。