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.

Even though I work primarily in Looker these days, I do spend a fair amount of time writing and reviewing SQL queries. Over time, I’ve experimented with and adopted various ways of styling my queries. Things like whether to use CTEs or subqueries (CTEs), single vs double quotes (single), how to format joins, and a lot more.

I’ve attempted to distill my preferences into a guide which you can check out on GitHub: Mazur’s SQL Style Guide.

Like with any style guide, you’ll have to consider which conventions you want to adopt. For example, you might write queries that use both single and double quotes and not give it a second thought, but feel strongly about columns always using snake_case and never camelCase. In the end, the key is readability, but opinions will differ on what’s readable and what’s not :).

For those of you writing a lot of SQL, hopefully you walk away from this guide with some things think about.

And like I mention in the guide, if you find yourself disagreeing strongly with any of my preferences, I’d love to chat. Please shoot me an email if do.

Last but not least, I have a Matt on Analytics newsletter where I’ll occasionally (very occasionally at the moment) share new content like this. If you’re interested in this kind of thing, you can sign up here.

A few months back I wrote about how Help Scout generates a Daily Metrics Report using Looker. I wound up giving a talk about this topic at JOIN, Looker’s annual user conference, and wanted to share the recording for anyone who wants to learn more.

Here’s the description:

How do you keep everyone in your organization informed about the performance of its key metrics when not everyone uses Looker regularly? In this session, we’ll talk about how to leverage Fivetran and Looker to build a daily metrics email report that will help educate end engage your entire team.

Huge thanks to Fivetran for inviting me to speak during their sponsor session and Looker for putting on the conference.