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.

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

LikeLike

Good to know about this “sys.dm_db_partition_stats”. New to me. Further Q1 is very simple and easy to understand.

LikeLike

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

LikeLiked by 1 person

great answer!

good interview question as well.

LikeLike