16.11. Working with Data in BigQuery with SQL

In this lesson, we will learn how to work with data in BigQuery using SQL.

Why SQL for BigQuery?

Think of BigQuery as a massive Google Sheet that allows you to use SQL to manipulate and analyze your data. You can query data, join multiple datasets together, clean, process, and even perform machine learning tasks using BigQuery ML. The best part is you don't need any knowledge of Python or R; it's as simple as writing SQL syntax. You can find an example of using BigQuery ML in our segmentation with BigQuery post.

Learning SQL

If you're new to SQL or want to brush up on your skills, there are plenty of resources available online. One popular choice is the Super Cool Data learning platform.

A Practical Example

Let's consider an example where we have a table containing daily sales information for ebooks, workshops, and courses. We want to calculate the profit from only ebooks and courses for the first quarter (January-March).

Our table consists of columns like date, product name, sales amount, cost of goods sold (COGS), and ad spend.

Translating Requirements into SQL

To achieve our goal:

1. We want to select the date and product name.
2. Calculate profit based on sales amount minus COGS minus ad spend.
3. Apply filters for specific date ranges (first quarter) and products (ebooks and courses).

Here's our SQL query:

SELECT
  date,
  product,
  sales - COGS - ad_spend AS profit
FROM
  table_name
WHERE
  product IN ('ebook', 'course')
  AND date < '2021-04-01'
ORDER BY
  product DESC;

This query is quite readable:

• We select the required columns (date and product) along with our calculated column (profit).
• Specify which table to select data from.
• Apply filters on the product and date columns.
• Finally, sort the output by product in descending order.

Comparing SQL with Looker Studio

Similar operations can be performed in Looker Studio, but SQL provides more power and flexibility and a more readable and easier-to-understand syntax. With practice, you will become proficient at writing SQL queries for your data analysis tasks in Looker Studio.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn