Appier interview question

SQL medium question (aggregation related)