The Dates in Unites States are formatted in MM-DD-YYYY or a similar month-first format. It is an odd convention compared to the rest of the world. The problem with this format is that when they are stored as string, they don’t sort in chronological order.
We might think that converting theses values from string to date might solve the problem, but it is actually not so quite simple as SQL formats dates as YYYY-MM-DD, a format that makes a lot of sense because it will sort in the same order whether it’s stored as a date or as a string.
Assuming we've got some dates properly stored as a date or time
data type, we can do some pretty powerful things. Maybe we'd like to
calculate a field of dates a week after an existing field. Or maybe
we'd like to create a field that indicates how many days apart the
values in two other date fields are. These are trivially simple, but
it's important to keep in mind that the data type of our results will
depend on exactly what we are doing to the dates.
When we perform arithmetic on dates (such as subtracting one date from another), the results are often stored as the interval
data type—a series of integers that represent a period of time. The
following query uses date subtraction to determine how long it took
companies to be acquired (unacquired companies and those without dates
entered were filtered out). Note that because the companies.founded_at_clean column is stored as a string, it must be cast as a timestamp before it can be subtracted from another timestamp.
SELECT companies.permalink,
companies.founded_at_clean,
acquisitions.acquired_at_cleaned,
acquisitions.acquired_at_cleaned -
companies.founded_at_clean::timestamp AS time_to_acquisition
FROM tutorial.crunchbase_companies_clean_date companies
JOIN tutorial.crunchbase_acquisitions_clean_date acquisitions
ON acquisitions.company_permalink = companies.permalink
WHERE founded_at_clean IS NOT NULL
In the example above, we can see that the time_to_acquisition column is an interval, not another date.
We can introduce intervals using the INTERVAL function as well:
SELECT companies.permalink,
companies.founded_at_clean,
companies.founded_at_clean::timestamp +
INTERVAL '1 week' AS plus_one_week
FROM tutorial.crunchbase_companies_clean_date companies
WHERE founded_at_clean IS NOT NULL
The interval is defined using plain-English terms like '10 seconds' or '5 months'. Also note that adding or subtracting a date column and an interval column results in another date column as in the above query.
We can add the current time (at the time you run the query) into your code using the NOW()function:
SELECT companies.permalink,
companies.founded_at_clean,
NOW() - companies.founded_at_clean::timestamp AS founded_time_ago
FROM tutorial.crunchbase_companies_clean_date companies
WHERE founded_at_clean IS NOT NULL
We can use LEFT to pull a certain number of characters from the left side of a string and present them as a separate string.
<aside> 💡 Syntax: LEFT(string, number of characters)
</aside>
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date
FROM tutorial.sf_crime_incidents_2014_01
We can use RIGHT to pull a certain number of characters from the right side of a string and present them as a separate string.
<aside> 💡 Syntax: RIGHT(string, number of characters)
</aside>
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date,
RIGHT(date, 17) AS cleaned_time
FROM tutorial.sf_crime_incidents_2014_01
The LENGTH function returns the length of the string.
SELECT incidnt_num,
date,
LEFT(date, 10) AS cleaned_date,
RIGHT(date, LENGTH(date) - 11) AS cleaned_time
FROM tutorial.sf_crime_incidents_2014_01
The TRIM function is used to remove characters from the beginning and end of a string.
SELECT location,
TRIM(both '()' FROM location)
FROM tutorial.sf_crime_incidents_2014_01
The TRIM function takes 3 arguments. First, we have to specify whether we want to remove characters from the beginning (’leading’), the end (’trailing’), or both. Next, we must specify all characters that are to be trimmed. Any characters included in the single quotes will be removed both from the beginning, end, or both sides of the string as mentioned in the query above. Finally, we must specify the text we want to trim using FROM.
POSITION allows us to specify a substring, then returns a numerical value equal to the character number (counting from left) where that substring first appears in the target string. For example, the following query will return the position of the character 'A' (case-sensitive) where it first appears in the descript field:
SELECT incidnt_num,
descript,
POSITION('A' IN descript) AS a_position
FROM tutorial.sf_crime_incidents_2014_01
We can also use STRPOS function to achieve the same results - just replace IN with a comma and switch the order of the string and substirng:
SELECT incidnt_num,
descript,
STRPOS(descript, 'A') AS a_position
FROM tutorial.sf_crime_incidents_2014_01
Importantly, both the POSITION and STRPOS functions are case-sensitive. If you want to look for a character regardless of its case, you can make your entire string a single by using the UPPER or LOWER functions described below.
LEFT and RIGHT both create substrings of a specified length, but they only do so starting from the sides of an existing string. If you want to start in the middle of a string, you can use SUBSTR.
<aside>
💡 Syntax: SUBSTR(*string*, *starting character position*, *# of characters*)
</aside>
SELECT incidnt_num,
date,
SUBSTR(date, 4, 2) AS day
FROM tutorial.sf_crime_incidents_2014_01
We can combine strings from several columns together (and with hard-coded values) using CONCAT. Simply order the values we want to concatenate and separate them with commas. If we want to hard-code values, enclose them in single quotes. Here's an example:
SELECT incidnt_num,
day_of_week,
LEFT(date, 10) AS cleaned_date,
CONCAT(day_of_week, ', ', LEFT(date, 10)) AS day_and_date
FROM tutorial.sf_crime_incidents_2014_01
Alternatively, we can use two pipe characters (||) to perform the same concatenation:
SELECT incidnt_num,
day_of_week,
LEFT(date, 10) AS cleaned_date,
day_of_week || ', ' || LEFT(date, 10) AS day_and_date
FROM tutorial.sf_crime_incidents_2014_01
Occasionally, we you'd prefer to contain actual values. This happens frequently in numerical data (displaying nulls as 0 is often preferable), and when performing outer joins that result in some unmatched rows. In cases like this, we can use COALESCE to replace the null values:
SELECT incidnt_num,
descript,
COALESCE(descript, 'No Description')
FROM tutorial.sf_crime_incidents_cleandate
ORDER BY descript DESC
Dates are some of the most commonly screwed-up formats in SQL. This can be the result of a few things:
MM/DD/YYYY format or another format that is not compliant
with SQL's strict standards.In order to take advantage of all of the great date functionality we need to have your date field formatted appropriately. This often involves some
text manipulation, followed by a CAST.
SELECT incidnt_num,
date,
(SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) ||
'-' || SUBSTR(date, 4, 2))::date AS cleaned_date
FROM tutorial.sf_crime_incidents_2014_01
This example is a little different from the answer above in that we've
wrapped the entire set of concatenated substrings in parentheses and
cast the result in the date format. We could also cast it as timestamp, which includes additional precision (hours, minutes, seconds).
Once we've got a well-formatted date field, we can manipulate in all sorts of interesting ways.
SELECT *
FROM tutorial.sf_crime_incidents_cleandate
We’ve learned how to construct a date field, but what if we want to deconstruct one? We can use EXTRACT to pull the pieces apart one-by-one:
SELECT cleaned_date,
EXTRACT('year' FROM cleaned_date) AS year,
EXTRACT('month' FROM cleaned_date) AS month,
EXTRACT('day' FROM cleaned_date) AS day,
EXTRACT('hour' FROM cleaned_date) AS hour,
EXTRACT('minute' FROM cleaned_date) AS minute,
EXTRACT('second' FROM cleaned_date) AS second,
EXTRACT('decade' FROM cleaned_date) AS decade,
EXTRACT('dow' FROM cleaned_date) AS day_of_week
FROM tutorial.sf_crime_incidents_cleandate
We can also round dates to the nearest unit of measurement. This is particularly useful if we don’t care about individual date, but do care about the week or month or quarter that it occurred in. The DATE_TRUNC function rounds a date to whatever precision we specify. The value displayed is the first value in that period. So when we DATE_TRUNC by year, any value in that year will be listed as January 1st of that year:
SELECT cleaned_date,
DATE_TRUNC('year' , cleaned_date) AS year,
DATE_TRUNC('month' , cleaned_date) AS month,
DATE_TRUNC('week' , cleaned_date) AS week,
DATE_TRUNC('day' , cleaned_date) AS day,
DATE_TRUNC('hour' , cleaned_date) AS hour,
DATE_TRUNC('minute' , cleaned_date) AS minute,
DATE_TRUNC('second' , cleaned_date) AS second,
DATE_TRUNC('decade' , cleaned_date) AS decade
FROM tutorial.sf_crime_incidents_cleandate
What if we want to include today’s date or time? We can instruct our query to pull the local date and time at the time the query is run using any number of functions. Interestingly, we can run them without a FROM clause:
SELECT CURRENT_DATE AS date,
CURRENT_TIME AS time,
CURRENT_TIMESTAMP AS timestamp,
LOCALTIME AS localtime,
LOCALTIMESTAMP AS localtimestamp,
NOW() AS now
Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. For example, if we wanted to take the sums of several columns, then average all of those values, we'd need to do each aggregation in a distinct step.
Subqueries can be used in several places within a query, but it's easiest to start with the FROM statement. Here's an example of a basic subquery:
SELECT
sub.*
FROM
(
SELECT
*
FROM tutorial.sf_crime_incidents_2014_01
WHERE day_of_week='Friday'
) sub
WHERE sub.resolution='NONE';
Let’s break down what happens when we run the above query:
First, the database runs the ‘inner query’ - the part between the parentheses:
SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE day_of_week = 'Friday'
When we run this query on its own, it would produce a result set like any other query. It returns the result set where all the records have “Friday” as the day of the week.
The important thing to remember is that the inner query must actually run on its own, as the database will treat it as an independent query. Once, the inner query runs, the outer query will run using the results from the inner query as its underlying table
SELECT sub.*
FROM (
<<results from inner query go here>>
) sub
WHERE sub.resolution = 'NONE'
Also, Subqueries are required to have names, which are added after parentheses the same way we would add an alias to a normal table.
-- Write a query that selects all Warrant Arrests from the
-- tutorial.sf_crime_incidents_2014_01 dataset, then wrap it
-- in an outer query that only displays unresolved incidents.
SELECT
sub.*
FROM
(SELECT
*
FROM tutorial.sf_crime_incidents_2014_01
WHERE category = 'WARRANT ARREST') sub
WHERE sub.resolution='NONE'
What if we wanted to figure out how many incidents get reported on each day of the week? Better yet, what if we wanted to know how many incidents happen, on average, on a Friday in December? In January? These are two steps to this process: counting the number of incidents each day (inner query), then determining the monthly average (outer query):
SELECT
LEFT(sub.date,2) AS cleaned_month,
sub.day_of_week,
AVG(sub.incidents) AS average_incidents
FROM
(SELECT
day_of_week,
date,
COUNT(incident_num) AS incidents
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1,2
) sub
GROUP BY 1,2
ORDER BY 1,2
We can use subqueries in conditional logic (in conjunction with WHERE, JOIN/ON or CASE . The following query returns all of the entries from the earliest date in the dataset.
SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE Date = (SELECT MIN(date)
FROM tutorial.sf_crime_incidents_2014_01
)
The above query works because the result of the subquery is only one cell.
Most conditional logic will work with subqueries containing one-cell results. However, IN is the only type of conditional logic that will work when the inner query contains multiple results:
SELECT
*
FROM tutorial.sf_crime_incidents_2014_01
WHERE DATE IN (
SELECT
date
FROM tutorail.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
)
Note that we should not include an alias when we write a subquery in a conditional statement. This is because the subquery is treated as an
individual value (or set of values in the IN case) rather than as a table.
It's fairly common to join a subquery that hits the same table as the outer query rather than filtering in the WHERE clause. The following query produces the same results as the previous example:
SELECT *
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
) sub
ON incidents.date = sub.date
This can be particularly useful when combined with aggregations. When
we join, the requirements for our subquery output aren't as stringent
as when we use the WHERE clause. For example, our inner query can output multiple results. The following query ranks all of the results according to how many incidents were reported in a given day. It does this by aggregating the total number of incidents each day in the inner query, then using those values to sort the outer query:
SELECT incidents.*,
sub.incidents AS incidents_that_day
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date,
COUNT(incidnt_num) AS incidents
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1
) sub
ON incidents.date = sub.date
ORDER BY sub.incidents DESC, time
-- Write a query that displays all rows from the three
-- categories with the fewest incidents reported.
SELECT incidents.*,
sub.count AS total_incidents_in_category
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN (
SELECT category,
COUNT(*) AS count
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1
ORDER BY 2
LIMIT 3
) sub
ON sub.category = incidents.category
Subqueries can be very helpful in improving the performance of your queries.
SELECT *
FROM tutorial.crunchbase_investments_part1
UNION ALL
SELECT *
FROM tutorial.crunchbase_investments_part2
It's certainly not uncommon for a dataset to come split into several parts, especially if the data passed through Excel at any point (Excel can only handle ~1M rows per spreadsheet). The two tables used above can be thought of as different parts of the same dataset—what we'd almost certainly like to do is perform operations on the entire combined dataset rather than on the individual parts. We can do this by using a subquery: