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.

Shoot your comments or queries.

Thank You.

Rate

Share

Share

Rate