Today I Learned

A Hashrocket project

First Postgres Queries: a Join and an Alias

To see some 2018 TIL stats, I learned to write some Postgres queries. One was:

select developers.username, count(*) from posts join developers on posts.developer_id = developers.id  where published_at >= '2018-01-01' and published_at < '2019-01-01' group by developers.username order by count(*) desc

This gave me the number of posts in 2018 associated with each username. I joined the posts table and the developers table with join developers on posts.developer_id = developers.id. That lines up the developer_id column in the posts table to the id column in the developers table. Now I can select the username column from the developers table: developers.username.

Next, I wanted to see the average number of likes per post for each developer. I do that by having the query evaluate the expression sum(likes)/count(*). I followed that with the phase as average. This creates an alias that can save some thinking and typing later in the query. The final query:

select developers.username, sum(likes)/count(*) as average from posts join developers on posts.developer_id = developers.id  where published_at >= '2018-01-01' and published_at < '2019-01-01' group by developers.username order by average desc

Congrats Taylor, Brian, and Jack for having the most loved posts of 2018!

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.