Posts With More Likes Than the Platform Average
The content team wants posts that did better than average. A post's like_count is NULL while its counters are being rebuilt; such posts are left out of the…
- Difficulty: Easy
- Topics: Subqueries, Basics
- Dialect: MySQL
- Problem: #430
Problem statement
The content team wants posts that did better than average. A post's like_count is NULL while its counters are being rebuilt; such posts are left out of the average and never returned.
Return the columns post_id, author_handle and like_count for every post whose like_count is strictly greater than the average like_count of all posts, ordered by like_count descending, then by post_id.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| author_handle | varchar |
| posted_on | date |
| like_count | int |
Primary key: post_id.
One row per post with its denormalised like counter.
Examples
Example 1
posts
| post_id | author_handle | posted_on | like_count |
|---|---|---|---|
| 1 | aarav.eats | 2025-05-01 | 120 |
| 2 | diya_travels | 2025-05-01 | 40 |
| 3 | aarav.eats | 2025-05-02 | 80 |
| 4 | kabir.codes | 2025-05-03 | NULL |
| 5 | zara_ootd | 2025-05-03 | 120 |
| 6 | meera_fit | 2025-05-04 | 40 |
Output
| post_id | author_handle | like_count |
|---|---|---|
| 1 | aarav.eats | 120 |
| 5 | zara_ootd | 120 |
How to solve Posts With More Likes Than the Platform Average
The average is one value over the whole table, so compute it in a scalar subquery and compare each row with it: like_count > (SELECT AVG(like_count) FROM posts). The database evaluates the uncorrelated subquery once.
NULLs are handled by the aggregates themselves: AVG skips them, so posts with a rebuilding counter do not drag the average down, and NULL > x is unknown, so they never pass the outer filter either.
Comparing with an average invites precision questions. The third query avoids division altogether — like_count × n > total is the same inequality multiplied through by the count of non-NULL posts, all in integers. A derived table cross-joined to the posts is another way to bring the single average next to every row. Equal like counts are ordered by post_id to keep the result fixed.
Reference solution (MySQL)
SELECT post_id, author_handle, like_count
FROM posts
WHERE like_count > (SELECT AVG(like_count) FROM posts)
ORDER BY like_count DESC, post_idAnother way
SELECT p.post_id, p.author_handle, p.like_count FROM posts p CROSS JOIN (SELECT AVG(like_count) AS avg_likes FROM posts) a WHERE p.like_count > a.avg_likes ORDER BY p.like_count DESC, p.post_idAnother way
SELECT post_id, author_handle, like_count FROM posts WHERE like_count * (SELECT COUNT(like_count) FROM posts) > (SELECT SUM(like_count) FROM posts) ORDER BY like_count DESC, post_id← Label Accounts Active, Dormant or Churned · Mutual Follows Between Active Accounts →