Further down the SQL hole
In this lesson, we’re going to work through the SQL necessary to determine how many users visited our site. We’ll use select, from, and where (from previous lessons) 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 and. and 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!