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!