Comment count per post
SQLMedium10 min
Your task
Return every post with how many comments it has. Give title and comments, most commented first, then by title. Posts with none show 0.
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