Google Interview Question

count occurance joins and subquery