Postgres with Drizzle · Chapter 18 of 40
The aggregation engine
By the end of this chapter you can turn many rows into a few numbers: counts, totals and averages per group, only the groups that pass a test, and totals for a whole table. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Advanced Queries
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Rohan is building the analytics dashboard. He needs how many posts each member has written, the average likes of each member's posts, numbers per post type, a list of members with at least a given number of posts, and one tile with the platform's totals.
With what you know, you would fetch every post and tally them in JavaScript: half a million rows over the network to produce a few dozen numbers. Or you would run one count per member, thousands of queries. Postgres can sort the rows into piles and do the arithmetic itself, and send back only the answers.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getTotalPostsByUser: posts per member
Write getTotalPostsByUser(). It reads the posts table and returns one row per author, with how many posts that author has written.
Return an array of objects with userId (the posts' author_id) and totalPosts, a number (not a string). Any order: the check finds each member by userId. With the check's data, the three members have 4, 2 and 1 posts.
- Task 2
getAverageLikesPerUser: average likes per member
Write getAverageLikesPerUser(). It returns one row per author, with the average likes_count of that author's posts.
Return an array of objects with userId (the author_id) and avgLikes. avgLikes comes back as a string such as '19.0000000000000000', which is fine: the check reads it with Number. Any order.
- Task 3
getPostStatsByType: numbers per post type
Write getPostStatsByType(). It returns one row per post type (text, image or video, only the types that have posts), with how many posts have that type, the total of their likes_count and the average of their likes_count.
Return an array of objects with type, totalPosts (a number), totalLikes and avgLikes (both may be strings). Any order. With the check's data all five posts are text, so the text row has totalPosts 5.
- Task 4
getUsersWithMinPosts: members with at least N posts
Write getUsersWithMinPosts(minPosts). minPosts is a number. It returns the authors who have written at least minPosts posts: exactly minPosts counts.
Return an array of objects with userId (the author_id) and postCount, a number. Any order. With the check's data (5, 2 and 1 posts), minPosts 3 gives one author with postCount 5, and minPosts 2 gives two authors.
- Task 5
getOverallStats: the platform's totals
Write getOverallStats(). It returns the platform's totals: how many members there are (all of them, including members with no posts), how many posts, the total of all posts' likes_count, and the largest and smallest likes_count of any post.
Return one object, not an array, with totalUsers and totalPosts (both numbers), totalLikes, maxLikes and minLikes. With the check's data: 3 members and 5 posts.
What you will learn
- groupBy with count(): one answer row per pile of rows that share a value.
- sum, avg, min and max: arithmetic on each pile, and which of them come back as strings.
- having: keep only the piles that pass a test, after counting.
- Aggregates without groupBy: one row for the whole table.
More in Advanced Queries
Build The aggregation engine yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.