Let’s look at the query that counts signups within the first week of Bindle’s existence:
SELECT
COUNT(*)
FROM users
WHERE
signup_date >= '2018-01-01'
AND signup_date <= '2018-01-07'
As you can see we used the signup_date column twice which might be sometimes confusing (imagine you have 15 filters they don’t follow any particular order).
SQL is 47+ years old, there’s definitely a better way to filter records with ranges: using BETWEEN operator. The query above could be rewritten like so:
SELECT
COUNT(*)
FROM users
WHERE
signup_date BETWEEN '2018-01-01' AND '2018-01-07'
BETWEEN operator filters records within a given range of numbers or dates.
BETWEEN operator is inclusive – both lower and higher values of the range will be included in the query’s output.
Which is exactly what we want! Now our query resembles English more than Mathematics
Be careful when using BETWEEN
with timestamp (datetime
) columns. Compare the results of these similarly looking 2 queries:
SELECT
COUNT(*)
FROM users
WHERE
signup_date BETWEEN '2018-01-01' AND '2018-01-07'
SELECT
COUNT(*)
FROM users
WHERE
created_at BETWEEN '2018-01-01' AND '2018-01-07'
The only difference is what column we’re using for our range: signup_date
is a date column and created_at
is a timestamp (datetime) column.
The trick here is the conversion of the range dates ('2018-01-01'
and '2018-01-07'
) to timestamps which SQL engine does behind the scenes:
SELECT
'2018-01-01'::timestamp,
'2018-01-07'::timestamp
To debug this and similar cases it often helps to go through all the records manually and look for a pattern (you’ll see there’re no records on Jan, 7):
SELECT
*
FROM users
WHERE
created_at BETWEEN '2018-01-01' AND '2018-01-07'
ORDER BY created_at DESC
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.