 # Interview Query – 1

,

Somewhere on internet i found this question. Thought to share it with you all.

We have two tables,  school_students and Class_student.

 Table : School_Students StudentID 1 2 3 4 5 6 7 8 9 10

Class_Student.

 StudentID ClassName 1 First 2 First 3 Second 4 Second 5 Second 6 Third 7 Third 8 Third 9 Third 10 Third

Find the total number of student in school  and the proportion of the class (In respect of school population).

Result Should be like this

 classname total_school_population Class_Proportion_In_% First 10 20 Second 10 30 Third 10 50

Above implies. Class 3rd hold the 50% of the students.

Solution :

There are two ways of doing it.

First, A simple nested SELECT.

SELECT

t.classname,

(SELECT COUNT (1) FROM school_students) as total_school_population,

COUNT (1) / (SELECT CAST(COUNT (1) as decimal(10,3)) FROM school_students) * 100 As [Class_Proportion_In_%]

FROM class_students t

group by t.classname

Second solution is much more optimize way of doing this.

SELECT

t.classname,

t1.cnt as total_school_population,

(COUNT(1)/ t1.cnt) * 100 As [Class_Proportion_In_%]

FROM

class_students t

CROSS JOIN

(

SELECT CAST(COUNT(1) as Decimal(10,3)) as cnt

from school_students

) t1

group by t.classname,t1.cnt

On any given day, i will choose second over first.

It is upto reader to identify why second is more optimized of doing the same.