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
name | first_ordered | last_ordered | last_received |
strawberry | 01/01/2020 | 11/12/2020 | 11/19/2020 |
pineapple | 02/01/2020 | 12/18/2020 | 12/11/2020 |
apple_r | 02/01/2020 | 12/20/2020 | 12/29/2020 |
apple_y | 02/15/2020 | 1/1/2021 | 12/21/2020 |
apple_g | 02/22/2020 | 01/12/2021 | 01/14/2021 |
banana | 03/01/2020 | 12/12/2020 | 12/19/2020 |
blueberry | 03/15/2020 | 12/18/2020 | 12/11/2020 |
raspberry | 04/01/2020 | 12/20/2020 | 12/29/2020 |
lemon | 04/01/2020 | 01/01/2021 | 12/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/21 | Received on or after 1/1/21 |
8 | 1 |
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/2020 | 1 |
02/2020 | 4 |
03/2020 | 2 |
04/2020 | 2 |
If you’re using a tool where you can make graphs from tables, this is very useful in visualizing the results.
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!