SQL for Product Managers — Part 1 .1— The Basics in Action

In this short note, I will show you how you can practice your SQL using a public database available on Bigquery.

Head to Bigquery and create a free account here — https://console.cloud.google.com/bigquery

You can then import ‘thelook_ecommerce’ an E-commerce database with tons of data to work with. Using this you will be able to fire actual SQL queries and fetch data in real time.

This is what the platform looks like once you’ve got it started.

Let me share a few more examples that I’ve implemented in the previous article and their associated data.

This is the script from the previous article. This allowed us to fetch all users from the users_table whose age was less than 35.

SELECT * 
FROM users_table 
WHERE age < 35

and this is what it would look like in Bigquery

SELECT * 
FROM `bigquery-public-data.thelook_ecommerce.users`
where age < 35

This is what it looks like on the platform

Using this script from the previous article, allowed us to group the genders in the user table and return the count of them. This is particularly useful to understand the type of records we have in our table and the aggregates of those records.

SELECT gender, COUNT(*) AS count 
FROM users_table 
GROUP BY gender;

This is what it would look like in Bigquery

SELECT gender, COUNT(*) AS count 
FROM `bigquery-public-data.thelook_ecommerce.users` 
GROUP BY gender;

Let's move to another, slightly complex script.

In the previous article, we used the following script to fetch the count of all users, grouped across particular months that they registered and then ordered them accordingly.

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS count 
FROM users_table
GROUP BY month 
ORDER BY month;

This is what it would look like in Bigquery

SELECT FORMAT_DATE('%B',created_at) as month, COUNT(*) AS count 
FROM `bigquery-public-data.thelook_ecommerce.users`
GROUP BY month 
ORDER BY count DESC

Here, instead of ordering the list by Month, I’ve tweaked it to order by count which helps give an understanding of how many users were created in which month, with the highest month at the top.

Going forward we will use the ‘thelook_ecommerce’ data source to query and understand the data patterns and trends from it.