You are given a table of posts and a table of post shares. Find the average number of shares per post for each user. Include users who have posts but received no shares, showing 0 for them. Return user_id and avg_shares_per_post rounded to 2 decimal places, ordered by user_id.
Schema
user_posts
post_idINTEGER
user_idINTEGER
contentVARCHAR
post_dateDATE
post_shares
share_idINTEGER
post_idINTEGER
shared_by_user_idINTEGER
shared_atTIMESTAMP
Target output
Target output
| user_id | avg_shares_per_post |
|---|
| 101 | 1.50 |
| 102 | 0.00 |
| 103 | 0.00 |
| 104 | 2.00 |
| 105 | 0.00 |
What this question tests
Tests join direction and NULL arithmetic: keeping users whose posts earned no shares requires a LEFT JOIN and a zero fallback, and the denominator must be posts, not shares. That denominator is the trap most candidates hit.
More SQL interview questions
