Geveo Australasia interview question

Provided a scenario and asked me to write the SQL query