Mihoyo interview question

Can you write some basic SQL code to do the operation.