Your task

Save your queries in data/queries/sql/06-counts.sql. This uses the same sample data as Level 5.

1. Admin dashboard. How many users are there? How many published posts?

2. Author leaderboard. Number of published posts per username, highest first.

3. Popular posts. The 3 published posts with the most likes: title and like count.

4. Growth email. Usernames of users who have never written a post.

5. Profile page. Every user with their follower count, including users with zero followers. Highest first.

6. Popular tags. Tags used on at least 3 posts, with how many.

7. Post card. For posts 1 and 3, show the like count and the comment count side by side. Write it the obvious way first (join both tables, count both), and work out why the numbers are wrong. To check: run SELECT count(*) FROM likes WHERE post_id = 1, the same for comments, and compare both with the number of rows your joined query produces for post 1 (drop the GROUP BY to see them). Then fix it.

8. Lurkers. Usernames of users who have never commented.