sql chat

select   * from `mafia1.chat` where player_id in ( select player_id from(SELECT player_id ,max(after_lev) as mansion_level FROM `heidao-market.mafia1.building_levelup` WHERE DATE(timestamp) > "2019-08-01" and  building_id=101 group by player_id) where mansion_level>5 and mansion_level<10 and player_id in
  ( 
  select player_id from  (SELECT player_id ,max(new_lev) as lord_level FROM `heidao-market.mafia1.level_up` WHERE DATE(timestamp) > "2019-08-01" group by player_id  ) where (lord_level>3 and lord_level<7 )
  )
  ) and  date(timestamp)='2019-09-09'