In this article, I’ll give a very quick overview of SQL and provide code snippets for the most frequent scenarios a marketer may face on a daily basis. I’m using PostgreSQL as a reference language, and you can find a playground for the dataset in the following link: https://www.db-fiddle.com/f/4HvQoKyrRyRFWDfou3extv/0
SQL (= Structured Query Language) has been a de-facto standard for data storage and processing for many years since its introduction in 1970s. Currently, it’s being widely used across multiple applications in business and technology thanks to its versatility, speed and simplicity.
All the data that is being stored in SQL is arranged in tables that are grouped into databases depending on the purpose. Within your company’s data systems this may look as follows:
*customers.purchase_data*
, where customers is an indicator of a database, purchase_data is an indicator of a table.
To request data from a particular table, SQL utilizes command FROM that looks like this:
FROM customers.purchase_data
A boilerplate format to request data from the entire table would look like this:
SELECT *
FROM customers.purchase_data
, where command SELECT is used to specify what you would like to extract from the database; and the * symbol is a built-in command for selecting all the data from all the columns. It is frequently dangerous to run this command for very large data tables as it will just download them in its entirety. Instead, if you need to take a quick look to see how data in the table looks like, it will be a good idea to use command LIMIT in the end to indicate, how many rows of data you would want to extract. For example, the following command will return first 200 rows of the data table:
SELECT *
FROM customers.purchase_data
LIMIT 200 -- value 200 can be anything depending on how much info you want to see
Now, when the table contains >1M rows, we would want to start filtering data to include only the datapoints we are interested in. Before proceeding, let me introduce the table called purchase_data that we will use as a reference across the article: