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
Want to see the real deal?
More inside scoop? View in App
More inside scoop? View in App
blind
SUPPORT
FOLLOW US
DOWNLOAD THE APP:
FOLLOWING
Industries
Job Groups
- Software Engineering
- Product Management
- Information Technology
- Data Science & Analytics
- Management Consulting
- Hardware Engineering
- Design
- Sales
- Security
- Investment Banking & Sell Side
- Marketing
- Private Equity & Buy Side
- Corporate Finance
- Supply Chain
- Business Development
- Human Resources
- Operations
- Legal
- Admin
- Customer Service
- Communications
Return to Office
Work From Home
COVID-19
Layoffs
Investments & Money
Work Visa
Housing
Referrals
Job Openings
Startups
Office Life
Mental Health
HR Issues
Blockchain & Crypto
Fitness & Nutrition
Travel
Health Care & Insurance
Tax
Hobbies & Entertainment
Working Parents
Food & Dining
IPO
Side Jobs
Show more
SUPPORT
FOLLOW US
DOWNLOAD THE APP:
comments
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
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
???