Articles in this section

Common SQLite Queries

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'​) A​ ND​ ​`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`​ E​ND​) 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.

Related articles