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()
`columnHeader` [operator] DATE('now', 'start of month', '-1 month')
AND `column header` [operator] DATE('now', 'start of month')
WHERE `orderDate` BETWEEN DATE('now', 'start of month', '-1 month')
AND DATE('now, 'start of month', '-1 day')
Rolling 2 week period (starting Sundays):
WHERE `orderDate` >= DATE('now', 'weekday 0', '-14 days')
Last month only:
WHERE `orderDate` between DATE('now','start of month', ' -1 month')
AND DATE('now', 'start of month', '-1 day')
December of last year:
WHERE `column` <= date('now', 'start of year', '-1 day')
AND `column` >= date('now', 'start of year', '-1 month')
Current month, last year:
WHERE `column` >= date('now', 'start of month', '-1 year')
AND `column` < date('now', 'start of month', '-11 months')
Most commonly used to calculate the number of days between two dates.
(JULIANDAY(`column name`) - JULIANDAY(`other column name`)) as days_between
Used to create a substring, or a shortened string. Most commonly used to shorten a date timestamp to YYYY-MM-DD or YYYY-MM.
substr(`columnheader`,[start position],[end position]) as ` columnheader`
substr(`orderDate`,1,10) as `newDate`
* 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
substr(`datecolumn`,1,7) as SalesMonth
* 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 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.
WHEN 'x happens'
THEN 'y happens'
ELSE 'orignal_column_header' END AS alias
SELECT `LastName`, `FirstName`,
CASE `LastName` WHEN 'ashby' THEN 'villanueva'
ELSE 'LastName`' END as newLastName
* 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.
This statement is summing up any week end sales found in the Northwest sales region.
SUM(CASE WHEN `region` = 'Northwest' THEN `weekend_sales` END) as Northwest
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.
WHEN days_since_refund <= 9 AND refundAmount > 0
THEN refundAmount END) / SUM(total_price_usd) as '0-9'
This statement is creating three different customer categories as well as including other fields from the dataset.
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`
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.