SQL with a focus on Product metrics by ex-Spotify & Meta DS

A walkthrough of the solution to the first exercise

Let’s imagine there is a sale going on and we’d like to offer a 50% discount on all our products. Select the all columns from the products table with a 50% reduction in the price

SELECT

name,

category,

unit_price*0.5 as unit_price,

is_premium

FROM products

In order to only select the rows that you want, you can use the WHERE clause.

If we have a numeric column, there are several comparisons that we can make. Here is the list of comparison operators:

• > greater than

• >= greater than or equal to

• < lesser than

• <= lesser than or equal to

• = equal to

• != <> not equal to

Different variable types will allow you to use different operators.

If you’re just looking for the first N rows, you can also use the LIMIT keyword

Select all orders of 3 products or more

SELECT * FROM orders WHERE quantity >= 3

Let’s talk about date variables. Dates in SQL are written with single quotes, like strings.

SELECT *

FROM clients

WHERE birthdate>'1989-01-01'

There are several functions that can be useful to parse dates and extract the level of detail that you want. For example, you could use the function MONTH to extract the month of each order

SELECT date, MONTH(date) month

FROM orders

We get the month of each order. Similarly, if we were to use the YEAR() function, we could extract the year

SELECT date,

MONTH(date) month,

YEAR(date) year

FROM orders

There are other date functions, including functions that let you add or subtract different dates , but these will be sufficient for our purposes.

AND, OR, BETWEEN

It can be useful to create more sophisticated conditions and make our WHERE clauses more interesting. For example, we may want to select all female clients who live in the US. To do this, we use an AND clause

SELECT * FROM clients WHERE gender='F' AND country='US'

Now, we only see results for which both conditions are true. We can add as many ANDs as we want. Let’s say that we want to query all the clients who are female or clients who live in the US. In this case, we can use the OR clause

SELECT * FROM clients WHERE gender='F' OR country='US'

In this case, we will retrieve all records for which at least one of the conditions evaluates to true. We can also add as many OR clauses as we want.

Select all products which are premium or whose price is less than 2

SELECT * FROM products WHERE is_premium OR unit_price<2

Sometimes we want to write conditions in which a field can take on multiple values. We could do this with multiple ANDs, but it is unnecessarily verbose and cumbersome. Instead, we can use the IN operator. For example, if we want to select all clients Portugal, the UK, and the US, we could do so like this

SELECT * FROM clients

WHERE country IN ('PT', 'UK', 'US')

Where we enclose the different values in brackets. Alternatively, if we wanted all clients except the ones in those same countries, we could add the NOT operator

SELECT * FROM clients

WHERE country NOT IN ('PT', 'UK', 'US')

The NOT operator negates the condition.

Select all products belonging to the “low cost” and “standard” categories

SELECT * FROM products

WHERE category IN ('low_cost', 'standard')

An alternative way to match is to use the LIKE operator which evaluates true if it matches a specific string pattern. When using the “%”, we are saying that we want to match any number of characters, which might be zero or more. For example, if wanted to get all client names which contain “ri”, we could so as follows

SELECT * FROM clients

WHERE name LIKE '%ri%'

The % sign acts as a wild card and it will match any number of characters before and after “ri”.

Select all clients whose country names do not contain N

SELECT * FROM clients

WHERE country NOT LIKE ('%N%')

This time I'm going to show you how to look for records that miss an attribute. For example, if we look at client’s surnames, we notice that some values are NULL. In other words the attribute is missing. NULL means the absence of a value. This could be because your clients forgot to input their surname into the registration form. How could you find all NULL values? You can do so by using the IS NULL statement

SELECT *

FROM clients

WHERE surname IS NULL

ORDER BY

Let’s imagine that you want to order your values according to some criteria. In order to do that, all you need to use is the ORDER BY statement. For example

SELECT *

FROM clients

ORDER BY name

Will sort all the records in the clients table by name in ascending order, which is the default sorting method. If you wanted to sort in descending order, you could include the DESC clause.

SELECT *

FROM clients

ORDER BY name DESC

We can also sort records using multiple columns

SELECT *

FROM clients

ORDER BY name, surname

By using the DESC statement and multiple column names, you can sort the table the way that you want.

Select all orders and the sort the values by quantity, in descending order, and then by date in ascending order

SELECT *

FROM orders

ORDER BY quantity DESC, date

Count , Sum, AVG, Max, Min, Distinct

When dealing with metrics, we are often interested about aggregates.

For example, It may be interesting to consider just the distinct products ordered. For this, we can use the distinct clause

SELECT DISTINCT product_id FROM orders

It looks like every product was ordered at least once.

One simple aggregation would be to count the number of orders placed by clients

SELECT COUNT(id) FROM orders

This will count the number of orders in the orders table. Another number we may be interested in is the total number of products ordered. In order to accomplish this, we can Sum the quantity field.

SELECT SUM(quantity) FROM orders

We might also be interested in the average number of products per order, which we can obtain by querying

SELECT AVG(quantity) FROM orders

We could also get the max/min number of products ordered with the following queries,

SELECT MAX(quantity) FROM orders

SELECT MIN(quantity) FROM orders

Exercise 8

Select the average rating given by clients

SELECT AVG(rating) FROM ratings

GROUP BY, HAVING

It can also be really useful to compute aggregates of certain groups. For example, if we want to query the total quantity ordered for each client, we could do so by

SELECT client_id, SUM(quantity) AS orders

FROM orders

GROUP BY client_id

Lets say that you now want to filter your results to only select clients who have ordered more than 3 products. You could do this by using the HAVING clause

SELECT client_id, SUM(quantity) AS orders

FROM orders

GROUP BY client_id

HAVING orders >=3

ORDER BY orders DESC

This “having” clause differs from the “WHERE” because it operates over groups, not rows.

To illustrate this, let’s query the number of product 1 orders per client, for clients who have ordered more than 3 products.

SELECT client_id, SUM(quantity) AS orders

FROM orders

WHERE product_id=1

GROUP BY client_id

HAVING orders >=3

ORDER BY orders DESC

Select the total quantity ordered by product id

SELECT

product_id,

SUM(quantity) as items

FROM orders

GROUP BY product_id

Select the average rating, by product, of products whose average rating is higher than 4

SELECT product_id, AVG(rating) AS average_rating

FROM ratings

GROUP BY product_id

HAVING AVG(rating) > 4

Select all product data and their related ratings

SELECT *

FROM products

JOIN ratings ON products.id=ratings.product_id

Select all product names and all respective ratings received

SELECT name, rating

FROM products p

LEFT JOIN ratings r

ON p.id=r.product_id

Select the names and total revenue generated from the clients who live in the UK or the US and who have bought more than 3 items. Order the results by who has spent the most, in descending order

SELECT c.name, SUM(p.unit_price*o.quantity) as revenue

FROM clients c

LEFT JOIN orders o

ON c.id=o.client_id

LEFT JOIN products p

ON p.id=o.product_id

WHERE country in ('US', 'UK')

GROUP BY c.name

HAVING SUM(quantity) >3

ORDER BY SUM(p.unit_price*o.quantity) DESC

It’s really important to have the right product metrics in place in order to know the health of your business. This section of the course will go over some commonly used product metrics and how to implement them using our database. The exact way to implement these metrics might differ depending on your use case and what you value, but you will still finish this course with a pretty good base and sense of how you could go about implementing them. It will also be a good opportunity to put into practice the SQL skills that you just learned.

Am overview of the fundamentals of metrics and good practices

Monthly Recurring Revenue - this is the amount of money that you are certain about receiving for a given period of time. It can be especially relevant to subscription businesses, as it can be used to measure how much (relatively) stable income you are generating.

A walktrough of the solution for Exercise 14.

SELECT

MONTH(date) month,

YEAR(date) year,

AVG(monthly_price)*COUNT(distinct client_id) as MRR

FROM subscriptions

GROUP BY

MONTH(date),

YEAR(date)

ORDER BY year, month

Average revenue per user → The average revenue per user received over a period of time. This will allow you to extract growth insights from your customer base. This can also be calculated as the MRR/number of users. This metric can be really powerful when broken down by specific groups of users and cohorts, helping you identify which customer segments you should focus on.

A walkthrough of the solution to exercise 15

SELECT

MONTH(date) month,

YEAR(date) year,

AVG(monthly_price) ARPU

FROM subscriptions

GROUP BY

MONTH(date),

YEAR(date)

ORDER BY year, month

Lifetime value

• = ARPU * Average customer lifetime

• How much money a user will generate in the long term

• Good for selecting customer acquisition channels and retention strategies

Monthly Active Users → the way to define “active” can vary from business to business. Sometimes, having a paying subscription can be enough to deem a client “active”. Most often, it will require a client to do some kind of high intent action, like adding a product to their favorites list or consuming a subscription service above a certain minimum. This can be really useful to measure the level of engagement between your clients and your product.

Using our business database, and considering that it is enough for a client to have a subscription for him/her to be deemed active, try to write a query that gets the MAU.

SELECT

MONTH(date) month,

YEAR(date) year,

COUNT(distinct client_id) as MAU

FROM subscriptions

GROUP BY year, month

ORDER BY year, month

Walkthrough of solution to exercise 17

SELECT

MONTH(o.date) month,

YEAR(o.date) year,

COUNT(distinct c.id) as MAU

FROM clients c

JOIN orders o

ON c.id=o.client_id

GROUP BY year, month

ORDER BY year, month

A brief of overview of retention and its importance as a product metric

A walkthrough of the solution to exercise 18

SELECT

MONTH(o.date) month,

YEAR(o.date) year,

c.id,

COUNT(1) as re_orders

FROM clients c

JOIN orders o

ON c.id=o.client_id

GROUP BY c.id, year, month

HAVING count(1)>1

ORDER BY year, month

Customer Satisfaction - Customer satisfaction can be measured as the average rating given to your product by your clients. Typically, you will ask for a rating shortly after your client has purchased your product.

A walkthrough of the solution to exercise 19

SELECT name, AVG(rating) CSAT

FROM products p

LEFT JOIN ratings r

ON p.id=r.product_id

GROUP BY name

A brief overview of how to vizualize metrics in a simple way

Some tips and principles about reporting metrics

Congratulations on making it to the end of the course! Thank you for the time dedicated.