Apple Interview Question

CASE WHEN question Three tables join