Example graph

Intro to SQL for Product Managers, Part 3

Please check out part 1 and part 2 if you haven’t already.

In this section, I’ll talk about a few ways to look at time when it comes to SQL queries.

Time is often an important piece of the puzzle for a product manager.

How many people have done x in the last 30 days? How many people did x within 7 days of signing up? How has our cancellation rate changed over time? How many people did x before we made a change vs after? etc.

Queries to figure out any of that require a good understanding of how to make the appropriate queries in SQL.

For the examples in this post, let’s say you have a table with dates showing when you last ordered a fruit and when you last got an order of those fruits.

order_table

namefirst_orderedlast_orderedlast_received
strawberry01/01/202011/12/202011/19/2020
pineapple02/01/202012/18/202012/11/2020
apple_r02/01/202012/20/202012/29/2020
apple_y02/15/20201/1/202112/21/2020
apple_g02/22/202001/12/202101/14/2021
banana03/01/202012/12/202012/19/2020
blueberry03/15/202012/18/202012/11/2020
raspberry04/01/202012/20/202012/29/2020
lemon04/01/202001/01/202112/21/2020

Date (compared to) Date

If you wanted to see which fruits have already been ordered and not yet arrived, you would look for those which had last ordered dates that are were later than last received dates.

SELECT name FROM order_table 
WHERE last_ordered>last_received

This would result in:

name
pineapple
apple_y
blueberry
lemon

Since all of these have last_ordered dates that are earlier than last_received.

COUNTS by date

It’s useful to look at counts before and after you launch something new, or make a change in your product.

In this case, let’s say you want to count how many orders you received before 1/1/21, and how many on or after 1/1/21.

SELECT COUNT(DISTINCT CASE WHEN last_received<'01/01/2021' THEN name END) 
AS "Received before 1/1/21", 
COUNT (DISTINCT CASE WHEN last_received >= '01/01/2021' THEN name END) 
AS "Received on or after 1/1/21" 
FROM order_table
Received before 1/1/21Received on or after 1/1/21
81

INTERVAL ’30 day’

Now, what if you want to see which fruits you’ve received in the last 30 days. The following would show all of the fruits where the last received date is greater than or equal to a date 30 days ago. 

SELECT name FROM order_table 
WHERE last_received >= (CURRENT_TIMESTAMP - INTERVAL '30 day')

CURRENT_TIMESTAMP, as the name implies, gives you today’s date and time. You can also do NOW(), which does the same thing.

You can use a variety of time increments with ‘interval’, as described in this SQL interval tutorial.

DATE_TRUNC

Next, let’s look at DATE_TRUNC. This lets you truncate the date to a week or month or other interval. This is particularly useful when you’re looking to map something over time on a weekly/monthly basis, rather than daily.

Let’s say I want to look at how many fruits I started order per month. I’m looking only at the month of the first ordered dates, counting the number of distinct names per month (that’s what the ‘group by m’ gives me). SQL notebook offers a list of the strings you can use with DATE_TRUNC.

SELECT DATE_TRUNC ('month', first_ordered) m, 
COUNT(DISTINCT name) as "# started ordering"
FROM order_table
GROUP BY m
m# started ordering
01/20201
02/20204
03/20202
04/20202

If you’re using a tool where you can make graphs from tables, this is very useful in visualizing the results.

Example graph

Note that the way dates are shown may be different; for example, your query system may show them with the year first, like 2021/01/01.

Let me know what you think!

Leave a Reply

Your email address will not be published. Required fields are marked *