Find Followers Count II
ASKED IN INTERVIEWTwitter
Problem Statement
Twitter product managers want to see which account categories are growing most effectively.
Write an SQL query to find the average number of followers for each account category.
Rules:
- You must first determine the follower count for every user who has at least one follower.
- Then, calculate the average of those counts per category.
- Output columns: category, avg_followers.
- Round the average to 2 decimal places.
- Results must be sorted by category in ascending order.
Tests your understanding of
Joins, Aggregation and Rounding
Input Tables
Followers
| user_id(INTEGER) | follower_id(INTEGER) |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
| 3 | 2 |
| 3 | 4 |
Users
| user_id(INTEGER) | category(VARCHAR) |
|---|---|
| 1 | Business |
| 2 | Personal |
| 3 | Business |
Account_Creation
| user_id(INTEGER) | creation_date(DATE) | verified_status(BOOLEAN) |
|---|---|---|
| 1 | 2020-01-01 | true |
| 2 | 2021-05-12 | false |
Expected Output
| category(VARCHAR) | avg_followers(DECIMAL) |
|---|---|
| Business | 2.5 |
| Personal | 1 |
Tags
EasyASKED IN INTERVIEWJoinsAggregationRounding
10-15 min
71%