User Engagement Analysis
For every user, how many posts, comments, and likes have they made — and what is their combined total engagement score?
- Joins
- Aggregation
- Sorting
- Distinct values
Challenge brief
Understand the request
Product Analytics wants a full-picture engagement score for every user to feed the algorithmic ranking model.
Calculate total engagement (posts + comments + likes) for every user including those with zero activity.
Return
- username
- total_posts
- total_comments
- total_likes_given
- total_engagement
Constraints
- Include every user, including users with no activity
- Count each post, comment, and like once even when a user has activity on multiple surfaces
- Show the highest combined engagement first and resolve ties by username
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
users
user_idINTEGERusernameVARCHAR(50)
posts
post_idINTEGERuser_idINTEGER
comments
comment_idINTEGERuser_idINTEGER
likes
like_idINTEGERuser_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Three separate one-to-many relationships hang off users: posts, comments, likes. LEFT JOIN all three independently. Use COUNT(DISTINCT ...) on each to prevent row multiplication from the fan-out.
Hint 2
LEFT JOIN users to posts, comments, and likes in separate joins. GROUP BY user. COUNT(DISTINCT p.post_id), COUNT(DISTINCT c.comment_id), COUNT(DISTINCT l.like_id). Total = sum of the three.
Hint 3
Start with the tables that establish the result grain for question 7, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT u.username, COUNT(DISTINCT p.post_id) AS total_posts, COUNT(DISTINCT c.comment_id) AS total_comments, COUNT(DISTINCT l.like_id) AS total_likes_given, COUNT(DISTINCT p.post_id) + COUNT(DISTINCT c.comment_id) + COUNT(DISTINCT l.like_id) AS total_engagement FROM users u LEFT JOIN posts p ON u.user_id = p.user_id LEFT JOIN comments c ON u.user_id = c.user_id LEFT JOIN likes l ON u.user_id = l.user_id GROUP BY u.user_id, u.username ORDER BY total_engagement DESC, u.username ASC;Why this works
The independent one-to-many joins can multiply rows, so distinct identifiers protect each activity count. Starting from users preserves zero-activity accounts.
Success check
5 users — jane_smith leads (5 total), alex_brown shows as all zeros
Expected result
Use this output to verify values, aliases, ordering, and row count.
| username | total_posts | total_comments | total_likes_given | total_engagement |
|---|---|---|---|---|
| jane_smith | 2 | 1 | 2 | 5 |
| john_doe | 2 | 1 | 1 | 4 |
| mike_wilson | 1 | 1 | 1 | 3 |
| sarah_jones | 1 | 1 | 1 | 3 |
| alex_brown | 0 | 0 | 0 | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Return to the complete interview preparation experience.