Classifying parking violations by time of day
There have been some concerns raised that parking violations are not being issued uniformly throughout the day. You have been tasked with associating parking violations with the time of day of issuance. You determine that the simplest approach to completing this task is to create a new column named morning. This field will be populated with (the integer) 1 if the violation was issued in the morning (between 12:00 AM and 11:59 AM), and, (the integer) 0, otherwise. The time of issuance is recorded in the violation_time column of the parking_violation table. This column consists of 4 digits followed by an A (for AM) or P (for PM).
In this exercise, you will populate the morning column by matching patterns for violation_times occurring in the morning.
Cet exercice fait partie du cours
Cleaning Data in PostgreSQL Databases
Instructions
- Use the regular expression pattern
'\d\d\d\dA'in the sub-query to matchviolation_timevalues consisting of 4 consecutive digits (\d) followed by an uppercaseA. - Edit the
CASEclause to populate themorningcolumn with1(integer without quotes) when the regular expression is matched. - Edit the
CASEclause to populate themorningcolumn with0(integer without quotes) when the regular expression is not matched.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
SELECT
summons_number,
CASE WHEN
summons_number IN (
SELECT
summons_number
FROM
parking_violation
WHERE
-- Match violation_time for morning values
___ SIMILAR TO ___
)
-- Value when pattern matched
THEN ___
-- Value when pattern not matched
ELSE ___
END AS morning
FROM
parking_violation;