So far we’ve been filtering records with equals
= operator. If a value in the column matches the value in our filter we’d see this record in the result:
SELECT COUNT(*) FROM users WHERE country = 'us'
This query will tell us how many signups do we have from the US in total. But what if we want to know how many signups did we have in the last 30 days, last year or June 2018? As the title of this lesson suggests – we’ll filter records by time.
In SQL, as in many other programming languages dates behind the scenes are just numbers. When we need to compare them we can use:
Let’s examine the query that counts users who signed up after 1, Jan 2018:
SELECT COUNT(*) FROM users WHERE signup_date > '2018-01-01'
Doesn’t look scary, right? We’re using a date string '2018-01-01' and the greater than comparison operator to filter records by signup_date column. Notice that the syntax is very similar to the previous query: we’re just using a different comparison operator (> instead of =).
You may be wondering, how does SQL figure out how to compare a string value '2018-01-01' and the actual dates inside the signup_date column? Remember that each column has a specific type? (we define column types when creating a table). In our case, the signup_date column has date type, which helps SQL perform an accurate comparison.
Today’s SQL milestone: you’ve learned equals =, greater than > and less than < operators. Now you can filter records by numbers (“All users older 18” or “All customers who paid more than 20$”) and by dates (“Signups after 2019” or “Books published after January 1, 2018”). Congratz!
Hi, it’s Anatoli, the author of SQL Habit.
SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst and solve real-life challenges from Business, Marketing, and Product Management.
SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work.