Blog Post

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.

 

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

 

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

 

Result Should be like this

classnametotal_school_populationClass_Proportion_In_%
First1020
Second1030
Third1050

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