Some BigQuery SQL tips and tricks I’ve picked up

Photo by Caspar Camille Rubin on Unsplash

As a data analyst, using SQL to query and extract data is one of the core aspects of my role. So I’d like to share some tips and tricks in SQL that I have learned over time that I have found helpful, and I hope this helps you if you’re also using BigQuery!

1. USING: Did you know that you can use this clause when you do your SQL joins, provided that the primary and foreign keys have the same name? This saved me some time from writing the condition(s) for joining different tables. Say for example I have 2 tables, Person and Address where the primary key for Person is personId and the foreign key for Address is also personId.

SELECT 
  p.firstName,
  p.lastName,
  a.city,
  a.state
FROM Person p
LEFT JOIN Address a
ON p.personId = a.personId

SELECT
  p.firstName,
  p.lastName,
  a.city,
  a.state
FROM Person p
LEFT JOIN Address a
USING(personId)

Both methods of joining the tables work, but the USING clause saves some time typing out the join conditions.

2. QUALIFY: This clause makes it easier to filter the results of a window function. Imagine, for example, if I ranked a list of students by their exam scores, and I wanted to find out which students were ranked first. Without learning the QUALIFY clause, I would have written a SQL query like the below, by creating a common table expression named exam_scores, and writing a window function to create a column named grade_rank before filtering in my final SELECT statement by grade_rank = 1.

WITH exam_scores AS (
SELECT
  student_name,
  grade,
  RANK() OVER (ORDER BY grade DESC) AS grade_rank
FROM exams 
)

SELECT
  student_name
FROM exam_scores
WHERE
  grade_rank = 1

SELECT
  student_name
FROM exams
QUALIFY RANK() OVER (ORDER BY grade DESC) = 1

Looking at the syntax, you will realize that if you use the QUALIFY clause, it saves you a lot of lines of SQL queries, and you don’t even have to create a common table expression to rank the grades of the students!

3. PIVOT: Imagine that you have a sales dataset like the one below and you need to transpose this table so that products become columns and months remain rows.

Your SQL query might look somewhat like the below, with a series of CASE WHEN clauses:

SELECT
    month,
    SUM(CASE WHEN product = 'A' THEN revenue ELSE 0 END) AS A_revenue,
    SUM(CASE WHEN product = 'B' THEN revenue ELSE 0 END) AS B_revenue
FROM
    sales
GROUP BY
    month;

What if I told you that you could do this using a single PIVOT clause?

SELECT *
FROM sales 
PIVOT (
  SUM(revenue) AS revenue FOR product IN ('A', 'B')
)

The expected output for the PIVOT clause should look like the above screenshot. The PIVOT clause automatically uses revenue as the prefix and the product names as the suffix to name the transposed columns. As we can see in the example above, PIVOT saves the need to write multiple CASE WHEN clauses when you are looking at transposing rows into columns.

These are a small part of some tips and tricks I have picked up along my data analytics journey. I hope to be able to share more next time!