I haven’t made too many changes to how I write SQL recently, but I did adopt a new convention recently that I really like so wanted to share.

In the past, I would have written the following query like so:

select
  email,
  sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

Note that this does not prefix email or amount with the table name where they came from.

Claire Caroll of dbt fame recently pinged me to suggest a change: whenever there’s a join involved, you should include the table name to make it clear where the column originated. The query above would look like this:

select
  users.email,
  sum(charges.amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

When there’s no join involved, it’s fine to leave it out because there’s no room for confusion:

select
  id,
  name
from companies

I’ve been following this convention for a few weeks and really like it because there’s zero ambiguity when seeing this around where each column originated. It’s more verbose obviously, but I think the extra clarity outweighs that downside.

I’ve updated the style guide to reflect this guide.

I’d love to hear your thoughts – do you always include the name name, only when necessary, or follow a convention like this and only include it when joins are involved?

I recently had the pleasure of being interviewed by Simon Ouderkirk about my journey to becoming a data analyst and lessons learned along the way. You can check out a recording of the interview here:

This came about because Simon volunteered to start this interview series for the Locally Optimistic data community. And because Simon and I have worked closely together for a number of years (first when I was full time at Automattic and now as a consultant), he pinged me to see if I wanted to be a guinea pig for this first interview :).

If data and analytics interest you, I highly recommend checking out the Locally Optimistic blog and Slack community linked to above; you’ll walk away thinking a lot more deeply about data, metrics, and running an effective analytics organization. You can also follow Simon on Twitter to learn about future interviews in this series.