85. Import of marketing data

Specifies the columns of the result set.

SELECT id, email
FROM users

Filters out duplicate records from the result set.

SELECT DISTINCT(country)
FROM users

If/else logic of SQL.

SELECT CASE WHEN age > 21 THEN 'adult' ELSE 'youngster' END
FROM users

It could be used without ELSE keyword (the default ELSE value is NULL):

SELECT COUNT(CASE WHEN status = 'customer' THEN id END)
FROM users

The CASE statement goes through all conditions and returns the first value when a condition is met (or ELSE value):

SELECT
  CASE
  WHEN age < 12 THEN 'child'
  WHEN age < 21 THEN 'teenager' ELSE 'adult'
  END AS age_category
FROM users

Specifies the tables we’re querying.

SELECT *
FROM purchases

Specifies one or multiple filters.

SELECT *
FROM users
WHERE
  country = 'us'

Returns records that have matching values in both left (the one in FROM clause) and right (the one in INNER JOIN) tables.

SELECT *
FROM purchases p
INNER JOIN users u
  ON u.id = p.user_id

Selects all records from the left table and matching records from the right table.

SELECT *
FROM users u
LEFT JOIN purchases p
  ON p.user_id = u.id

Groups records and returns a row for each group – result of an aggregate function.

SELECT
  country,
  COUNT(*)
FROM users
GROUP BY 1

Sorts records in the result set. The default order is ASC.

SELECT *
FROM purchases
ORDER BY created_at DESC

Sets the max number of records in the result set.

SELECT *
FROM books
LIMIT 10

Filters the records within a range. It’s an inclusive filter, basically a shortcut for >= ... AND <= ...

SELECT *
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-02-01'

Combines two filter conditions. Evaluates to TRUE only if both conditions are TRUE.

SELECT *
FROM users
WHERE
  country = 'us'
  AND status = 'customer'

Combines two filter conditions. Evaluates to TRUE if any condition is TRUE. In a query with many filters make sure to wrap two conditions with OR in brackets to avoid interferance with other filters.

SELECT *
FROM marketing_spends
WHERE
  utm_source = 'twitter'
  OR utm_source = 'facebook'

Filters records by matching a string pattern. Symbol % stands for any character (wildcard).

SELECT *
FROM users
WHERE
  email LIKE '%gmail.com'

Filters records that are present in a list or a subquery.

SELECT *
FROM users
WHERE
  country IN ('us', 'ca', 'au', 'gb')

Evaluates to TRUE if the value is NULL.

SELECT *
FROM users
WHERE
  utm_campaign IS NULL

Evaluates to TRUE if the value is not NULL.

SELECT *
FROM users
WHERE
  utm_campaign IS NOT NULL

Defines a CTE (Common Table Expression) or simply a subquery.

WITH customers AS (
  SELECT *
  FROM users
  WHERE
    status = 'customer'
)

SELECT *
FROM customers

Returns the current timestamp.

SELECT NOW()
-- Example result: 2020-02-22 22:16:16.634526+02

Returns the part of a timestamp or an interval as a number.

SELECT DATE_PART('day', '2020-02-14'::timestamp)
-- Result: 14

Returns the part of a timestamp as a rounded timestamp.

SELECT DATE_TRUNC('day', '2020-02-14'::timestamp)
-- Result: 2020-02-14 00:00:00

Returns a formatted timestamp as a string. Possible patterns that could be used in a format argument:

SELECT TO_CHAR('2020-02-14'::timestamp, 'YYYY-MM')
-- Result: 2020-02

The whole list of patterns could be found on the official PostgreSQL website.

Converts a string into a date.

SELECT TO_DATE('2020-02-14', 'YYYY-MM-DD')
-- Result: 2020-02-14

SELECT TO_DATE('02/14/2020', 'MM/DD/YYYY')
-- Result: 2020-02-14

Returns the absolute value of the number.

SELECT ABS(-100)
-- Result: 100

Returns a random float number between 0 and 1.

SELECT RANDOM()
-- Exampe result: 0.8502873764373362

Rounds a number to a certain precision.

SELECT ROUND(3.141592)
-- Result: 3

SELECT ROUND(3.141592, 4)
-- Result: 3.1416

Round a number to the closest higher integer.

SELECT CEIL(3.1415)
-- Result: 4

Round a number to the closest lower integer.

SELECT FLOOR(3.1415)
-- Result: 3

Truncates the number to a precision without rounding it.

SELECT TRUNC(3.141592, 5)
-- Result: 3.14159

SELECT TRUNC(3.141592)
-- Result: 3

Returns the smallest value from the list of arguments.

SELECT LEAST(2, 1, 3)
-- Result: 1

Returns the highest value from the list of arguments.

SELECT GREATEST(1, 3, 2)
-- Result: 3

Returns the first non-NULL input argument. Used to specify default values.

SELECT COALESCE(NULL, NULL, 'foo', 'bar')
-- Result: foo

Returns NULL if arguments are equal. Returns the first argument otherwise.

SELECT
  NULLIF(123, 123),
  NULLIF('2020-02-14', '2020-02-15')
-- Result: NULL, '2020-02-14'

Gives each record in the partition a number (starting with 1).

SELECT
  ROW_NUMBER() OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Returns the column value (or NULL) from the previous row in the partition (or a number of rows specified by the offset argument).

SELECT
  user_id,
  created_at,
  amount,
  LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at ASC)
FROM purchases

Returns the column value (or NULL) from the next row in the partition (or a number of rows specified by the offset argument).

SELECT
  visitor_id,
  created_at,
  url,
  LEAD(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC)
FROM web_analytics.pageviews

Return value of the first row in the partition.

SELECT
  FIRST_VALUE(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Return value of the last row in the partition.

SELECT
  LAST_VALUE(url) OVER(PARTITION BY visitor_id ORDER BY created_at ASC),
  *
FROM web_analytics.pageviews

Returns the number of rows in the partition. NULL values are ignored.

SELECT
  COUNT(*) OVER (PARTITION BY country),
  *
FROM users

Returns the sum of values in the partition.

SELECT
  SUM(amount) OVER (),
  *
FROM purchases

Returns the average value in the partition.

SELECT
  AVG(age) OVER(PARTITION BY country),
  *
FROM users

Returns the max value in the partition.

SELECT
  MAX(age) OVER(PARTITION BY country),
  *
FROM users

Returns the min value in the partition.

SELECT
  MIN(age) OVER(PARTITION BY country),
  *
FROM users

Returns the number of rows. NULL values are ignored.

SELECT
  country,
  COUNT(*)
FROM users
GROUP BY 1

Returns the sum of values.

SELECT
  SUM(amount)
FROM purchases

Returns the average value.

SELECT
  AVG(age)
FROM users

Returns the max value.

SELECT
  country,
  MAX(age)
FROM users
GROUP BY 1

Returns the min value.

SELECT
  country,
  MIN(age)
FROM users
GROUP BY 1

Combines the results of two queries (stacks two result sets vertically). It filters out duplicate records.

SELECT *
FROM table_1
UNION
SELECT *
FROM table_2

Combines the results of two queries. It keeps duplicate records.

SELECT *
FROM table_1
UNION ALL
SELECT *
FROM table_2

Returns records from the first query that are not returned by the second query. It filters out duplicate records.

SELECT *
FROM table_1
EXCEPT
SELECT *
FROM table_2

Returns records from the first query that are not returned by the second query. It keeps duplicate records.

SELECT *
FROM table_1
EXCEPT ALL
SELECT *
FROM table_2

Returns records that are returned by both the first and second queries. It filters out duplicate records.

SELECT *
FROM table_1
INTERSECT
SELECT *
FROM table_2

Returns records that are returned by both the first and second queries. It keeps duplicate records.

SELECT *
FROM table_1
INTERSECT ALL
SELECT *
FROM table_2

Combines all input strings to one string.

SELECT CONCAT('Hello', ' ', 'world', '!')
-- Result: Hello world!

Returns a number of characters in the input text.

SELECT LENGTH('Hello world!')
-- Result: 12

Changes the case of text to the lower case.

SELECT LOWER('Hello world!')
-- Result: hello world!

Changes the case of text to the upper case.

SELECT LOWER('Hello world!')
-- Result: HELLO WORLD!

Reverses the input text.

SELECT REVERSE('foobar')
-- Result: raboof

Trims the number of characters from the beginning of a text.

SELECT LEFT('foobar', 3)
-- Result: foo

Trims the number of characters from the end of a text.

SELECT RIGHT('foobar', 3)
-- Result: bar

Adds new characters to the beginning of a text input until it has the specified length.

SELECT LPAD('8319', 16, 'X')
-- Result: XXXXXXXXXXXX8319

Adds new characters to the end of a text input until it has the specified length.

SELECT RPAD('1248', 8, '_')
-- Result: 1248____

Splits a text input by a substring into multiple parts and returns the specified part. The parts numbers start with 1.

SELECT SPLIT_PART('foo_bar', '_', 2)
-- Result: bar

Repeats a text input a number of times.

SELECT REPEAT('abc', 3)
-- Result: abcabcabc

Replaces a substring in a text with a new string.

SELECT REPLACE('foobar', 'foo', '')
-- Result: bar

Replaces substrings of a text which match an input pattern with a new string.

SELECT REGEXP_REPLACE('foobar@gmail.com', '\A\w+@', '')
-- Result: gmail.com

Returns the first matched group.

SELECT SUBSTRING('Sally (engineer)', '(\w+)\s\((\w+)\)')
-- Result: Sally

Returns an array of all captured substrings.

SELECT regexp_matches('Sally (engineer)', '(\w+)\s\((\w+)\)')
-- Result: {Sally,engineer}