Schema:: Student_id, depart_id, coach_id, date Find AVERAGE number of students per department in March 2021 Is window function needed here?? I know a group by depart_id is used and i can count number of students by using count (*) but how do I calculate average? Edit: one student could belong to multiple departments Thanks in advance. #sql #sqlinterview
Given it’s an average number of students per department, I assume the semantic is: Total # of students / Total # of departments count distinct student_id / count distinct depart_id E.g. 90 students exist. 3 department exist. Avg of 30 student per department ???
I checked and the question says, 1 student could be enrolled with multiple departments
Ok, so if you want to double or triple etc count the students based on dep (non unique): SEL sum(num_stud) / count distinct dep_id FROM ( SEL dep_id , count distinct stud_id as num_stud FROM table group by 1 )
Just use CTEs. Makes it pretty simple. Or can also simply count number of students by departments
First step is to find the number of students for each department. Then you can avg that number. The count(distinct) functionality removes the possibility of duplicates. Let me know if you have any questions! with march_data as ( select depart_id, count(distinct student_id) as total_students from table_name where date between '2021-03-01' and '2021-03-31' group by 1 ) select avg(total_students) as avg_students_per_dept from march_data
Why do you even have to do all this shit with extra table? Isn’t the average just count of unique enrolments divided by number of departments.
What is the 1 in the group by
DROP TABLES
Little Bobby? Is that you?
Lmao
Blind had to send notification for this?🤦🏼♂️😒
seriously
Fuck blind
Google AVG() in SQL
You failed Edit: Avg() directly will not work here as it sums the numbers and averages them. So you need to get the count per department in an inner sql and then use Avg() in outer sql as the guy 3 posts above did
Thanks Amazon. Now shut the F up and send me my packages I ordered 5 seconds ago
Stack Overflow
This is such an easy question omg
Seriously
Now this is the kind of quality content I want to see on blind!
World Conflicts
Yesterday
688
American police seem to work only when Israel is challenged
India
7h
248
Voted for dictatorship. Voted for modi. F*CK Congress.
Tech Industry
12h
2703
Asians - what are your thoughts on asian female white male ?
Pets
Yesterday
896
Cat killed bird
World Conflicts
Yesterday
1111
I am Columbia alumni
To me average makes no sense here because you already have the students and their departments and so you have exact counts. You'd have to ask what is the definition of average no. Of students per department in this case.
Wording is confusing but pretty sure I know what op meant
I checked and the question says, 1 student could be enrolled with multiple departments