SQL is the language of BI. Grow uses PostgreSQL as our SQL transform language. While you will use tools and data transforms to build many valuable metrics, you will find that for some more complex or unique metrics, you will need to use SQL.
Here at Grow we work hard to eliminate as much SQL work as possible for you in the platform. However, because we also give our users full customizability to manipulate the data as they see fit, it is likely that SQL may be needed to accomplish those particularly custom metrics.
SQL stands for Structured Query Language. You can pronounce it however you want; most people say the letters (ess-que-el) but some people also pronounce it the same was as the word sequel. Either is fine.
SQL is a query language that is used to communicate with databases, where you hold tables of data. You can both update and retrieve data from the database by using SQL.
Syntax
SQL has a few key words that tell the database what to do and where to do it. Below are some of the basic function words.
SELECT: Select statement tells the database to pull data from the location in the table specific in the rest of the query.
FROM: From statement specifies the table(s) to be queried.
WHERE: Where statement selects the data values or rows that will be returned, based on the criteria you give it. This works as a filter and it is optional to include.
Data Definition Language like alter, create, insert, drop, crosstab etc. are not supported in Grow, even though the language is technically PostgreSQL. This is language that is typically used for manipulating the structure of a database and this code will run if a client tries it in a sample database or their own. But within Grow if a client tries to apply this code in a SQL Transform, it will simply return a blank table.
Data Manipulation Language like select, with, recursive, etc. are supported in Grow. We have specifically programmed Grow to only accept this type of language since we are more traditionally querying/manipulating data within Grow, rather than defining.
Sometimes you might have to "cast" a field as a specific data type. The common data types you might use are text, numeric, and date. To do this, add two colons and the data type after each column you want to cast, like this: "number_columm"::numeric.