DATE() Functions
BEST REFERENCE: https://www.sqlite.org/lang_datefunc.html
- Used to format a column and define it as a date.
- Not an operator, not a time stamp. Use this to filter by a dynamic date range.
- Date operators: +/- days, seconds, minutes, hours years, start of year/month, now
- Standard Operators: =, <, >, <=, >=, <>, !=, BETWEEN, CONTAINS, LIKE, IN()
- Weekday:Sun=0,Mon=1...Sat=6
Template:
SELECT *
FROM report
WHERE
`columnHeader` [operator] DATE('now', 'start of month', '-1 month')
AND `column header` [operator] DATE('now', 'start of month')
Example:
SELECT *
FROM report
WHERE `orderDate` BETWEEN DATE('now', 'start of month', '-1 month')
AND DATE('now, 'start of month', '-1 day')
Rolling 2 week period (starting Sundays):
SELECT *
FROM report
WHERE `orderDate` >= DATE('now', 'weekday 0', '-14 days')
Last month only:
SELECT *
FROM report
WHERE `orderDate` between DATE('now','start of month', ' -1 month')
AND DATE('now', 'start of month', '-1 day')
December of last year:
SELECT *
FROM report
WHERE `column` <= date('now', 'start of year', '-1 day')
AND `column` >= date('now', 'start of year', '-1 month')
Current month, last year:
SELECT *
FROM report
WHERE `column` >= date('now', 'start of month', '-1 year')
AND `column` < date('now', 'start of month', '-11 months')
JULIANDAY()
Most commonly used to calculate the number of days between two dates.
Template:
SELECT *,
(JULIANDAY(`column name`) - JULIANDAY(`other column name`)) as days_between
FROM report
SUBSTR()
Used to create a substring, or a shortened string. Most commonly used to shorten a date timestamp to YYYY-MM-DD or YYYY-MM.
Template:
SELECT
substr(`columnheader`,[start position],[end position]) as ` columnheader`
FROM report
Examples:
SELECT
substr(`orderDate`,1,10) as `newDate`
FROM report
* This function would take the data, start at the first character or number and go 10 spaces. Usually the length needed to trim a timestamp date to just the date. 2018-05-09T00:10:54+00:00 to 2018-05-09
SELECT
substr(`datecolumn`,1,7) as SalesMonth
FROM report
* This function would take the data, start at the first character or number and go 7 spaces. Usually the length needed to trim 2018-08-13 to 2018-08 or YYYY-MM-DD to YYYY-MM
CASE Statements
CASE statements are used very frequently with clients when they have certain criteria they’re trying to track in their data. CASE statements allow you apply a label or perform a calculation when a certain criteria is met.
Grow recently released our tagging transform which is a non-SQL way to perform some basic CASE statements. While this will be helpful for many, you will finds some custom calculations that will require actual SQL. A CASE statement creates a new column in the dataset.
Basic Logic:
CASE
WHEN 'x happens'
THEN 'y happens'
ELSE 'orignal_column_header' END AS alias
Example:
SELECT `LastName`, `FirstName`,
CASE `LastName` WHEN 'ashby' THEN 'villanueva'
ELSE 'LastName`' END as newLastName
FROM report
* This CASE statement will show three columns. LastName, FirstName, and then the new column which will replace the name ‘ashby’ with ‘villanueva’ at any instance. If that criteria isn’t met, it will just populate the new column with the name found in the LastName column. The new column will be called “newLastName”
Things to Note About CASE Statements:
- ELSE statements are a good error check. If you CASE everything, and there are still other values, the ELSE statement would provide that value.
- When working with identifying a string, you need to wrap the string in single quotations ‘jake’ , ‘jason’ , ‘sales team’, for the query to identify that string exactly.
- You can use calculations in your CASE statement.
- You can pivot data by using case statements.
Example 1:
This statement is summing up any week end sales found in the Northwest sales region.
SELECT
SUM(CASE WHEN `region` = 'Northwest' THEN `weekend_sales` END) as Northwest
FROM report
Example 2:
This statement is applying less than/greater than logic to a data field to calculate the total refund amount in the last 9 days. Probably being used to build out a basic cohort analysis.
SELECT
SUM(CASE
WHEN days_since_refund <= 9 AND refundAmount > 0
THEN refundAmount END) / SUM(total_price_usd) as '0-9'
FROM report
Example 3:
This statement is creating three different customer categories as well as including other fields from the dataset.
SELECT
Enrollment_Date__c AS 'Enrollment Date',
CASE WHEN `Type` = 'Customer' THEN 1 ELSE 0 END AS Customer,
CASE WHEN `Type` = 'Customer Cancelled' THEN 1 ELSE 0 END AS Cancelled,
CASE WHEN `Type` = 'Customer Cancelled - Trial Period' THEN 1 ELSE 0 END AS `Trial Fallout`
FROM report
Note: If you are searching for a specific part of a string, you can you can use the underscore and the percent symbol as wildcards.