Background Consider a hypothetical position where you work for a social network
ID: 3751233 • Letter: B
Question
Background
Consider a hypothetical position where you work for a social network company for academics. This social network includes full professors, assistant professors, postdoctoral students, graduate students and others as members. To increase engagement and interaction among the members of this academic community, your product manager wants to roll out a feature that ranks members in the network according to the number of times other people have viewed their social profiles. The product manager sketches out a general vision for the product, which would appear differently for each user:
Your rank for profile views
1.Jane Doe
2.John Doe
3.You
4.Jon Doe
5.Jan Doe
6....
Working as the data team’s point person on this project, you are responsible for pulling together and manipulating relevant data to advise the product manager on how data can inform the creation of this new feature. Central to these tasks are the tables in your company’s database that contain user information and profile view records. Here's a diagram and explanation of the database schema:
Prompt
Using the table names and relations shown in the schema diagram above, please write SQL queries designed to address the following questions (since the data are not in a database, we're just looking for the queries you would write):
a. What is the average age of users by region?
b. Can you generate a ranked list of the top 10 most viewed users based on internal profile views?
c. Which region gets the most viewed users based on internal profile views?
Explanation / Answer
a)select region as Region,Avg(age) as AverageAgeOfUsers
from users
group by region;
b)select * from users
where id IN (select viewed_user_id from internal_profile_views group by viewed_user_id order by count(*) Desc Limit 10);
c)select region from users
where id IN (select viewed_user_id from internal_profile_views group by viewed_user_id order by count(*) Desc Limit 1);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.