SQL

SQL Workflow

Before we get into anything remotely substantive, we need to discuss how we should approach SQL questions/projects. Without some structure in our approach we’re more likely to miss vital parts of the question. First I’ll show an example and then I’ll explain it.

Suppose we have a question that asks us to take a table of user transactions and output how much a person bought at their last visit to a store. We write in our own words, what the output table should look like after the word ‘WANT’. Include any sorting that needs to take place here. Then we list what we have. If we have multiple tables, we’d list multiple tables and their common IDs. Next, we separate the have and want from the map we use to get there. The map is a sketch of the code we’ll use but in mostly english with a mix of technical terms.

----- WANT: number of products bought in last visit. 
----- i.e. user_id, products bought, last visit
----- SORT BY transaction date earliest to latest
----- HAVE: user transactions 
------------
----- Get count of products bought by transaction date -- 
----- dense_rank the transaction dates descending -- 
----- take latest transaction date -- 
----- do the first in a cte -- 
----- then use where and select latest 

SELECT ....
FROM ...

Generically this looks like:

1. List variable we want

2. Then list datasets we have

3. List the steps we need to take to get from 2 to 1.

Preliminaries

First we’re going to create a few datasets using r. The majority of the tutorial doesn’t use them because the combination of SQL and Quarto is a bit buggy but it’s a useful exercise anyway.

library(odbc)
library(tidyverse)
library(DBI)
library(here)
library(RSQLite)

here::i_am("SQL-published-version.qmd")

sample_data <- tibble(
  id = 1:1000,
  x1 = rnorm(1000, 0, 1),
  x2 = rnorm(1000, 10, 15),
  y = 3 * x1 + 4 * x2 + rnorm(1000, 0, 10),
  g = rbinom(1000, size = 1, prob = 0.3)
)
sample_data2 <- tibble(
  id = 1:1000,
  x3 = rnorm(1000, 15, 30),
  x4 = rnorm(1000, 20, 5),
  y2 = 10 * x3 + 40 * x4 + rnorm(1000, 0, 40),
  g2 = rbinom(1000, size = 1, prob = 0.3)
)

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbWriteTable(conn = mydb, name = "sample", value = sample_data, overwrite = T)
dbWriteTable(conn = mydb, name = "other_sample", value = sample_data2, overwrite = T)

#### Try to load in postgresql - doesn't currently work

# https://caltechlibrary.github.io/data-carpentry-R-ecology-lesson/05-r-and-databases.html
# https://jtr13.github.io/cc21fall2/how-to-integrate-r-with-postgresql.html
# https://solutions.posit.co/connections/db/databases/postgresql/
#https://medium.com/geekculture/a-simple-guide-on-connecting-rstudio-to-a-postgresql-database-9e35ccdc08be

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbListTables(mydb) # returns a list of tables in your database

[1] "other_sample" "sample"      

SELECT, WHERE and Other Basics

The basic syntax is you SELECT variables FROM a database.

\[\underbrace{\text{SELECT }}_{\text{Select vars}} \underbrace{\text{*}}_{\text{* is all variables}}\]

\[\underbrace{\text{FROM }}_{\text{from where}} \underbrace{\text{db\_name}}_{\text{name}}\]

See e.g.,

SELECT * ----- select all 
FROM sample ----- select from this dataframe
LIMIT 10 ----- for demo purposes, limit output to 10 rows

You can run these commands in r using the following syntax. Since you can’t run the sql commands in quarto without compiling, I’ve used this method to check that my sql commands actually work.

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbGetQuery(mydb,'
  select *
  from "sample"
  limit 10
')

   id         x1          x2           y g
1   1  0.2294868  14.3156629  74.7362982 0
2   2  0.6851657  14.1110263  73.5693131 0
3   3 -0.5247317 -12.8748044 -40.5904988 1
4   4  0.3784018   7.0292202  27.0929951 0
5   5 -0.8718342  -0.3591293   1.6870891 0
6   6 -0.2286860  10.9886896  39.6442041 0
7   7  0.3249191   2.3494209  23.1583781 0
8   8 -1.0748827   2.5263681   0.5947356 0
9   9  1.0472492  22.1233829 110.3033277 1
10 10 -1.4071715   2.2942946  14.5204852 0

This can be modified (obviously!). Suppose you need two variables, x1 and x2.

SELECT x1, x2
FROM sample

Here’s a more advanced query where we select rows where var_1 \(> 10\)

SELECT x1, x2
FROM sample
WHERE x2 >= 10

If you need the where to be in a discrete list of things, like selecting actions performed on an iphone or ipad from a database of actions performed on all sorts of devices, then you need WHERE var in ('condition1','condition2')

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbGetQuery(mydb,'
  select "x1","x2"
  from "sample"
  limit 10
')

Often you need summaries or operations by group. This is easy, just add the GROUP BY clause. Additionally you can perform an operation on the groups themselves using HAVING. Below, for example, we take the average of variable 1 by group having var2 \(> 2\)

SELECT avg(var1)
FROM db_name
GROUP BY group_var
HAVING var2 > 0

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbGetQuery(mydb,'
  select avg("x1") ---- note the quoting needed in the R version not the SQL version
  from "sample"
  group by "g"
  having x2 >= 0
')

[1] avg("x1") ---- note the quoting needed in the R version not the SQL version
<0 rows> (or 0-length row.names)

What is the difference? HAVING applies to groups as a whole whereas WHERE applies to individual rows. If you have both, the WHERE clause is applied first, the GROUP BY clause is second - so only the individual rows that meet the WHERE clause are grouped. The HAVING clause is then applied to the output. Then only the groups that meet the HAVING condition will appear.

Suppose you need both:

SELECT AVG(var_3)
FROM db_name
WHERE var1 >= 10
GROUP BY group_var
HAVING var_2 > 5

Above you’ll get the average of variable 3 from \(db\_name\) only for the individual rows where \(var\_1\) is greater than 10 grouped by group_var where, within the groups their associated \(var\_2\) value is greater than 5.

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbGetQuery(mydb,'
  select avg("y")
  from "sample"
  where "x1" >= 3 
  group by "g"
  having x2 >= 0
')

  avg("y")
1 49.22952

Data types

Here are a few common data types. For a full list go to https://www.postgresql.org/docs/current/datatype.html.

Data Types

Data Type	What does it do?
int	signed four-byte integer
numeric	exact number. use when dealing with money
varchar	variable-length character string
time	time of day (no time zone)
timestamp	date and time (no time zone)
date	calendar date (year, month, day)

For a technical discussion of the difference between float4 and float8 see this post: https://stackoverflow.com/questions/16889042/postgresql-what-is-the-difference-between-float1-and-float24.

NULLS

Use IS NOT NULL to get rid of nulls. Usually used after the WHERE clause.

Aliasing

Sometimes you need to alias variables. This is especially necessary when merging as you can overwrite columns that have the same name that aren’t explicitly part of the merge.

SELECT var AS new_var_name
FROM ...

You can also alias data frames - this is useful when you have multiple data frames.

SELECT var AS new_var_name
FROM df1 a 

mydb <- dbConnect(RSQLite::SQLite(), "sample.sqlite")

dbGetQuery(mydb,'
  select "g" as "group", ROUND(avg("y"),2) as "new_average"
  from "sample" "b"
  group by "g"
')

  group new_average
1     0       39.80
2     1       37.17

Converting Data Types

Sometimes the data is in one format and you need it in another. You can use CAST to do this

CAST(variable AS int/numeric/varcar/time)

Sometimes you need to get rid of nulls and coerce them to zeroes, to do that use COALESCE

COALESCE(variable, 0)

Extracting

A lot of times when dealing with dates you’ll need a range or only part of the information given. To extract this data, you need the command extract.

This extracts a year from a date:

EXTRACT(Year from date_var)

This extracts an epoch (i.e. the time difference) between the end date and the start date:

EXTRACT(EPOCH from endvar-startvar)

Suppose, however, that you only want the days in the epoch. That’s surprisingly easy with the following code:

EXTRACT(Day from endvar-startvar)

Aggregate Functions

Note that in the table below all of the functions EXCEPT count ignore null values.

Aggregate Types

Aggregate Fcn	What does it do?
MIN()	returns the smallest value within the selected column
MAX()	returns the largest value within the selected column
COUNT()	returns the number of rows in a set
SUM()	returns the total sum of a numerical column
AVG()	returns the mean value of a numerical column. Getting the median requires PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY var1). See below for more info
GREATEST(var1, var2)	Greatest rowwise among var1 and var2
LEAST(var1, var2)	Least rowwise among var1 and var2

Before we go on, a brief digression on getting the median. For reasons known only to the creators of SQL, getting the median is fantastically difficult. Suppose you want the median as a decimal rounded to the second significant digit. You’d need to write ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY var1)::DECIMAL, 2).¹

The functions MIN() to AVG() operate globally but sometimes you need the biggest/smallest out of a single record (i.e. locally/within a row). You can do that with GREATEST() and LEAST(). Note that these also work on characters. An example will help clarify why this feature is useful.

Suppose you want to see the number of flights between a pair of cities (e.g. Austin and Dallas) but you don’t care about where the plane begins. In this case the command CONCAT(departure_city, '-' arrival_city)² will create 2 separate entries for Austin-Dallas and Dallas-Austin which is not what the question asked for. So you have to use GREATEST and LEAST.

Greatest gets the highest alphabetically/greatest date/number out of a variable PER RECORD (i.e per row). Max gets the most OVER ALL RECORDS. Why does this difference matter? Suppose Abilene and Amarillo are in the data. Then if you used MAX() every row would be in the Abilene and Amarillo group.

Going back to our Dallas/Austin example, GREATEST(departure_city, arrival_city) would give us Austin and LEAST(departure_city, arrival_city) gives us Dallas in a row with a flight from Austin to Dallas or in a row with a flight from Dallas to Austin. In a row with a flight from Austin to London the command would give us Austin and London. So to combine these to create a unique ID, we could type CONCAT(GREATEST(destination_location, source_location),'-',LEAST(destination_location, source_location)) and that would give us Austin-Dallas whenever the these two cities appeared in destination location and source location.

Percentiles

As you could see from above, extracting the median is difficult. If you want a bunch of percentiles, the problem is even worse.

SELECT 
UNNEST(array[0, 0.25, 0.5, 0.75, 1]) AS percentile, 
UNNEST(PERCENTILE_CONT(array[0, 0.25, 0.5, 0.75, 1]) within group (order by var1)) ---- Note that unnest wraps the whole thing!  
FROM ...

You have two options for calculating percentiles PERCENTILE_CONT and PERCENTILE_DISC. PERCENTILE_CONT will interpolate values while PERCENTILE_DISC will give you values only from your data. Suppose you have 2,3,4,5. PERCENTILE_CONT would give you 3.5, PERCENTILE_DISC gives you 3.

Merging

There are four types of joins:

• INNER JOIN/JOIN
  • Joins all common records between tables
• LEFT JOIN
  • Joins the matching records in the right frame (i.e. the one after the LEFT JOIN clause) with all the records in the left frame (i.e. the one after the FROM clause)
• RIGHT JOIN
  • Joins all of the records in the right frame (i.e. the one after the RIGHT JOIN clause) with all the matching records in the left frame (i.e. the one after the FROM clause)
• FULL JOIN
  • All the records in both frames

These joins are all on some variable.

SELECT a.var1, a.var2, a.id, b.var3, b.var4, b.id1 ---- note the aliases up here.  This is good practice to show where you're getting each variable from
FROM df1 a ---- alias dataframe 1 as a
INNER JOIN df2 b ----- alias dataframe 2 as b
ON a.id = b.id

You can use OR in the join to join on either value. AND works too.

SELECT a.var1, a.var2, a.id, a.id2, b.var3, b.var4, b.id1, b.alt_id <---- note the aliases up here.  This is good practice to show where you're getting each variable from
FROM df1 a <---- alias dataframe 1 as a
INNER JOIN df2 b <----- alias dataframe 2 as b
ON a.id = b.id OR a.var1 = b.alt_id <---- this joins if EITHER is true

• UNION
  • Concatenates queries. Does not allow duplicates

SELECT ...
FROM ...
UNION
SELECT ...
FROM ...

• UNION ALL
  • Concatenates queries. Allows duplicates

Window Functions

Suppose you need to do something within a window like find all flights within 3 days. Here you need a window function. The basic syntax is as follows:

\(\underbrace{\dots}_{\text{Some fcn}} \text{OVER} (\)

\(\hspace{0.5cm}\underbrace{\text{PARTITION BY} {\color{blue}{\text{var1}}}}_{\text{group by }{\color{blue}{\text{var1}}}}\)

\(\hspace{0.5cm}\underbrace{\text{ORDER BY} {\color{green}{\text{var2}}}}_{\text{order by }{\color{green}{\text{var2}}}}\) \() \text{ AS newvar}\)

In addition to the functions in Section 9, here are a bunch of useful functions. For a more comprehensive list, go to https://www.postgresql.org/docs/current/functions-window.html

Window Functions

Window Function	What does it do?
lag()	lags the data 1, lag(2) would lag 2 rows
lead()	opposite of lag()
rank()	ranks rows. Suppose you have two rows tied for first, the rankings would go 1,1,3
dense_rank()	ranks rows without skipping.Suppose you have two rows tied for first, the rankings would go 1,1,2
ntile()	splits the data into n groups, indexed by an integer, as equally as possible. ntile(4) for example, gives us quartiles if ordered properly
cume_dist()	cumulative distribution

SELECT lag(var1, 1) OVER  --<---- lag variable 1 
 (  
 ---PARTITION BY group_var --<---- if you include a value here, the lag will be within the group
 ORDER BY date --<---- lag by date globally. 
)
FROM df1

Bounding window functions

Sometimes you need to search within a certain window in a group as opposed to within an entire group. Suppose we wanted a moving average within the last three years. We could do that by properly bounding our query. The bounds come after the ORDER BY clause.

SELECT AVG(var1) OVER  <---- give us the mean of variable 1
 (  
 PARTITION BY country <---- group by country
 ORDER BY year desc <---- order by year descending
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW <---- gives us the last 3 years.  includes the current row.
)
FROM df1

We can be fairly creative with the bounds using the following building blocks:

Bounds	What does it do?
n PRECEDING	2 Preceding gives us the 2 prior rows not including the current row
UNBOUND PRECEDING	All rows up to but not including the current row
CURRENT ROW	Just the current row
n PRECEDING AND CURRENT ROW	n preceding and the current row
n FOLLOWING	n following but not including the current row
UNBOUND FOLLOWING	n preceding and the current row

Conditionals

Sometimes you don’t just need an average, you need a conditional average or sum. This can be done with the FILTER command

sum(var) FILTER(WHERE ...) AS ...

You can create a new variable based on the values of other variables. You do that with CASE WHEN

CASE 
  WHEN [condition] THEN [result]
  WHEN [condition2] THEN [result2]
  ELSE [result3] END AS new_conditional_variable  

Note that the CASE WHEN ... can be used in the GROUP BY command to create groups.

Dates

Dates are difficult to deal with. You just have to memorize these commands.

Datetime Functions

Function	What does it do?
MAKE_DATE(year,month,day)	makes dates
MAKE_TIMESTAMP(year,month,day, hour, minute, second)	makes timestamps
MAKE_INTERVAL(year,month,day, hour, minute, second)	makes intervals
DATE(datetime_var)	extracts dates from datetimes

There are variations. Suppose you wanted to find all processes that lasted less than 10 days. You could use the command MAKE_INTERVAL(days < 10)

Extracting Parts of Dates

Extracting Datetime Elements

Function	What does it do?
to_char(trans_date, 'YYYY-MM')	extracts year and month from a variable called trans_date
extract('month' from trans_date)	extracts month from a variable called trans_date

Neat Question

Here’s a neat little SQL question that asks you to take a databse of transactions and get a count of the number of approved transactions, unapproved transactions, total amount of transactions, total amount of approved transactions while grouping by month and year. Since it ties the last two sections in nicely, it’s worth going over.

Here’s the full code:³

SELECT to_char(trans_date, 'YYYY-MM') as month, -- to_char allows us to extract year and month
country, -- this just gives us country
count(*) as trans_count, -- count number of members of the group of country and year and month (which is specified in the group command)
count(country) FILTER(WHERE state = 'approved') as approved_count, --  a conditional count here - gives us the count of all approved transactions 
sum(amount) AS trans_total_amount, --  gives us sum of all transactions
sum(amount) FILTER(WHERE state = 'approved')AS approved_total_amount --  conditional sum where we sum amount when the state is approved
FROM Transactions 
GROUP BY country, to_char(trans_date, 'YYYY-MM') --  note we only group by country and year-month because we need to extract information about approved and unapprved transactions at this level

Common Table Expressions

Sometimes you need to create a separate table that you can then extract data from to avoid conflicts like using a window function in the WHERE clause. You do this with a common table expression (CTE).

The basic syntax is as follows:

WITH cte1 AS (
SELECT ...
FROM db1
)

SELECT ...
FROM ... (likely db1 or db2)

You’re not limited to one CTE, you can have multiple if you want:

WITH RECURSIVE cte1 AS (
SELECT ...
FROM db1
), ---- need the parenthesis and comma here otherwise you get an error!
cte2 AS (
SELECT ...
FROM db2
)

SELECT ...
FROM ... (likely db1 or db2)

Below is an an example of using CTEs to avoid a window function in the WHERE clause.⁴ The query is from a problem that asks you to find the second longest flight between two cities. This is a bit of a tricky problem because it requires a window function, concatentaion, ordering text strings, and a common table expression.

WITH tmp AS (SELECT 
id, 
destination_location,
source_location,
flight_start,
flight_end,
dense_rank() OVER (
    PARTITION BY CONCAT(GREATEST(destination_location, source_location),'.',LEAST(destination_location, source_location))
    ORDER BY extract(epoch from flight_end - flight_start) desc
) AS flight_duration
FROM flights)

SELECT id, destination_location, source_location, flight_start, flight_end
FROM tmp
where flight_duration = 2
order by id

Let’s go through this, starting with the structure. Here we’re creating a common table expression called cte and then using it

\(\text{WITH } {\color{red}{\text{tmp}}} \text{ AS (} \leftarrow \text{Create CTE called } \color{red}{\text{tmp}}\)
\(\hspace{1cm}\text{SELECT} \dots \leftarrow \text{Standard SQL commands in here}\)
\(\hspace{1cm}\text{FROM} \dots\)
\() \leftarrow \text{Close out CTE}\)

\(\text{SELECT} \dots\)
\(\text{FROM } {\color{red}{\text{tmp}}} \leftarrow \text{Use } {\color{red}{\text{tmp}}} \text{ as a normal table}\)

The other tricky bit is the window function. Here we’re using a dense rank (i.e. ranking everything sequentially so if two are tied for first the ranks are 1,1,2) by city pair (that’s the variable after partition by) ordering those groups by flight time descending.

\(\underbrace{\text{dense\_rank()}}_{\text{rank everything}} \underbrace{\text{OVER (}}_{\text{create the window}}\)

\(\underbrace{\text{PARTITION BY}}_{\text{select grouping var}} \hspace{0.2cm}\underbrace{\text{CONCAT(GREATEST(destination\_location, source\_location),'.',LEAST(destination\_location, source\_location))}}_{\text{group by city pair}}\)

\(\underbrace{\text{ORDER BY}}_{\text{select order var(s)}} \hspace{0.2cm}\underbrace{\text{extract(epoch from flight\_end - flight\_start) desc}}_{\text{order by duration descending}}\)

\() \hspace{0.2cm} \underbrace{\text{AS flight\_duration}}_{\text{Alias this to use later}}\)

Subqueries

Suppose you need a one off query within a query. You can use a sub query! The basic syntax is below. You can put them anywhere. For example, here’s one in the FROM clause. Note that you need to alias your subqueries or else they’ll fail

SELECT ...
FROM (
  SELECT *
  FROM db1
) AS db2

You could, however, do one in the SELECT clause if you wanted

SELECT (
  SELECT ...
  FROM ...
)
FROM db1

So when to use common table expressions versus subtables? Common table expressions are, generally, preferred because they’re more readable and can be used multiple times. See, e.g., below, where we have the same query as Section 14 but with a subquery instead of a common table expression.

SELECT 
id, 
destination_location,
source_location,
flight_start,
flight_end
FROM ( ----- create a subquery that has everything in the initial dataframe AND a rank
    SELECT  ---- (ctd) column that we can use in a where clause because the subquery 
    *,   ----- separates the window function from the where so you can just call the 
dense_rank() OVER ( ---- duration rank column as usual
    PARTITION BY CONCAT(GREATEST(destination_location, source_location),'.',LEAST(destination_location, source_location))
    ORDER BY extract(epoch from flight_end - flight_start) desc) AS duration_rank
    FROM flights
    ) AS subquery

WHERE duration_rank = 2
order by id

Subquery Joins

Sometimes you need to do something to a database before joining it. Here a subquery join is helpful

SELECT user_id, var1, var2 
FROM  db1
LEFT JOIN 
    ( ---- begin subquery 
        SELECT id, var3, var4
        FROM db2
        WHERE ... -----  you're using a subquery because you need to do something so I've included the where in here 
    ) AS a --- end and alias subquery 
ON db1.user_id = a.id ---- join on subquery name!

Text

Sometimes you’re faced with a task where you have to concatenate (i.e. join) or split variables. I’ve seen this problem when trying to match pairs of cities for flights when the question only cares about the pair and not the ordering.

\(\text{CONCAT(var1, var2)}\)

If you need visual separation between the values, you can always do something like:

\(\text{CONCAT(var1, '-', var2)}\)

To undo a split column, use the split_part clause. Be careful here because you need to specify which part of the split you want to take!

\(\underbrace{\text{SPLIT\_PART}}_{\text{split apart}}(\underbrace{{\color{red}{\text{var}}, {\color{blue}{\text{'.'}}}, {\color{green}{\text{1}}}}}_{\text{split apart}{\color{red}{\text{ var}}} \text{ on}{\color{blue}{\text{ a period}}} \text{ taking }{\color{green}{\text{the first instance}}} })\)

Random Sample

The method below selects 10% of the data.

SELECT ...
FROM ...
TABLESAMPLE BERNOULLI(10) --- this is a faster way of sampling

Summary Statistics

Getting summary statistics is a pain the ass. So here is some code that will do it for a variable called \(a\) in database \(t1\). The difficulty of the code is that it requires two separate common table expressions. Here’s a sketch of what that looks like. Note that adding RECURSIVE to the with query allows you to select from db1 in the db2 query.

WITH RECURSIVE cte1 AS (
SELECT ...
FROM db1
),
cte2 AS (
SELECT ...
FROM db2
)

SELECT ...
FROM ... (likely db1 or db2)

WITH RECURSIVE
summary_stats AS
(
 SELECT 
  ROUND(AVG(a), 2) AS mean,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a) AS median,
  MIN(a) AS min,
  MAX(a) AS max,
  MAX(a) - MIN(a) AS range,
  ROUND(STDDEV(a), 2) AS standard_deviation,
  ROUND(VARIANCE(a), 2) AS variance,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY a) AS q1,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY a) AS q3
   FROM t1
),
row_summary_stats AS
(
SELECT 
 1 AS id, 
 'mean' AS statistic, 
 mean AS value 
  FROM summary_stats
union all
SELECT 
 2, 
 'median', 
 median 
  FROM summary_stats
UNION
SELECT 
 3, 
 'minimum', 
 min 
  FROM summary_stats
UNION
SELECT 
 4, 
 'maximum', 
 max 
  FROM summary_stats
UNION
SELECT 
 5, 
 'range', 
 range 
  FROM summary_stats
UNION
SELECT 
 6, 
 'standard deviation', 
 standard_deviation 
  FROM summary_stats
UNION
SELECT 
 7, 
 'variance', 
 variance 
  FROM summary_stats
UNION
SELECT 
 9, 
 'Q1', 
 q1 
  FROM summary_stats
UNION
SELECT 
 10, 
 'Q3', 
 q3 
  FROM summary_stats
UNION
SELECT 
 11, 
 'IQR', 
 (q3 - q1) 
  FROM summary_stats
UNION
SELECT 
 12, 
 'skewness', 
 ROUND(3 * (mean - median)::NUMERIC / standard_deviation, 2) AS skewness 
  FROM summary_stats
)
SELECT * 
 FROM row_summary_stats
  ORDER BY id;

Execution Order

1. JOIN
   • If no JOIN, then we start at FROM
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT/OFFSET

More Efficient Code

• https://nodeteam.medium.com/how-to-optimize-postgresql-queries-226e6ff15f72
• Properly index* columns used in WHERE and JOIN conditions.
  • Index will create a pointer to the actual rows in the specified table.
  • https://www.postgresql.org/docs/current/sql-createindex.html
• Use appropriate data types and avoid unnecessary data type conversions.
• Limit the use of SELECT * and only retrieve the columns you need.
• Minimize the use of subqueries and consider JOIN alternatives.
• Monitor and analyze query performance using database-specific tools and profiling.”

Footnotes

1. If you’re viewing this in raw markdown (say in R Studio) it won’t look like the explanations next to each line of code are comments. If you render it, they will be.

2. See Section 16 for more details

3. If you’re viewing this in raw markdown (say in R Studio) it won’t look like the explanations next to each line of code are comments. If you render it, they will be.

4. See https://www.interviewquery.com/questions/second-longest-flight for full problem details