SQL SELECT顶行和聚合计数

I can't wrap my head around this. I have a client-staff communication system. On the admin side, I'm trying to retrieve a list of users, which contains total number of messages, total new messages, last message date, last message for that user.I can't wrap my head around this. I have a clie