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.

4 thoughts on “Interview Query – 1

  1. Ben says:

    you could use sys.dm_db_partition_stats to speed it up further by avoiding the count(1) table scan…

    but more importantly you always have to factor in readability. if by keeping it simpler (and slower) you save time and effort in the long run (when other developers have to look at it) then query one is just as valid and maybe even preferable.

    cheers

    Like

  2. Andrii says:

    select distinct ClassName, c2, cast((c1+0.0)/c2*100 as int)
    from
    (
    select ClassName, count(*) over (partition by ClassName)c1, count(*) over () c2
    from Class_Student
    )t

    Liked by 1 person

Shoot Your Comments!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s