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.
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:
- We want to select the date and product name.
- Calculate profit based on sales amount minus COGS minus ad spend.
- 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.
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn