16.11. Working with Data in BigQuery with SQL

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

16_page-0011.jpg

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: