Amazon interview question

Sql codes explanation around cases