Posts With More Likes Than the Platform Average

Easy
430.

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 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

ColumnType
post_idint
author_handlevarchar
posted_ondate
like_countint

Primary key: post_id.

One row per post with its denormalised like counter.

Example

The rows must come back in the order shown.

posts

post_idauthor_handleposted_onlike_count
1aarav.eats2025-05-01120
2diya_travels2025-05-0140
3aarav.eats2025-05-0280
4kabir.codes2025-05-03NULL
5zara_ootd2025-05-03120
6meera_fit2025-05-0440

Output

post_idauthor_handlelike_count
1aarav.eats120
5zara_ootd120
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
post_idint
author_handlevarchar
posted_ondate
like_countint

Primary key: post_id.

One row per post with its denormalised like counter.

Examples

Example 1

posts

post_idauthor_handleposted_onlike_count
1aarav.eats2025-05-01120
2diya_travels2025-05-0140
3aarav.eats2025-05-0280
4kabir.codes2025-05-03NULL
5zara_ootd2025-05-03120
6meera_fit2025-05-0440

Output

post_idauthor_handlelike_count
1aarav.eats120
5zara_ootd120

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_id

Another 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_id

Another 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 →