Common SQL Queries

Grow uses PostgreSQL, and you can find more information on available queries in the official PostgreSQL documentation. We also recommend this tutorial site.

The PopSQL website also has some good examples of how to get different date ranges using PostgreSQL.

When using the INTERVAL, DATE_PART, or DATE_TRUNC functions, here are the commonly-used date ranges:

second, minute, hour, day, week, month, quarter, year.

Date Ranges

Last 30 Days:

SELECT *
 FROM report
 WHERE "date_column"  >= date_trunc('DAY',NOW()) - INTERVAL '30 DAYS'

Start of Month:

SELECT *
 FROM report
 WHERE DATE_TRUNC('month', "date_column")

Only last week:

SELECT *
 FROM report
 WHERE "date_column" = date_trunc('WEEK', NOW()) - INTERVAL '1 week'

Only last month:

SELECT *
 FROM report
 WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '1 months';

Prior month (the month before last):
You can change the last 2 months to be any number of months back.

SELECT *
 FROM report
 WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '2 months';

Last month to now:

SELECT *
 FROM report
 WHERE "date_column" >= date_trunc('MONTH', NOW()) - INTERVAL '1 months' 

Only last year:
You can change the last 1 year to be any number of years back.

SELECT *
 FROM report
 WHERE DATE_TRUNC('YEAR', "date_column") = date_trunc('YEAR', NOW()) - INTERVAL '1 year';

Current month, last year:

SELECT *
 FROM report
 WHERE DATE_TRUNC('MONTH', "date_column") = date_trunc('MONTH', NOW()) - INTERVAL '1 year' 

Last year to date (to the same day last year):

SELECT *
 FROM report
 WHERE DATE_TRUNC('day', "date_column") <= DATE_TRUNC('day', "now"()) - INTERVAL '1 year'
 AND DATE_TRUNC('year', "date_column") = DATE_TRUNC('year', "now"()) - INTERVAL '1 year'

or

SELECT *
 FROM report
 WHERE "date_column" between (date_trunc('year', now() - interval '1 Year')) and (now() - interval '1 year');

Find the Difference Between Two Dates Using Date_Part

Most commonly used to calculate the number of days between two dates.

SELECT *,
DATE_PART('day', "end_date" - "start_date") as "days_between" 
FROM report

or

SELECT *,
"end_date"::DATE - "start_date"::DATE AS "days_between"
FROM report

Getting the Month name from a Date

This will extract the month name:
to_char(current_date,'Month') = July

This will extract the month abbreviation from a date:
to_char(current_date,'Mon') = Jul

Convert a Decimal Time to MM:SS

If you are getting a time duration (like the "Average Session Duration" from Google Analytics, but it comes in as a decimal (like 1.50 = 1:30) then use this SQL:

SELECT *,
to_char(to_timestamp("time_duration_column"), 'MI:SS') AS "Time Duration"
FROM report;

SUBSTRING()

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
 substring("columnheader",[?start position?],[length]) as "columnheader" 
FROM report

Examples:

SELECT
 substring("orderDate",1,10) as "newDate" 
FROM report

^ This function would take the data, start at the first character 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
 substring("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 "Date"
 SUM(CASE WHEN "region" = 'Northwest' THEN "weekend_sales" END) as Northwest 
FROM report
GROuP BY "Date"

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

If you are searching for a specific part of a string, you can you can use the underscore and the percent symbol as wildcards.