Meta Interview Question

SQL queries with basic group by, self joins and inner queries. The problem could be solved by analytical queries