Posts beating their author's average
SQLHard13 min
Your task
Return published posts whose views exceed the average views of that author's published posts. Give title and views, ordered by title.
Tables
author| idINTEGER | nameTEXT | countryTEXT |
|---|
| 1 | Nia | KE |
| 2 | Oz | AU |
| 3 | Pia | BR |
| 4 | Quin | NULL |
post| idINTEGER | author_idINTEGER | titleTEXT | published_onTEXT | viewsINTEGER |
|---|
| 1 | 1 | Indexes explained | 2026-01-10 | 4200 |
| 2 | 1 | Query plans | 2026-02-02 | 1800 |
| 3 | 2 | Vacuum and bloat | 2026-02-11 | 900 |
| 4 | 3 | Draft on locks | NULL | 0 |
| 5 | 2 | Replication lag | 2026-03-01 | 5300 |
comment| idINTEGER | post_idINTEGER | author_idINTEGER | bodyTEXT |
|---|
| 1 | 1 | 2 | Useful |
| 2 | 1 | 3 | Thanks |
| 3 | 1 | 2 | One more thing |
| 4 | 3 | 1 | Good write-up |
| 5 | 5 | 3 | Saved me |
Model answerRun