Periscope Data
The world’s fastest visualization platform for data analysts.

SQL for Marketers Part 2 — Users over the Last 30 Days

October 30, 2014

Jon Bishop recently joined Periscope to scale our marketing. This blog post is the second in a series he’s writing as he learns SQL. You can see part one here.

Further down the SQL hole

Welcome back to SQL for Marketers. Last time we covered basic SQL terms and the SQL commands selectfromwhere and join.

This time around, we’re going to work through the SQL necessary to determine how many users visited our site. We’ll use selectfrom and where from our previous lesson while introducing a few new commands.

First we start with select, which we’ll use to retrieve user ids and the dates that users signed up.

Here’s the SQL:

select id, created_at
from users

You’ll notice that we separate names of the user id and date columns with a comma. You need to do this for every column selected, except for the last.

And here’s the table output:

Getting Dates from Timestamps

Now that we have the table above, we need to make some changes to make the data more useful. It’s common for databases to store a user’s signup date as a timestamp.

In addition to the date, a timestamp includes the hour, minute and second that the user signed up. For today’s query, we only want the date information.

To convert the timestamps into a date, we’ll use a new SQL function: date().

select id, date(created_at)
from users

Retrieving data within a date range

While our SQL is starting to generate useful data, we can make it much more useful by adjusting the date range to the last 30 days.

We do this with the where command. We’ll use it to select data produced within certain dates, but you can also use it to slice data on many other dimensions from your database.

select id, date(created_at)
from users
where created_at >= '2014-09-28' and created_at <= '2014-10-28'

In the query above we used >=. This means greater than or equal to. Since we’re using it on a date in our query, it means on or after the date that follows.

Next, we used andand connects the two date restrictions. After that, we use <=(less than or equal to) and follow it with a date.

Creating dynamic queries with the now() function

In our query above, we wrote specific dates. We want a chart, though, that shows the data from the last 30 days no matter when you run it.

To accomplish this, we’ll use the now() function. now() produces the current date and time. If you want to look at the last 30 days of user data, you’ll type now() - interval '30 days'. You need to include interval whether it’s 30 days or just one day for this to work.

Our SQL now looks like:

select id, date(created_at)
from users
where created_at >= now() - interval '30 days'

Now we have the data we want!

Sign up for our blog updates to get future SQL lessons in your inbox!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?