Anja's SQL Information

The Case (summarized from Mode):

You show up to work Tuesday morning, September 2, 2014. The head of the Product team walks over to your desk and asks you what you think about the latest activity on the user engagement dashboards. You fire them up, and something immediately jumps out. There is a specific dip in the number of engaged users when comparing the end of July to beginning of August. The responsiblility is to determine what caused the dip at the end of the chart and, if appropriate, recommend solutions for the problem.

My Report:

You can check out my mode report here!

The Case (summarized from Mode):

The product team is determining priorities for the next development cycle and they are considering improving the site's search functionality. Before tackling search, the product team wants to make sure that the engineering team's time will be well-spent in doing so. The product team is most interested in determining whether they should even work on search in the first place and, if so, how they should modify it.

My Report:

You can check out my mode report here!

The Case (summarized from Mode):

On July 1, you check the results of the A/B test. You notice that message posting is 50% higher in the treatment group—a huge increase in posting. Your job is to determine whether this feature is the real deal or too good to be true. The product team is looking to you for advice about this test, and you should try to provide as much information about what happened as you can.

My Report:

You can check out my mode report here!

Clauses

The following is in the query clause order it must appear in (only the bold clauses must appear in each search).

SELECT - specify which columns to look at (* means all columns)

FROM - specify which table the data is being pulled from (format of: name of user.name_of_table)

WHERE - filter data based on a certain condition

ORDER BY - reorder results based on the data in columns, can be more than one column (DESC used for descending order; otherwise automatically in ascending order)

LIMIT - to speed up query searches, limit it to the top 100 (LIMIT 100 - Mode does it automatically)

Note: to refer to a column use double quotations (“”) only need to do it when column name is the same as a function name in SQL (example: group)

Clause to be used in combination with other clauses

AS - used for naming a new column. Example:
SELECT west AS west_region
FROM user.housing_info

Logical Operators

AND
OR
NOT
IS NULL
BETWEEN
LIKE - allows search for keywords (case-sensitive)
ILIKE - allows search for keywords (not case-sensitive)

% is a wildcard character that allows searching for strings with any character or set of characters contained where the % is located

IN - look at a list of values rather than writing them as separate statements. It can be for any data type. Example:
WHERE
category IN (‘item one’, ‘item two’, ‘item three’)

BETWEEN - look at a range of values, it can be numerical or a string. Example:
WHERE
name BETWEEN ‘a’
AND ‘m’

Clauses

Continuing the query clause order (from the basics section)

SELECT
DISTINCT - allows the user to see the unique values in a particular column (for two or more columns then they are listed as ordered pairs). It can be used in combination with aggregate functions - primarily:
COUNT(DISTINCT month)
CASE - is like an if/then statement:
WHEN - if this condition is true
THEN - then we will make it this value (can be string, number or boolean)
ELSE - not necessary but use if needed to encompass the rest of the data
END - you must always end the case statement
Two ways to approach it using aggregate functions:
1. with aggregate function:
  SELECT
  CASE WHEN location = 'west' THEN 'west'
  WHEN location = 'east' THEN 'east'
  END AS location_group,
  COUNT(1) AS count
  
  FROM user.housing_info GROUP BY 1
2. inside aggregate function (this pivots the table above) - must put the case statement within the aggregate function. Example:
  SELECT
  COUNT(CASE WHEN location = 'west' THEN 1 END) AS west_count,
  COUNT(CASE WHEN location = 'east' THEN 1 END) AS east_count,
  FROM user.housing_info
FROM
JOIN - let’s the user combine different (or the same) tables
INNER JOIN (can be written as just JOIN) - is the intersection between the two tables; any rows that are not in both tables will be disregarded
Outer joins - allows inclusion of rows that are not found in both tables. Broken up into:
LEFT JOIN (or OUTER LEFT JOIN) - returns unmatched rows from left table (first one listed)
RIGHT JOIN (or OUTER RIGHT JOIN)- returns unmatched rows from the right table (second one listed). This is not used often because you can get the same results using LEFT JOIN if you switch the order of the tables listed in FROM
FULL JOIN (or FULL OUTER JOIN) - returns unmatched rows from both tables; gives the union of the two tables with matching rows being overlapped. This is not used very often but Mode has suggested the most common use is if it is used in conjunction with aggregations to understand the amount of overlap between two tables
CROSS JOIN - allows the joining of two tables which are not related to each other. This will produce a table that has every permutation of rows from each table
Note: You can give a table an alias by typing the variable after a space beside the table name
Joining with multiple keys - this can add accuracy (and sometimes speed) to the query. AND is used with sets of keys being defined in order to join with multiple keys

ON - says which columns should be used to map the tables (the columns are called: "foreign keys" or "join keys"). This must be included with JOIN for any inner joins.
Note: any conditions put in the ON clause will be applied to the separate tables before the joining of them

UNION - allows the user to stack two tables one on top of the other. Different from FULL JOIN because it does not look for similarities based on common values within a column, but will drop full rows in the second table if they are identical to the first table. In order to use UNION, the following two conditions must be satisfied: both tables must have the same number of columns, and the columns must have the same data types in the same order as the first table
UNION ALL - is used to append tables together without any rows being dropped (even if the rows are the same)

WHERE (using JOIN) - will apply the condition(s) after the join; don’t forget WHERE can filter NULL values as a byproduct unintentionally

GROUP BY - allows user to group data based on a specific column(s) (must be used in conjunction with an aggregate function)

HAVING - like WHERE except allows you to filter on aggregate columns

ORDER BY
LIMIT

Aggregate functions

COUNT counts how many rows are in a particular column
SUM adds together all the values in a particular column
MIN and MAX return the lowest and highest values in a particular column
AVG calculates the average of a group of selected values

This section looks at extending the tools learnt in the basics and intermediate levels.

- There are a lot of data types; W3Schools lists them
- Common types:
  String: VARCHAR
  Date/Time: TIMESTAMP
  Number: DOUBLE PRECISION
  Boolean: BOOLEAN
- To change a data type use any of the following:
  CAST(column_name AS type)
  column_name :: type
  CONVERT( type, ‘string’)
- Date formats are YYYY-MM-DD
  The difference between two dates can be found if they are subtracted, however, be sure the data types are TIMESTAMP (if they aren’t be sure to cast them)
  INTERVAL - defined using terms like '10 seconds' or '5 months'. The follow example returns a week added to the time interval given:
  column_name :: timestamp + INTERVAL '1 week'
  NOW() - function which returns the current date and time
  The following will also give the current date/time:
  CURRENT_DATE
  CURRENT_TIME
  CURRENT_TIMESTAMP
  LOCALTIME
  LOCALTIMESTAMP
  CURRENT_TIME AT TIME ZONE 'timezone_from_list'
  
  Timezones listed here
  
  Note: it is important to take care if importing from Excel as dates in Excel might not be formatted this way
Here I will be looking at the various functions that can be used. It would take a long time to show every combination of the functions, so I will write the function with parameters needed along with a couple of more complex examples where the functions are combined.
- LEFT(string/column_name, number of characters) : used to get a substring from counting characters from the left side
- RIGHT(string/column_name, number of characters) : used to get a substring from counting characters from the right side
- SUBSTR(string/column_name, starting character position, number of characters) : takes a substring from any position. Example that will be used quite often with dates is if you are given the format 01/31/2014 08:00:00 AM +0000, here is how you can make it into the SQL date format(YYYY-MM-DD):
  SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2)
- LENGTH(string/column_name) : returns number of characters.
- TRIM(direction ‘characters to remove’ FROM string/column_name) : removes characters. Direction can be: 'leading' (beginning), 'trailing' (end), or 'both'
- POSITION(‘character/substring’ IN string/column_name) or STRPOS(string/column_name,‘character/substring’) : both specify a substring first occurring location (counted with character spaces). Both are case sensitive
- CONCAT(string/column_name, 'string to be inserted ', string/column_name) : used to combine strings. It can be used with more than just 3 arguments.
  Also || can be used to combine to form a string. Example:
  string/column_name || 'string to be inserted ' || string/column_name
- UPPER(string/column_name) : returns the string in uppercase.
- LOWER(string/column_name) : returns the string in lowercase.
- EXTRACT(‘part_of_date’ FROM date/column_name) : used to pull parts of the date out. ‘part_of_date’ can be 'year', 'month', 'day', 'hour', 'minute', 'second', 'decade', or 'dow' (stands for day of the week)
- DATE_TRUNC(‘part_of_date’, date/column_name) : rounds date to the ‘part_of_date’ specified
- COALESCE(column_name, ‘string/number’) : replaces nulls in column
Subqueries can be helpful in improving the performance of queries.
- Using subquery within the FROM: treat any subquery as it’s own query - meaning if must be able to be run on it’s own. Then give it an alias and you may use it in the outer query. Example, to calculate the average number of crime occurrences each month by category:
  SELECT
  sub.category,
  AVG(sub.monthly_count)
  FROM (
  SELECT
  category,
  EXTRACT('month' FROM cleaned_date) AS month,
  COUNT(incidnt_num) AS monthly_count
  FROM tutorial.sf_crime_incidents_cleandate
  GROUP BY 1,2
  ) sub
  GROUP BY 1
You can use subqueries in conditional logic (in conjunction with WHERE, JOIN/ON, or CASE).
- Subquery within WHERE:
  SELECT *
  FROM tutorial.sf_crime_incidents_2014_01
  WHERE Date = (SELECT MIN(date)
  FROM tutorial.sf_crime_incidents_2014_01
  )
  IN is the only type of conditional logic that will work when the inner query contains multiple results. Example where the inner query allows the selection of the bottom 5 dates to be returned from the whole table:
  SELECT *
  FROM user.table
  WHERE Date IN (SELECT date
  FROM user.table
  ORDER BY date
  LIMIT 5
  )
  Note: that you should not include an alias when you write a subquery in a conditional statement because they are considered value(s), not tables
- Subquery with JOIN: is powerful because it allows filtering to a greater degree than WHERE. Example for getting a list of all of the 3 least occurring crime categories:
  SELECT
  *
  sub.category_count AS total_incidents_by_category
  FROM
  tutorial.sf_crime_incidents_cleandate crime
  JOIN (
  SELECT
  category,
  COUNT(incidnt_num) AS category_count
  FROM tutorial.sf_crime_incidents_cleandate
  GROUP BY 1
  ORDER BY 2
  LIMIT 3
  ) category_sub
  ON crime.category = category_sub.category
- Subquery with UNION: is particularly useful when you want to get data for all rows when a dataset is separated in different tables (usually occurs when pulling data from Excel)
Allows aggregate functions to be used without GROUP BY.
- OVER : is used to establish a window
- PARTITION BY : breaks tables into parts to evaluate the aggregate function over. The aggregate will be calculated once per partition
- ORDER BY : will run the aggregate function over each distinct row within the ORDER BY column specified
- ROW_NUMBER() : returns row number; usually used after the data is ordered in some way
- RANK() : returns the rankings of each row. The ranking numbers are skipped after multiple of the same (e.g. 1, 2, 2, 2, 5, 6, …)
- DENSE_RANK() : returns the rankings of each row. The ranking numbers are not skipped after multiple of the same (e.g. 1, 2, 2, 2, 3, 4, …)
- NTILE(# of buckets) : gives percentile (or quartile, or any other subdivision) a given row falls into
  Note: if using percentile but the amount of rows is less than 100, it will return a row number based on rank
- LAG(‘column_name’, number of rows before) : pulls from previous rows
  LEAD(‘column_name’, number of rows after) : pulls from following rows
  Example for looking at previous runner’s time:
  LAG(duration_seconds, 1) OVER (PARTITION BY start_terminal ORDER BY duration_seconds) AS previous_time
- WINDOW : window alias - allows defining the window following OVER clause to be reused within the same query. Example:
  WINDOW ntile_window AS
  (PARTITION BY start_terminal ORDER BY duration_seconds)
  
  Note: WINDOW clause comes after WHERE
Even more window functions!
The purpose being to save time and resources with each query.

Factors that can be controlled to reduce cost of a query:
- Table size: The more rows you have, the slower the query will run
- Joins: If a join creates a substantial increase in the number of rows, this will likely slow down your query
- Aggregations: combining rows and performing operations tends to increase the time required for a query to run
Things that can be done to mitigate the amount of time/resources spent on each query:
- Limiting dates: if possible look at a smaller range of dates and then expand to a greater set - if needed
- Run intermediate steps on smaller subsets of data
- Limit results: limiting results doesn't speed it up if aggregating into one row, so use subqueries to limit before using aggregate function. This is only used to check if you are doing the query correctly, the results will not be a good indication of your whole dataset!
- EXPLAIN : written at start of query to determine cost of steps in query. Best practice is to run EXPLAIN, and then modify the steps that have the best cost (if possible) and rerun EXPLAIN to see if improvements were made
If data is to be pivoted based on aggregate function, use CASE (as shown previously):
SUM(CASE WHEN year = 'FR' THEN players ELSE NULL END) AS fr,
SUM(CASE WHEN year = 'SO' THEN players ELSE NULL END) AS so,
SUM(CASE WHEN year = 'JR' THEN players ELSE NULL END) AS jr,
SUM(CASE WHEN year = 'SR' THEN players ELSE NULL END) AS sr

Given table that is to be pivoted completely (switch rows and columns):
1. List the values that you want to be rows using VALUE :
  FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
  (2007),(2008),(2009),(2010),(2011),(2012)) v(year)
2. Then CROSS JOIN to the original:
  SELECT years.*,
  earthquakes.*
  FROM tutorial.worldwide_earthquakes earthquakes
  CROSS JOIN (
  SELECT year
  FROM (VALUES (2000),(2001),(2002),(2003),(2004),(2005),(2006),
  (2007),(2008),(2009),(2010),(2011),(2012)) v(year)
  
  ) years
3. To collect all like years together, add CASE :
  CASE year
  WHEN 2000 THEN year_2000
  WHEN 2001 THEN year_2001
  WHEN 2002 THEN year_2002
  WHEN 2003 THEN year_2003
  WHEN 2004 THEN year_2004
  WHEN 2005 THEN year_2005
  WHEN 2006 THEN year_2006
  WHEN 2007 THEN year_2007
  WHEN 2008 THEN year_2008
  WHEN 2009 THEN year_2009
  WHEN 2010 THEN year_2010
  WHEN 2011 THEN year_2011
  WHEN 2012 THEN year_2012
  ELSE NULL END
  AS number_of_earthquakes

Anja Wu

Mode Analytics Training

The Case (summarized from Mode):

My Report:

The Case (summarized from Mode):

My Report:

The Case (summarized from Mode):

My Report:

SQL Syntax

Clauses

Clause to be used in combination with other clauses

Logical Operators

Clauses

Aggregate functions