Cognizant Interview Question

Sql query for count and sub queries