SQL for the product (minimum) · Sergey Sukharevsky

SQL is not for the replacement of the analyst, but for the purpose of checking a simple question and more accurately putting a complex one. The quality of a query begins with the unit of analysis and metric determination; even a syntactically true JOIN can subtly multiply strings and give a convincing but incorrect answer.

Why a Product Knows SQL

Why SQL Knowledge is Necessary

The product manager who owns SQL is not dependent on analysts for fresh data or quick checks on A/B test results. This saves time and speeds up decision-making.

Example: The question is how many users have been onboarded in a week. You can wait for the report, or you can make a simple sample from the database in 5 minutes. The second option allows you to immediately prepare a hypothesis or start a new experiment.

At least you need

You don’t need to be able to build complex ETL processes or optimize indexes. Enough to be able to:

select the right lines from the table
Aggregate data (amounts, averages, quantity)
connect tables (retention, conversions)
filter by date, event, segment of users

Basic constructions of SQL: the basis for the product

SELECT, WHERE, GROUP BY

SELECT is the base. You select the desired fields, you can filter, group and count metrics.

Example: output the total number of purchases by day per month, where the user\ id is not empty.

SELECT date, COUNT(*) as purchases
FROM orders
WHERE user_id IS NOT NULL
  AND date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY date

There are three frequent requests: date filtering, aggregation and grouping. This pattern is often used to construct dashboards on key metrics.

JOIN - Table connection

Often, a product needs to combine tables, such as events from logs and a list of users from users.

Example: Find the number of registrations that led to at least one purchase.

SELECT COUNT(DISTINCT u.user_id) as buyers_after_signup
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.signup_date BETWEEN '2024-06-01' AND '2024-06-30'

You can see here: first, registrations are selected for the period, then through JOIN they are connected to purchases. It’s a funnel-building pattern.

Detailed explanations are given in SQL for Product: Minimum Necessary.

Metrics: What and how to count with SQL

Examples of basic metrics

Metrics that are important to be able to count manually:

DAU, WAU, MAU – counted by COUNT (DISTINCT user\ id)
Retention – by comparing user activity between different days (Cohort Analysis)
Conversion – the share of users with a certain action (the number of paid to the number of registered)
Average check – AVG (amount) from the order table

Examples for Daily Retention:

SELECT signup_date,
       COUNT(DISTINCT user_id) AS cohort_size,
       COUNT(DISTINCT CASE WHEN activity_date = signup_date + INTERVAL 1 day THEN user_id END) AS d1_retained
FROM users
LEFT JOIN activity ON users.user_id = activity.user_id
GROUP BY signup_date

This shows that retention for D1 can be considered as one short sample.

Cases - Application in Experiments

In the A/B test, it is often segmented: how many people in Group A performed a targeted action compared to Group B.

SELECT experiment_group, COUNT(DISTINCT user_id) as conversions
FROM exp_results
WHERE event = 'target_action'
GROUP BY experiment_group

Mistakes and anti-patterns when working with SQL

Popular Beginner Mistakes

Do not filter by events, including test or blocked users, mistakenly overestimating metrics.

Confusing unique and non-unique users. For example, counting orders as users without DISTINCT is 3-5 times higher.

Duplicate JOIN and get repeats of lines, which distorts the amounts and averages.

How to Avoid Model Traps

Examine the structure of the tables before use. Large products have dozens of tables with similar names, different links, and indices.

Example: Orders tables may include test payments. If you don’t filter them (e.g. is\ test = false), the average check will be incorrect.

Checklist of self-checking: check the filtering, correctness of aggregation, connection of only the necessary entities, selection of unique records.

How to learn: resources and advice

Selection of services and tasks

SQL honing is easiest on real-world tasks: start with everyday queries on key product spreadsheets.

Services for training and knowledge cutting:

Recommendations for practice

It is better to practice short sessions – every day for 10-15 minutes. Save ready-made requests, comment on them. In a month you will get your own set of craft templates.

Don’t be afraid of mistakes: at the start, it is important to go through dozens of wrong options to develop automation.

FAQ

What is SQL useful to a product when you have BI reports and analytics? SQL allows you to independently obtain and verify data, quickly test hypotheses and save time when analyzing metrics.

What are the most popular products? Reading events by user, building simple funnels, counting retension, calculating conversion, comparing segments.

**Is it worthwhile to understand advanced SQL if you don’t want to be an analyst? Basic knowledge of SQL is a must-have, even for those who don’t go into analytics, because it’s a quick tool to check critical metrics.

How do I know if the data is correct? Carefully check the filtering, table structure, connection logic and user uniqueness. Compare the results with previous reports.

Where to find practical training tasks? Use real questions about your product, platforms like SQLBolt, disassemble case studies from open courses.

What are the most common errors in SQL products? Exclude the necessary filters, make mistakes in the connections of tables, confuse unique users and repetitive actions.