SQL for Product Managers — Part 1 — The Basics

If you’re a product manager looking to access and manipulate databases, SQL (Structured Query Language) is an incredibly powerful tool to have in your toolkit. With SQL, you can extract valuable insights, analyze data, and make informed decisions about your product.

In case you are an absolute beginner at SQL, I’d recommend visiting w3schools which has a comprehensive tutorial on SQL along with ‘Try it yourself’ options for practice.

However if you are familiar with the basics of SQL, I’d recommend reading further to get a refresher on some common SQL queries and concepts that could prove useful in your role.

I will break the SQL snippets into small parts, categorized by their use. For ease of understanding, we will use a very common ‘users_table’ table, which has information about users on an e-commerce platform.

Data Exploration

Fetch all records from a table: A simple SQL query that fetches all records of each user in the users_table table. This may include the id, first_name, last_name, etc.

SELECT * FROM users_table

Fetch specific columns from a table: A simple SQL query that fetches specific records from the users_table table. This example includes the first_name and email of each user.

SELECT first_name, email FROM users_table

Filter data using a WHERE clause: This SQL query fetches all records of each user in the users_table table where the age of the user is lesser than 35.

SELECT * 
FROM users_table 
WHERE age < 35

Count the number of records meeting a condition: This SQL query counts the number of records in the users_table table where the age of the user is lesser than 35. This will return the exact number of users under 35, and will not return rows/data like their name, email, etc.

SELECT COUNT(*) 
FROM users_table 
WHERE age < 35

Limiting Results

Limit the number of rows returned by a query: This example limits the number of rows returned by an SQL query. Even if there are 10,000 entries in the table, it will only return 10.

SELECT * 
FROM users_table
LIMIT 10;

Data Aggregation

Calculate averages, sums, and other aggregations: SQL is great for quick calculations of results. Using this query we are able to fetch all female users and calculate their average age and the sum of their orders.

SELECT AVG(age) AS average_age, SUM(orders) AS total_orders
FROM users_table 
WHERE gender = 'F';

Sorting and Ordering

Sort data in ascending or descending order: Using this example we are able to sort all users by the date they were registered/created in the table. We should be able to sort this by the ASC or DESC (ascending or descending) keyword.

SELECT * 
FROM users_table 
ORDER BY created_at ASC;

Grouping and Group Aggregations

Group data by a specific column and calculate aggregations within each group: This example groups the genders in the user table and returns 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;

Joining Tables

Combine data from multiple tables using INNER JOIN, LEFT JOIN, etc.: In SQL, a join is used to combine data from two or more tables based on a related column (usually a user ID or some other ID) between them. This allows you to fetch and display data that is stored across multiple tables. Joins are important for working with relational databases where data is stored in separate tables.

In this example, we Join the users_table with the orders_table. The common column is ‘id’ in the users_table and ‘user_id’ in the orders_table. Once we join these tables, we are able to fetch data like id, first_name, num_of_items from either the users_table or the orders_table.

SELECT USER.id, USER.first_name, ORDER.num_of_item 
FROM users_table as USER
INNER JOIN orders_table as ORDER 
ON USER.id = ORDER.user_id;

SELECT * 
FROM users_table as USER
INNER JOIN orders_table as ORDER 
ON USER.id = ORDER.user_id;

Subqueries

Use a subquery to retrieve data based on the results of another query: SQL subqueries are a way to use the result of one query (the inner query) as a part of another query (the outer query). Subqueries help you break down complex tasks into smaller, more manageable steps.

In this example, the inner query is fetching user_ids of orders that are marked ‘Complete’. The outer query then goes on to fetch the first_name and last_name of these user_ids. This essentially returns the names of all users who have Completed Orders.

SELECT first_name,last_name 
FROM users_table 
WHERE id 
IN (SELECT user_id FROM orders WHERE status = 'Complete');

Date and Time Manipulation

Extract year, month, day, etc., from date columns: Date and Time Manipulation involves performing operations on date and time values stored in your database tables. SQL provides various functions and techniques to work with date and time data.

In this example, we are fetching the YEAR and MONTH from the created_at column.

SELECT YEAR(created_at) AS year, MONTH(created_at) AS month 
FROM users_table;

Data Trends and Patterns

Analyze trends over time using time-based grouping: There are endless ways of Analysing Trends and Patterns in SQL Databases. While the below SQL is just one example, we will delve into further details in the upcoming posts on Analysing Data.

This example fetches the count of all users, grouped across particular months, and then orders them accordingly.

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

Analyzing User Behavior

Identifying the most active users based on interactions: Similar to Trend and Pattern Analysis, there is no limit to the Analysis carried out to understand user behavior.

In this particular example, we group the user_id and count the number of orders per user_id. We then order them in Descending and Limit to 10. This essentially gives us the top 10 user_id with the highest order count.

SELECT user_id, COUNT(*) AS orders 
FROM orders_table
GROUP BY user_id 
ORDER BY orders DESC 
LIMIT 10;

This covers the overview of SQL queries and gives an understanding of the basic structure and capabilities of SQL. In subsequent articles, we will cover in further detail, the abilities of SQL to identify Data Trends and User Behaviour.