Last year, I applied for a PM job through a referral. I got a call from the recruiter the same day and they only asked 1 questions - “How good is your SQL?”. This was their first question even before they asked me my CTC, availability etc.
Although, not every company would want you to be an expert in SQL and not every job description will have it as a skill, but still SQL is a very important skill to have for a Product Manager. Hence, today we will be going through the basics of SQL and make you equipped with the skills to navigate through data. Are you ready?
What is SQL?
SQL, or Structured Query Language, is a database specific programming language – meaning it can only be used with databases.
To understand databases, think of them as digital repositories for your product's data, similar to a well-organized filing system.
In this system, there are tables containing specific categories of information about your users and their actions. What makes this system particularly powerful is that these are connected with each other. This interconnectedness allows us to get access to complex data drawn from these relationships between different tables.
When you use SQL, you're asking questions to this data repository. For instance, you might ask, "Show me all users who registered last month," or "What is our most frequently used feature?" SQL then processes these requests, searches through the relevant tables, and returns the specific information you've requested.
SQL works with MySQL databases.
What's a MySQL database? Well, it's a popular type of database known as a relational database. What's a relational database? A relational database is a set of multiple data sets organised by tables, records and columns. Each table is known as a relation.
Data is stored in tables. Just like data in your excel sheets is stored in tables.
Lets consider a very basic example from instagram to understand this, a simplified database structure (AKA data-schema) for instagram could look like this:
Table 1: Users Columns: id, first_name, last_name, handle, registered_at, email_id
Table 2: Photos Columns: id, user_id, photo_url, created_at, caption
Table 3: Likes Columns: id, user_id, photo_id, created_at
As per this schema, whenever someone registers on Instagram, a new row is added, the person is assigned an automatically generated unique id (id) and other information (first_name, last_name, handle, registered_at, email_id) about them is recorded in the users table.
Why do Product Managers need to know SQL?
As a product manager, you might question why you need to know SQL, given that data analysis isn't considered a core part of the role and you have data teams who can answer your queries.
Now, imagine a scenario where you need to know what discount value leads to highest revenue and conversion. Now, if you wait for an engineer or analyst to answer this, it might take some time. But if you know SQL, you can literally know this in 2 minutes. That's a superpower, no?
Well, here are several compelling reasons why SQL is a valuable skill for you:
- Less dependency on others: SQL empowers you to access and analyse data independently. This reduces your reliance on data science and engineering teams for basic information, allowing you to obtain important data without any delays.
- Enhanced Analytical Capabilities: SQL enables you to delve deeper into product data. You can test hypotheses, validate or challenge assumptions, and identify patterns in user behaviour. This analytical power can lead to more informed product decisions.
- Increased Efficiency: In product management, quick access to information is crucial. SQL allows you to rapidly answer data-related questions that arise during meetings or discussions, enhancing your responsiveness and decision-making speed.
- Customized Data Insights: SQL allows you to create custom queries, giving you the flexibility to extract and combine data in ways that are most relevant to your current challenges or questions.
By mastering SQL, you're not just learning a technical skill; you're enhancing your ability to make data-driven decisions and communicate effectively.
How much knowledge is enough?
As a product manager, you don't need to become an SQL expert. The goal is to acquire enough knowledge to extract insights from your data. Here's a concise list of essential SQL skills to focus on:
- Basic Queries: Learn to write simple SELECT statements.
- Filtering and Sorting: Master WHERE clauses and ORDER BY commands.
- Aggregating and Grouping: Understand functions like COUNT, SUM, and GROUP BY.
- Basic Joins: Learn to connect tables with simple joins.
- Data Schema: Familiarise yourself with your product's data structure.
I know, you might not be familiar with any of these things right now, but don't worry, we will cover all of them.
These skills will enable you to handle most data challenges you'll encounter. Remember, the objective is to become self-sufficient in basic data analysis, not to replace your data science team.
How do you query data using SQL?
Alright, it's time to roll up our sleeves and get into the nitty-gritty of SQL querying.
The heart of SQL querying is the SELECT statement. It's like saying, "Hey database, could you please show me..."
Here's the basic structure:
SELECT [what you want to see]
FROM [where to look for it]
WHERE [any specific conditions]
ORDER BY [how you want it sorted]
LIMIT [how many results you want];
Let's break this down with a real-world example. Say you want to find out who your top 10 most active users were last month. Your SQL query might look something like this:
SELECT username, count(*) as actions
FROM user_actions
WHERE action_date >= '2023-05-01' AND action_date < '2023-06-01'
GROUP BY username
ORDER BY actions DESC
LIMIT 10;
This query is saying: "Show me the usernames and how many actions they took, look in the user_actions table, only consider actions from last month, group the actions by user, sort them from most actions to least, and just show me the top 10."
See? It's almost like English! With a bit of practice, you'll be writing queries like this in your sleep.
Writing SQL - learn this one syntax
Alright, listen up because this is the golden ticket of SQL querying.
If you learn this one syntax well, you'll be able to answer a ton of questions about your product:
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ... ASC/DESC
LIMIT number;
Each component serves a specific purpose:
- SELECT: Specifies the columns to retrieve.
- FROM: Indicates the source table.
- WHERE: Filters the data.
- GROUP BY: Aggregates data into groups.
- HAVING: Filters grouped data.
- ORDER BY: Sorts the results.
- LIMIT: Caps the number of returned rows.
Understanding how these elements work together will significantly enhance your SQL proficiency.
Most important SQL Queries
Retrieving all data from a table:
Note: Always use this judiciously, preferably with a LIMIT clause.
SELECT * FROM users;
Counting records:
This query counts the number of signups for the current day.
sql
Copy
SELECT COUNT(*) FROM signups WHERE signup_date = CURDATE();
Grouping and counting:
This query provides user counts by country, sorted in descending order.
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;
Calculating averages:
This query calculates the average session duration for the past week.
SELECT AVG(session_duration) as avg_session
FROM user_sessions
WHERE session_start >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
Identifying top performers:
This query identifies the top 5 best-selling products.
SELECT product_name, SUM(quantity) as total_sold
FROM orders
GROUP BY product_name
ORDER BY total_sold DESC
LIMIT 5;
Applying Filters and Limits
If you want to filter your data by some criteria, you can use the 'WHERE' clause, for example you want to name and date of registration of the user whose email address is 'john.doe@gmail.com', for such as case you will use:
SELECT first_name, last_name, created_at, email_id
FROM users
WHERE email_id = 'john.doe@gmail.com'
Here are some other examples:
- Basic filter:
This retrieves all users who signed up since the start of 2023.
sql
Copy
SELECT * FROM users WHERE signup_date >= '2023-01-01';
- Multi-condition filter:
This finds high-value, completed orders from the current month.
sql
Copy
SELECT * FROM orders
WHERE order_date >= '2023-06-01'
AND total_amount > 100
AND status = 'completed';
- Pattern matching filter:
This finds all products with "phone" in the name.
sql
Copy
SELECT * FROM products WHERE name LIKE '%phone%';
The LIMIT clause is essential for managing query output:
sql
Copy
SELECT * FROM large_table LIMIT 1000;
This ensures you're working with a manageable subset of data.
Introduction to Joins in SQL
Joins are powerful SQL features that allow you to combine data from multiple tables. The main types of joins are:
- INNER JOIN: Returns only matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
- FULL OUTER JOIN: Returns all rows when there's a match in either table.
Here's an example of an INNER JOIN:
sql
Copy
SELECT users.name, orders.order_date, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.order_date >= '2023-01-01';
This query combines user data with their order information for the current year.
Bringing it all together
Your SQL skills can be applied to various product management scenarios:
- Feature adoption tracking: Query to measure daily usage of new features.
- User engagement analysis: Calculate metrics like average session duration or login frequency.
- Power user identification: Rank users based on activity levels.
- Conversion rate monitoring: Track conversion from free trials to paid subscriptions.
- Bug investigation: Query relevant tables to identify patterns in reported issues.
Always verify your queries and results, especially when presenting data to stakeholders. Remember to use LIMIT to manage query performance.
What next?
To further develop your SQL skills:
- Practice with real data: Use your product's actual database for hands-on experience.
- Explore advanced concepts: Learn about subqueries, window functions, and complex joins.
- Study data visualization: Familiarize yourself with tools like Tableau or Looker that complement SQL.
- Collaborate with data experts: Learn best practices from your data science team.
- Stay informed: Keep up with new SQL features and improvements.
The goal is to become self-sufficient in data analysis and decision-making, not to replace your data team. With SQL in your skillset, you're better equipped to be an effective, data-driven product manager.
