This is the accompanying class "notes" for the O'Reilly Online Training Intermediate

SQL for Data Analysis.

Section I - Setup
To set up, you will need two things: SQLiteStudio and the files for this class (which
you likely have already if you are reading this document).

SQLiteStudio can be downloaded from its website: [Link]

act=download

The files for this class can be downloaded here:

[Link]

Import the "thunderbird_manufacturing.db" database file, which we will be using for

almost all of the examples.

Section II - Subqueries, Unions, and

Advanced Aggregations
2.1A - Scalar Subqueries
Get all orders on the last day there were orders

SELECT * FROM CUSTOMER_ORDER

WHERE ORDER_DATE = (SELECT MAX(ORDER_DATE) FROM CUSTOMER_ORDER)

2.1B - Multi-value Subqueries

Sometimes it can be helpful to leverage subqueries that return a set of values, rather
than one scalar value. For instance, to query customer orders for customers in TX, we
can save ourselves a join and use a subquery to get CUSTOMER_ID's that belong to
customers in TX. Then we can leverage that with a WHERE and specify an IN condition:

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY

FROM CUSTOMER_ORDER

WHERE CUSTOMER_ID IN (
SELECT CUSTOMER_ID
FROM CUSTOMER
WHERE STATE = 'TX'
)

2.2 - Correlated Subqueries

Retrieving the average of quantity by each record's CUSTOMER_ID and PRODUCT_ID

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
(
SELECT AVG(QUANTITY)
FROM CUSTOMER_ORDER c2
WHERE c2.CUSTOMER_ID = c1.CUSTOMER_ID
AND c2.PRODUCT_ID = c1.PRODUCT_ID
) AS AVG_QUANTITY
FROM CUSTOMER_ORDER c1

Depending on how they are used, subqueries can be more expensive or less expensive
than joins. Subqueries that generate a value for each record tend to me more
expensive, like the example above.

2.3 - Derived Tables

A more efficient way to bring in averages by CUSTOMER_ID and PRODUCT_ID is by deriving
a table of these averages, and joining to it.

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ORDER.CUSTOMER_ID,
ORDER_DATE,
CUSTOMER_ORDER.PRODUCT_ID,
QUANTITY,
cust_avgs.avg_qty

FROM CUSTOMER_ORDER
INNER JOIN
(
SELECT CUSTOMER_ID,
PRODUCT_ID,
AVG(QUANTITY) as avg_qty
FROM CUSTOMER_ORDER
GROUP BY 1, 2
) cust_avgs
ON CUSTOMER_ORDER.CUSTOMER_ID = cust_avgs.CUSTOMER_ID
AND CUSTOMER_ORDER.PRODUCT_ID = cust_avgs.PRODUCT_ID

2.4 Common Table Expressions

You can actually create Common Table Expressions to "re-use" one or more derived
tables. These can be helpful to simply provide names to derived tables, and simplify
your queries greatly.

WITH CUST_AVGS AS (
SELECT CUSTOMER_ID,
PRODUCT_ID,
 AVG(QUANTITY) AS AVG_QTY
FROM CUSTOMER_ORDER
GROUP BY 1, 2
)

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ORDER.CUSTOMER_ID,
ORDER_DATE,
CUSTOMER_ORDER.PRODUCT_ID,
QUANTITY,
AVG_QTY

FROM CUSTOMER_ORDER INNER JOIN CUST_AVGS

ON CUSTOMER_ORDER.CUSTOMER_ID = cust_avgs.CUSTOMER_ID
AND CUSTOMER_ORDER.PRODUCT_ID = cust_avgs.PRODUCT_ID

For instance, we can create two derived tables "TX_CUSTOMERS" and "TX_ORDERS" but give
them names as common table expressions. Then we can proceed to use those two derived
tables like this.

WITH TX_CUSTOMERS AS
(
SELECT * FROM CUSTOMER
WHERE STATE = 'TX'
),

TX_ORDERS AS
(
SELECT * FROM CUSTOMER_ORDER
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM TX_CUSTOMERS)
)

SELECT * FROM TX_ORDERS INNER JOIN TX_CUSTOMERS

ON TX_ORDERS.CUSTOMER_ID = TX_CUSTOMERS.CUSTOMER_ID

Conditional Subqueries

SELECT * FROM CUSTOMER c

WHERE EXISTS (SELECT * FROM CUSTOMER_ORDER WHERE CUSTOMER_ID = c.CUSTOMER_ID)

2.5 - Unions
To simply append two queries (with identical fields) together, put a UNION ALL
between them.

SELECT
'FEB' AS MONTH,
PRODUCT.PRODUCT_ID,
PRODUCT_NAME,
SUM(PRICE * QUANTITY) AS REV
FROM PRODUCT LEFT JOIN CUSTOMER_ORDER
ON PRODUCT.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID

WHERE ORDER_DATE BETWEEN '2017-02-01' AND '2017-02-28'

GROUP BY 1,2,3

UNION ALL

SELECT
'MAR' AS MONTH,
PRODUCT.PRODUCT_ID,
PRODUCT_NAME,
SUM(PRICE * QUANTITY) AS REV
FROM PRODUCT LEFT JOIN CUSTOMER_ORDER
ON PRODUCT.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

GROUP BY 1,2,3

Using UNION instead of UNION ALL will remove duplicates, which should not be
necessary in this case.

You should strive not to use unions as they often encourage bad, inefficient SQL.
Strive to use CASE statements or other tools instead. In this example, it would have
been better to do this:

SELECT
CASE
WHEN ORDER_DATE BETWEEN '2017-02-01' AND '2017-02-28' THEN 'FEB'
WHEN ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31' THEN 'MAR'
END AS MONTH,
PRODUCT.PRODUCT_ID,
PRODUCT_NAME,
SUM(PRICE * QUANTITY) AS REV
FROM PRODUCT LEFT JOIN CUSTOMER_ORDER
ON PRODUCT.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID

WHERE ORDER_DATE BETWEEN '2017-02-01' AND '2017-03-31'

GROUP BY 1,2,3

2.6 - GROUP CONCAT

A neat little trick you can do on some database platforms (like SQLite, MySQL, and
PostgreSQL) is the group_concat() aggregate function. This will concatenate all
values in a column as an aggregation, and can be used in conjunction with a GROUP BY
like MIN, MAX, AVG, etc.

This shows a concatenated list of values of PRODUCT_ID's ordered for each ORDER_DATE.

SELECT ORDER_DATE,
group_concat(PRODUCT_ID) as product_ids_ordered
FROM CUSTOMER_ORDER
WHERE ORDER_DATE BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY ORDER_DATE

Putting the DISTINCT keyword inside of it will only concatenate the DISTINCT product
ID's.

SELECT ORDER_DATE,
group_concat(DISTINCT PRODUCT_ID) as product_ids_ordered

FROM CUSTOMER_ORDER
WHERE ORDER_DATE BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY ORDER_DATE

GROUP_CONCAT is a helpful function to compress the results into a single record, in a

single cell, often in a reporting context.

Note that GROUP_CONCAT is used in MySQL and SQLite, but is often called STRING_AGG
on other platforms such as Oracle, PostgreSQL, and Microsoft SQL Server.

Exercise 1
Bring in all records for CUSTOMER_ORDER , but also bring in the total quantities ever
ordered each given PRODUCT_ID and CUSTOMER_ID .

ANSWER:

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ORDER.CUSTOMER_ID,
ORDER_DATE,
CUSTOMER_ORDER.PRODUCT_ID,
QUANTITY,
sum_qty

FROM CUSTOMER_ORDER
INNER JOIN
(
SELECT CUSTOMER_ID,
PRODUCT_ID,
SUM(QUANTITY) AS sum_qty
FROM CUSTOMER_ORDER
GROUP BY 1, 2
) total_ordered

ON CUSTOMER_ORDER.CUSTOMER_ID = total_ordered.CUSTOMER_ID
AND CUSTOMER_ORDER.PRODUCT_ID = total_ordered.PRODUCT_ID

Or if you choose to use a common table expression...

WITH total_ordered AS (
SELECT CUSTOMER_ID,
PRODUCT_ID,
 SUM(QUANTITY) AS sum_qty
FROM CUSTOMER_ORDER
GROUP BY 1, 2
)

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ORDER.CUSTOMER_ID,
ORDER_DATE,
CUSTOMER_ORDER.PRODUCT_ID,
QUANTITY,
sum_qty

FROM CUSTOMER_ORDER INNER JOIN total_ordered

ON CUSTOMER_ORDER.CUSTOMER_ID = total_ordered.CUSTOMER_ID
AND CUSTOMER_ORDER.PRODUCT_ID = total_ordered.PRODUCT_ID

Section III - Regular Expressions

Regular expressions are a powerful tool for qualifying complex text patterns. Their
usage extends far outside of SQL and can be found on many technology platforms
including Python, R, Java, .NET, LibreOffice, Alteryx, and Tableau.

While regular expressions can be used to split and search text, we will primarily be
using it to match text, much like wildcards.

The REGEXP operator is used in SQLite for matching a text to a regex pattern. Like all
boolean operations, it will return a 1 for true or 0 for false.

Here are some interactive training sessions on O'Reilly's platform to learn regular
expressions. [Link]

There is also this short article teaching regular expressions on O'Reilly:

[Link]

Note carefully that the REGEXP operator is not implemented by default with
SQLite. SQLiteStudio does implement it for you, so it will work while using
SQLiteStudio. However, if you use SQLite with Python, Java, or other programming
platforms you will have to implement it yourself.

3.1 - Literals
Literals are characters in a regex pattern that have no special function, and
represent that character verbatim. Numbers and letters are literals. For example, The
regex TX will match the string TX

SELECT 'TX' REGEXP 'TX' --true

Some characters, as we have seen, have special functionality in a regex. If you want
to use these characters as literals, sometimes you have to escape them with a
preceding \ . These characters include:

[\^$.|?*+()
So to qualify a U.S. currency amount, you will need to escape the dollar sign $ and
the decimal place .

SELECT '$181.12' REGEXP '\$181\.12' -- true

3.1 - Qualifying Alphabetic and Numeric Ranges

A range is a valid set of values for a single character. For instance [A-Z]
qualifies one uppercase alphabetic value for the range of letters A thru Z, so [A-Z]
[A-Z] would qualify two uppercase alphabetic text values. This would match the text
string TX .

SELECT 'TX' REGEXP '[A-Z][A-Z]' --true

SELECT '45' REGEXP '[A-Z][A-Z]' --false
SELECT 'T2' REGEXP '[A-Z][0-3]' --true
SELECT 'T9' REGEXP '[A-Z0-9][A-Z0-9]' --true

TX would not match [A-Z][A-Z][A-Z] though because it is not three characters.

SELECT 'TX' REGEXP '[A-Z][A-Z][A-Z]' --false

SELECT 'ASU' REGEXP '[A-Z][A-Z][A-Z]' --true

We can also specify certain characters, and they don't necessarily have to be ranges:

SELECT 'A6' REGEXP '[ATUX][469]' --true

SELECT 'B8' REGEXP '[ATUX][469]' --false

Conversely, we can negate a set of characters by starting the range with ^ :

SELECT 'A6' REGEXP '[^ATUX][^469]' --false

SELECT 'B8' REGEXP '[^ATUX][^469]' --true

3.2 - Anchoring
If you don't want partial matches but rather full matches, you have to anchor the
beginning and end of the String with ^ and $ respectively.

For instance, [A-Z][A-Z] would qualify with SMU . This is because it found two
alphabetic characters within those three characters.

SELECT 'SMU' REGEXP '[A-Z][A-Z]' --true

If you don't want that, you will need to qualify start and end anchors, which
effectively demands a full match when both are used:

SELECT 'SMU' REGEXP '^[A-Z][A-Z]$' --false

You can also anchor to just the beginning or end of the string to check, for instance,
if a string starts with a number followed by an alphabetic character:

SELECT '9FN' REGEXP '^[0-9][A-Z]' --true

SELECT 'RFX' REGEXP '^[0-9][A-Z]' --false
3.3 - Repeaters
Sometimes we simply want to qualify a repeated pattern in our regular expression. For
example, this is redundant:

SELECT 'ASU' REGEXP '^[A-Z][A-Z][A-Z]$' --true

We can instead explicitly identify in curly brackets we want to repeat that alphabetic
character 3 times, by following it with a {3} .

SELECT 'ASU' REGEXP '^[A-Z]{3}$' --true

We can also specify a min and max number of repetitions, such as a minimum of 2 but
max of 3.

SELECT 'ASU' REGEXP '^[A-Z]{2,3}$' --true

SELECT 'TX' REGEXP '^[A-Z]{2,3}$' --true

Leaving the second argument blank will result in only requiring a minimum of
repetitions:

SELECT 'A' REGEXP '^[A-Z]{2,}$' --false

SELECT 'ASDIKJFSKJJNXVNJGTHEWIROQWERKJTX' REGEXP '^[A-Z]{2,}$' --true

To allow 1 or more repetitions, use the + . This will qualify with 1 or more
alphanumeric characters.

SELECT 'ASDFJSKJ4892KSFJJ2843KJSNBKW' REGEXP '^[A-Z0-9]+$' --true

SELECT 'SDFJSDKJF/&SSDKJ$#SDFKSDFKJ' REGEXP '^[A-Z0-9]+$' --false

To allow 0 or more repetitions, use the *

SELECT 'ASDFJSKJ4892KSFJJ2843KJSNBKW' REGEXP '^[A-Z0-9]*' --true

SELECT '' REGEXP '^[A-Z0-9]*' --true

To allow 0 or 1 repetitions (an optional character), follow the item with a ? . This
will allow two characters to be preceded with a number, but it doesn't have to:

SELECT '9FX' REGEXP '^[0-9]?[A-Z]{2}$' --true

SELECT 'AX' REGEXP '^[0-9]?[A-Z]{2}$' --true

You can use several repeaters for different clauses in a regex. Below, we qualify a
string of alphabetic characters, a dash - followed by a string of numbers, and then
another - with a string of alphabetic characters.

SELECT 'ASJSDFH-32423522-HUETHNB' REGEXP '^[A-Z]+-[0-9]+-[A-Z]+$' --true

3.4 Wildcards
A dot . represents any character, even whitespaces.

SELECT 'A-3' REGEXP '...' --true

You can also use it with repeaters to create broad wildcards for any number of
characters.

SELECT 'A-3' REGEXP '.{3}' --true

SELECT 'A-3' REGEXP '.+' --true
SELECT 'A-3' REGEXP '.*' --true

.* is a common way to express qualifying any text.

3.5 Alternation and Grouping

You can group up parts of a regular expression using rounded paranthesis ( ) , often
to put a repeater on that entire group. For example, we can make the entire decimal
part of a dollar amount optional:

SELECT '181.12' REGEXP '^[0-9]+(\.[0-9]{2})?$' --true

SELECT '181' REGEXP '^[0-9]+(\.[0-9]{2})?$' --true
``

We can also qualify a string of letters, a slash `/`, and a string of numbers, but
qualify any number of repetitions of this entire pattern:

```sql
SELECT 'WHISKY/23482374/ZULU/23423234/FOXTROT/6453' REGEXP '^([A-Z]+/[0-9]+/?)+$' --
true

The pipe | operator functions as an alternator operator, or effectively an OR . It

allows you to qualify any number of regular expressions where at least one of them
must be true:

SELECT 'ALPHA' REGEXP '^(FOXTROT|ZULU|ALPHA|TANGO)$' --true

3.7 Using Regular Expressions in queries

Find all customers with an address ending in "Blvd" or "St":

SELECT * FROM CUSTOMER

WHERE ADDRESS REGEXP '.*(Blvd|St)$'

## EXERCISE

Find all customers with a 3-4 digit street number. Note the literal space before the
wildcard `.*`:

```sql
SELECT * FROM CUSTOMER
WHERE ADDRESS REGEXP '^[0-9]{3,4} .*$'

# Section IV - Advanced Joins and Temporary Tables

## 4.1 Inner Join Review

Using an `INNER JOIN`, you can view `CUSTOMER` and `PRODUCT` information with each
`CUSTOMER_ORDER`.

```sql
SELECT CUSTOMER_ORDER_ID,
CUSTOMER_NAME,
ORDER_DATE,
PRODUCT_ID,
PRODUCT_NAME,
PRODUCT_GROUP
QUANTITY,
PRICE

FROM CUSTOMER_ORDER

INNER JOIN CUSTOMER

ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

INNER JOIN PRODUCT

ON CUSTOMER_ORDER.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID

4.2 Left Join Review

Total quantity sold of all products, null means no products were sold that day.

SELECT PRODUCT_ID,
PRODUCT_NAME,
SUM(QUANTITY) as total_quantity

FROM PRODUCT LEFT JOIN CUSTOMER_ORDER

ON PRODUCT.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID
AND ORDER_DATE = '2017-03-01'

GROUP BY 1, 2

4.3 Creating a Volatile Table

Herei show to create a volatile/temporary table of discount rules. This table will
dispose at the end of each session. It is no different than a standard CREATE TABLE
statement other than the TEMP keyword.

CREATE TEMP TABLE DISCOUNT (

CUSTOMER_ID_REGEX VARCHAR (20) NOT NULL DEFAULT ('.*'),
PRODUCT_ID_REGEX VARCHAR (20) NOT NULL DEFAULT ('.*'),
PRODUCT_GROUP_REGEX VARCHAR (30) NOT NULL DEFAULT ('.*'),
STATE_REGEX VARCHAR (30) NOT NULL DEFAULT ('.*'),
DISCOUNT_RATE DOUBLE NOT NULL
);
INSERT INTO DISCOUNT (STATE_REGEX, DISCOUNT_RATE) VALUES ('LA|OK', 0.20);
INSERT INTO DISCOUNT (PRODUCT_GROUP_REGEX, STATE_REGEX, DISCOUNT_RATE) VALUES
('BETA|GAMMA','TX', 0.10);
INSERT INTO DISCOUNT (PRODUCT_ID_REGEX, CUSTOMER_ID_REGEX, DISCOUNT_RATE) VALUES
('^[379]$', '^(1|6|12)$', 0.30);

Note you can also create a temporary (or permanent) table from a SELECT query. This is
helpful to persist expensive query results and reuse it multiple times during a
session. SQLite is a bit more convoluted to do this than other platforms:

CREATE TEMP TABLE ORDER_TOTALS_BY_DATE AS

WITH ORDER_TOTALS_BY_DATE AS (
SELECT ORDER_DATE,
SUM(QUANTITY) AS TOTAL_QUANTITY
FROM CUSTOMER_ORDER
GROUP BY 1
)
SELECT * FROM ORDER_TOTALS_BY_DATE

4.4 Joining with Regular Expressions

Left-joining to the temporary table and qualifying on the regular expressions for each
respective field allows us to apply the discounts to each CUSTOMER_ORDER as
specified.

SELECT CUSTOMER_ORDER.*,
PRICE,
DISCOUNT_RATE,
PRICE * (1 - DISCOUNT_RATE) AS DISCOUNTED_PRICE

FROM CUSTOMER_ORDER
INNER JOIN CUSTOMER
ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

INNER JOIN PRODUCT

ON CUSTOMER_ORDER.PRODUCT_ID = PRODUCT.PRODUCT_ID

LEFT JOIN DISCOUNT

ON CUSTOMER_ORDER.CUSTOMER_ID REGEXP DISCOUNT.CUSTOMER_ID_REGEX
AND CUSTOMER_ORDER.PRODUCT_ID REGEXP DISCOUNT.PRODUCT_ID_REGEX
AND PRODUCT.PRODUCT_GROUP REGEXP DISCOUNT.PRODUCT_GROUP_REGEX
AND [Link] REGEXP DISCOUNT.STATE_REGEX

WHERE ORDER_DATE BETWEEN '2017-03-26' AND '2017-03-31'

If you expect records to possibly get multiple discounts, then sum the discounts and
GROUP BY everything else:

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_NAME,
STATE,
ORDER_DATE,
CUSTOMER_ORDER.PRODUCT_ID,
PRODUCT_NAME,
PRODUCT_GROUP
QUANTITY,
PRICE,
SUM(DISCOUNT_RATE) as TOTAL_DISCOUNT_RATE,
PRICE * (1 - SUM(DISCOUNT_RATE)) AS DISCOUNTED_PRICE

FROM CUSTOMER_ORDER
INNER JOIN CUSTOMER
ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

INNER JOIN PRODUCT

ON CUSTOMER_ORDER.PRODUCT_ID = CUSTOMER_ORDER.PRODUCT_ID

LEFT JOIN DISCOUNT

ON CUSTOMER_ORDER.CUSTOMER_ID REGEXP DISCOUNT.CUSTOMER_ID_REGEX
AND CUSTOMER_ORDER.PRODUCT_ID REGEXP DISCOUNT.PRODUCT_ID_REGEX
AND PRODUCT.PRODUCT_GROUP REGEXP DISCOUNT.PRODUCT_GROUP_REGEX
AND [Link] REGEXP DISCOUNT.STATE_REGEX

WHERE ORDER_DATE BETWEEN '2017-03-26' AND '2017-03-31'

GROUP BY 1,2,3,4,5,6,7,8

4.5A Self Joins

We can join a table to itself by invoking it twice with two aliases. This can be
useful, for example, to look up the previous day's order quantity (if any) for a given
CUSTOMER_ID and PRODUCT_ID :

SELECT o1.CUSTOMER_ORDER_ID,
o1.CUSTOMER_ID,
o1.PRODUCT_ID,
o1.ORDER_DATE,
[Link],
[Link] AS PREV_DAY_QUANTITY

FROM CUSTOMER_ORDER o1
LEFT JOIN CUSTOMER_ORDER o2

ON o1.CUSTOMER_ID = o2.CUSTOMER_ID
AND o1.PRODUCT_ID = o2.PRODUCT_ID
AND o2.ORDER_DATE = date(o1.ORDER_DATE, '-1 day')

WHERE o1.ORDER_DATE BETWEEN '2017-03-05' AND '2017-03-11'

Note if you want to get the previous quantity ordered for that record's given
CUSTOMER_ID and PRODUCT_ID , even if it wasn't strictly the day before, you can use a
subquery instead that qualifies previous dates and orders them descending. Then you
can use LIMIT 1 to grab the most recent at the top.
SELECT ORDER_DATE,
PRODUCT_ID,
CUSTOMER_ID,
QUANTITY,
(
SELECT QUANTITY
FROM CUSTOMER_ORDER c2
WHERE c1.ORDER_DATE > c2.ORDER_DATE
AND c1.PRODUCT_ID = c2.PRODUCT_ID
AND c1.CUSTOMER_ID = c2.CUSTOMER_ID
ORDER BY ORDER_DATE DESC
LIMIT 1
) as PREV_QTY
FROM CUSTOMER_ORDER c1

4.5B Recursive Self Joins

At some point of your career, you may encounter a table that is inherently designed to
be self-joined. For instance, run this query:

SELECT * FROM EMPLOYEE

This is a table containing employee information, including their manager via a

MANAGER_ID field. Here is a sample of the results below.

ID FIRST_NAME LAST_NAME TITLE DEPARTMENT MANAGER_ID

13 Pembroke Killgus Accountant I Accounting 10

14 Harper Argontt Director Operations 3

15 Fabio Treversh Manager Operations 14

16 Gerard Morforth Analyst Operations 15

17 Stephanus Palatino Senior Analyst Operations 15

18 Jennilee Withers Analyst Operations 15

19 Desdemona Farmar Business Consultant Operations 15

20 Ashlin Creamen Manager Operations 14

21 Daniel Licquorish Analyst Operations 20

This MANAGER_ID points to another EMPLOYEE record. If you want to bring in Daniel
and his superior's information, this isn't hard to do with a self join.

SELECT e1.FIRST_NAME,
e1.LAST_NAME,
[Link],
e2.FIRST_NAME AS MANAGER_FIRST_NAME,
e2.LAST_NAME AS MANAGER_LAST_NAME

FROM EMPLOYEE e1 INNER JOIN EMPLOYEE e2

ON e1.MANAGER_ID = [Link]

WHERE e1.FIRST_NAME = 'Daniel'

FIRST_NAME LAST_NAME TITLE MANAGER_FIRST_NAME MANAGER_LAST_NAME

Daniel Licquorish Analyst Ashlin Creamen

But what if you wanted to display the entire hierarchy above Daniel? Well shoot, this
is hard because now you have to do several self joins to daisy-chain your way to the
top. What makes this even harder is you don't know how many self joins you will need
to do. For cases like this, it can be helpful to leverage recursive queries.

A recursion is a special type of common table expression (CTE). Typically, you "seed"
a starting value and then use UNION or UNION ALL to append the results of a query
that uses each "seed", and the result becomes the next seed.

In this case, we will use a RECURSIVE common table expression to seed Daniel's ID,
and then append each MANAGER_ID of each EMPLOYEE_ID that matches the seed. This will
give a set of ID's for employees hierarchical to Daniel. We can then use these ID's to
navigate Daniel's hierarchy via JOINS, IN, or other SQL operators.

-- generates a list of employee ID's hierarchical to Daniel

WITH RECURSIVE hierarchy_of_daniel(x) AS (

SELECT 21 -- start with Daniel's ID
UNION ALL -- append each manager ID recursively
SELECT MANAGER_ID
FROM hierarchy_of_daniel INNER JOIN EMPLOYEE
ON [Link] = hierarchy_of_daniel.x -- employee ID must equal previous recursion
)

SELECT * FROM EMPLOYEE

WHERE ID IN hierarchy_of_daniel;

Recursive queries are a bit tricky to get right, but practice them if you deal
frequently with hierarchical records. You will likely use them with a specific part of
the hierarchy in focus (e.g. Daniel's superiors). It's harder to show the hierarchy
for everyone at once, but there are ways. For instance, you can put a RECURSIVE
operation in a subquery and use GROUP_CONCAT .

SELECT e1.* ,

(
WITH RECURSIVE hierarchy_of(x) AS (
SELECT [Link]
UNION ALL -- append each manager ID recursively
SELECT MANAGER_ID
FROM hierarchy_of INNER JOIN EMPLOYEE
ON [Link] = hierarchy_of.x -- employee ID must equal previous recursion
)

SELECT GROUP_CONCAT(ID) FROM EMPLOYEE e2

 WHERE ID IN hierarchy_of
) AS HIERARCHY_IDS

FROM EMPLOYEE e1

ID FIRST_NAME LAST_NAME TITLE DEPARTMENT MANAGER_ID HIERARCHY_I

14 Harper Argontt Director Operations 3 1,3,14

15 Fabio Treversh Manager Operations 14 1,3,14,15

16 Gerard Morforth Analyst Operations 15 1,3,14,15,1

Senior
17 Stephanus Palatino Operations 15 1,3,14,15,1
Analyst

18 Jennilee Withers Analyst Operations 15 1,3,14,15,1

Business
19 Desdemona Farmar Operations 15 1,3,14,15,1
Consultant

20 Ashlin Creamen Manager Operations 14 1,3,14,20

21 Daniel Licquorish Analyst Operations 20 1,3,14,20,2

Senior
22 Quill Pinder Operations 20 1,3,14,20,2
Analyst

Business
23 Maybelle Freiburger Operations 20 1,3,14,20,2
Consultant

Business
24 Angelique Havis Operations 20 1,3,14,20,2
Consultant

25 Lyn Geale Director Technology 4 1,4,25

26 Tammy Eakly Manager Help Desk 25 1,4,25,26

Technician
27 Junie Blanque Help Desk 26 1,4,25,26,2
I

Note recursive queries also can be used to improvise a set of consecutive values
without creating a table. For instance, we can generate a set of consecutive integers.
Here is how you create a set of integers from 1 to 1000.

WITH RECURSIVE my_integers(x) AS (

SELECT 1
UNION ALL
SELECT x + 1
FROM my_integers
WHERE x < 1000
)
SELECT * FROM my_integers

Generating integers can also be be helpful to "repeat-and-modify" records in a given

table. For example, say we have a table of air travel bookings where each booking can
have "x" number of passengers (such as 3 passengers).
 BOOKING_ID BOOKED_EMPLOYEE_ID DEPARTURE_DATE ORIGIN DESTINATION FARE_PRI

1 6 2017-03-01 DFW ORD 170

2 6 2017-03-04 ORD DFW 160

3 19 2017-03-21 DFW JFK 210

4 19 2017-03-24 JFK DFW 220

5 1 2017-03-26 DFW LAX 180

6 1 2017-03-27 LAX DFW 190

7 5 2017-03-27 DFW ORD 210

8 5 2017-03-29 ORD DFW 190

9 9 2017-03-28 DFW SFO 220

10 9 2017-03-28 SFO DFW 230

11 31 2017-04-01 DFW LAX 190

12 31 2017-04-05 LAX DFW 180

We can break up each booking into individual bookings for each passenger (e.g create 3
records off of a booking with 3 passengers).

WITH RECURSIVE repeat_helper(x) AS (

SELECT 1
UNION ALL
SELECT x + 1
FROM repeat_helper
WHERE x < 1000
)

SELECT BOOKING_ID,
BOOKED_EMPLOYEE_ID,
DEPARTURE_DATE,
ORIGIN,
DESTINATION,
FARE_PRICE,
repeat_helper.x AS PASSENGER_NUMBER
FROM EMPLOYEE_AIR_TRAVEL CROSS JOIN repeat_helper
ON repeat_helper.x <= NUM_OF_PASSENGERS

BOOKING_ID BOOKED_EMPLOYEE_ID DEPARTURE_DATE ORIGIN DESTINATION FARE_PRI

1 6 2017-03-01 DFW ORD 170

1 6 2017-03-01 DFW ORD 170

2 6 2017-03-04 ORD DFW 160

2 6 2017-03-04 ORD DFW 160

3 19 2017-03-21 DFW JFK 210

 3 19 2017-03-21 DFW JFK 210

3 19 2017-03-21 DFW JFK 210

4 19 2017-03-24 JFK DFW 220

4 19 2017-03-24 JFK DFW 220

4 19 2017-03-24 JFK DFW 220

5 1 2017-03-26 DFW LAX 180

6 1 2017-03-27 LAX DFW 190

7 5 2017-03-27 DFW ORD 210

You can also use some clever CASE expression logic with an integer generator to find
total costs of sending employees to each airport.

WITH RECURSIVE repeat_helper(x) AS (

SELECT 1
UNION ALL
SELECT x + 1
FROM repeat_helper
WHERE x < 1000
)

SELECT
CASE WHEN repeat_helper.x == 1 THEN ORIGIN ELSE DESTINATION END AS AIRPORT,
SUM(FARE_PRICE * NUM_OF_PASSENGERS) AS AIRPORT_REVENUE
FROM EMPLOYEE_AIR_TRAVEL CROSS JOIN repeat_helper
ON repeat_helper.x <= 2
GROUP BY AIRPORT

AIRPORT AIRPORT_REVENUE

DFW 4840

JFK 1290

LAX 740

ORD 1460

SFO 1350

You can apply the same concept to generate a set of chronological dates. This
recursive query will generate all dates from today to '2030-12-31':

WITH RECURSIVE my_dates(x) AS (

SELECT date('now')
UNION ALL
SELECT date(x, '+1 day')
FROM my_dates
WHERE x < '2030-12-31'
)
SELECT * FROM my_dates

4.6 Cross Joins

Sometimes it can be helpful to generate a "cartesian product", or every possible
combination between two or more data sets using a CROSS JOIN. This is often done to
generate a data set that fills in gaps for another query. Not every calendar date has
orders, nor does every order date have an entry for every product, as shown in this
query:

SELECT ORDER_DATE,
PRODUCT_ID,
SUM(QUANTITY) as TOTAL_QTY

FROM CUSTOMER_ORDER

GROUP BY 1, 2

We should use a cross join to resolve this problem. For instance, we can leverage a
CROSS JOIN query to generate every possible combination of PRODUCT_ID and
CUSTOMER_ID .

SELECT
CUSTOMER_ID,
PRODUCT_ID
FROM CUSTOMER
CROSS JOIN PRODUCT

In this case we should bring in CALENDAR_DATE and cross join it with PRODUCT_ID to
get every possible combination of calendar date and product. Note the CALENDAR_DATE
comes from the CALENDAR table, which acts as a simple list of consecutive calendar
dates. Note we could also have used a recursive query, as shown in the previous
example, to generate the dates. We'll stick with a simple table instead for now in
case you are not comfortable with recursion yet. We should only filter the calendar to
a date range of interest, like 2017-01-01 and 2017-03-31 .

SELECT
CALENDAR_DATE,
PRODUCT_ID
FROM PRODUCT
CROSS JOIN CALENDAR
WHERE CALENDAR_DATE BETWEEN '2017-01-01' and '2017-03-31'

Then we can LEFT JOIN to our previous query to get every product quantity sold by
calendar date, even if there were no orders that day:

SELECT CALENDAR_DATE,
all_combos.PRODUCT_ID,
TOTAL_QTY

FROM
(
 SELECT
CALENDAR_DATE,
PRODUCT_ID
FROM PRODUCT
CROSS JOIN CALENDAR
WHERE CALENDAR_DATE BETWEEN '2017-01-01' and '2017-03-31'
) all_combos

LEFT JOIN
(
SELECT ORDER_DATE,
PRODUCT_ID,
SUM(QUANTITY) as TOTAL_QTY

FROM CUSTOMER_ORDER

GROUP BY 1, 2
) totals

ON all_combos.CALENDAR_DATE = totals.ORDER_DATE
AND all_combos.PRODUCT_ID = totals.PRODUCT_ID

ORDER BY CALENDAR_DATE, all_combos.PRODUCT_ID

Note you can also use common table expressions:

WITH all_combos AS (
SELECT
CALENDAR_DATE,
PRODUCT_ID
FROM PRODUCT
CROSS JOIN CALENDAR
WHERE CALENDAR_DATE BETWEEN '2017-01-01' and '2017-03-31'
),

totals AS (
SELECT ORDER_DATE,
PRODUCT_ID,
SUM(QUANTITY) as TOTAL_QTY

FROM CUSTOMER_ORDER

GROUP BY 1, 2
)

SELECT CALENDAR_DATE,
all_combos.PRODUCT_ID,
TOTAL_QTY

FROM all_combos LEFT JOIN totals

ON all_combos.CALENDAR_DATE = totals.ORDER_DATE
AND all_combos.PRODUCT_ID = totals.PRODUCT_ID

ORDER BY CALENDAR_DATE, all_combos.PRODUCT_ID

4.7 Comparative Joins

Note also you can use comparison operators in joins. For instance, we can self-join to
create rolling quantity totals and generate a cartesian product on previous dates to
the current order, and then sum those quantities. It is much easier to use windowing
functions for this purpose though, which is covered in the next section.

SELECT c1.ORDER_DATE,
c1.PRODUCT_ID,
c1.CUSTOMER_ID,
[Link],
SUM([Link]) as ROLLING_QTY

FROM CUSTOMER_ORDER c1 INNER JOIN CUSTOMER_ORDER c2

ON c1.PRODUCT_ID = c2.PRODUCT_ID
AND c1.CUSTOMER_ID = c2.CUSTOMER_ID
AND c1.ORDER_DATE >= c2.ORDER_DATE

GROUP BY 1, 2, 3, 4

Exercise 4
For every CALENDAR_DATE and CUSTOMER_ID , show the total QUANTITY ordered for the
date range of 2017-01-01 to 2017-03-31 :

ANSWER:

SELECT CALENDAR_DATE,
all_combos.CUSTOMER_ID,
coalesce(TOTAL_QTY, 0) AS TOTAL_QTY

FROM
(
SELECT
CALENDAR_DATE,
CUSTOMER_ID
FROM CUSTOMER
CROSS JOIN CALENDAR
WHERE CALENDAR_DATE BETWEEN '2017-01-01' and '2017-03-31'
) all_combos

LEFT JOIN
(
SELECT ORDER_DATE,
CUSTOMER_ID,
SUM(QUANTITY) as TOTAL_QTY
 FROM CUSTOMER_ORDER

GROUP BY 1, 2
) totals

ON all_combos.CALENDAR_DATE = totals.ORDER_DATE
AND all_combos.CUSTOMER_ID = totals.CUSTOMER_ID

ORDER BY CALENDAR_DATE, all_combos.CUSTOMER_ID

Using Common Table Expressions:

WITH all_combos AS (
SELECT
CALENDAR_DATE,
CUSTOMER_ID
FROM CUSTOMER
CROSS JOIN CALENDAR
WHERE CALENDAR_DATE BETWEEN '2017-01-01' and '2017-03-31'
),

totals AS (
SELECT ORDER_DATE,
CUSTOMER_ID,
SUM(QUANTITY) as TOTAL_QTY

FROM CUSTOMER_ORDER

GROUP BY 1, 2
)

SELECT CALENDAR_DATE,
all_combos.CUSTOMER_ID,
coalesce(TOTAL_QTY, 0) AS TOTAL_QTY

from all_combos LEFT JOIN totals

ON all_combos.CALENDAR_DATE = totals.ORDER_DATE
AND all_combos.CUSTOMER_ID = totals.CUSTOMER_ID

ORDER BY CALENDAR_DATE, all_combos.CUSTOMER_ID

Section V - Windowing
Windowing functions allow you to greater contextual aggregations in ways much more
flexible than GROUP BY. Many major database platforms support windowing functions.

5.1 PARTITION BY
Sometimes it can be helpful to create a contextual aggregation for each record in a
query. Windowing functions can make this much easier and save us a lot of subquery
work.

For instance, it may be helpful to not only get each CUSTOMER_ORDER for the month of
MARCH, but also the average quantity that customer purchased for that PRODUCT_ID . We
can do that with an OVER PARTITION BY combined with the AVG() function. We have done
this operation with derived tables and common table expressions in the past, but now
we can do it in a one-line window function!

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
AVG(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as AVG_PRODUCT_QTY_ORDERED

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

Here is the maximum quantity for shared customers and products:

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
MAX(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as MAX_PRODUCT_QTY_ORDERED

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

Each MAX_PRODUCT_QTY_ORDERED will only be the minimum QUANTITY of that given

record's PRDOUCT_ID and CUSTOMER_ID . The WHERE will also filter that scope to only
within MARCH .

You can have multiple windowed fields in a query. Below, we get a MIN, MAX, and AVG
for that given window.

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
MIN(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as MIN_PRODUCT_QTY_ORDERED,
MAX(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as MAX_PRODUCT_QTY_ORDERED,
AVG(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as AVG_PRODUCT_QTY_ORDERED

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

You can also mix and match scopes which is difficult to do with derived tables.

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
MIN(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID) as
MIN_PRODUCT_CUSTOMER_QTY_ORDERED,
MIN(QUANTITY) OVER(PARTITION BY PRODUCT_ID) as MIN_PRODUCT_QTY_ORDERED,
MIN(QUANTITY) OVER(PARTITION BY CUSTOMER_ID) as MIN_CUSTOMER_QTY_ORDERED

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

When you are declaring your window redundantly, you can reuse it using a WINDOW
declaration, which goes between the WHERE and the ORDER BY .

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
MIN(QUANTITY) OVER(w) as MIN_PRODUCT_QTY_ORDERED,
MAX(QUANTITY) OVER(w) as MAX_PRODUCT_QTY_ORDERED,
AVG(QUANTITY) OVER(w) as AVG_PRODUCT_QTY_ORDERED

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

WINDOW w AS (PARTITION BY PRODUCT_ID, CUSTOMER_ID)

ORDER BY CUSTOMER_ORDER_ID

5.2 ORDER BY
You can also use an ORDER BY in your window to only consider values that
comparatively come before that record.

5.2A USING ORDER BY

For instance, you can get a ROLLING_TOTAL of the QUANTITY by ordering by the
ORDER_DATE.

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER (ORDER BY ORDER_DATE) AS ROLLING_QUANTITY

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

Note you can precede the ORDER BY clause with a DESC keyword to window in the
opposite direction.

5.2B Ordering and Bounds

Above, notice our example output has the same rolling total for all records on a given
date. This is because the ORDER BY in a window function by default does a logical
boundary, which in this case is the ORDER_DATE . This means it is rolling up
everything on that ORDER_DATE and previous to it. A side effect is all records with
the same ORDER_DATE are going to get the same rolling total.

This is the default behavior our query did previously:

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER (ORDER BY ORDER_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW) AS ROLLING_QUANTITY

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

If you want to incrementally roll the quantity by each row's physical order (not
logical order by the entire ORDER_DATE ), you can use ROWS BETWEEN instead of RANGE
BETWEEN .

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER (ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW) AS ROLLING_QUANTITY

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

Note the AND CURRENT ROW is a default, so you can shorthand it like this:

SUM(QUANTITY) OVER (ORDER BY ORDER_DATE ROWS UNBOUNDED PRECEDING) AS ROLLING_QUANTITY

In this particular example, you could have avoided using a physical boundary by
specifying your window with an ORDER BY CUSTOMER_ORDER_ID . But we covered the
previous strategy anyway to see how to execute physical boundaries. Here is an
excellent overview of windowing functions and bounds:
[Link]

5.3 Mixing PARTITION BY / ORDER BY

We can combine the PARTITION BY / ORDER BY to create rolling aggregations partitioned
on certain fields.

5.3A Simple MAX over PARITION and ORDER BY

For example, for each record we can get the max quantity ordered up to that date

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
MAX(QUANTITY) OVER(PARTITION BY PRODUCT_ID, CUSTOMER_ID ORDER BY ORDER_DATE) as
MAX_TO_DATE_PRODUCT_QTY

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

5.3B Rolling Total Quantity by PRODUCT_ID, with physical boundary

SELECT CUSTOMER_ORDER_ID,
ORDER_DATE,
CUSTOMER_ID,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER(PARTITION BY PRODUCT_ID ORDER BY ORDER_DATE ROWS UNBOUNDED
PRECEDING) as total_qty_for_customer_and_product

FROM CUSTOMER_ORDER
WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'
You need to be very careful mixing PARITITION BY with an ORDER BY that uses a physical
boundary! If you sort the results, it can get confusing very quickly because you lose
that physical ordered context.

SELECT CUSTOMER_ORDER_ID,
ORDER_DATE,
CUSTOMER_ID,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER(PARTITION BY PRODUCT_ID ORDER BY ORDER_DATE) as
total_qty_for_product

FROM CUSTOMER_ORDER
WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY ORDER_DATE

5.4 Rolling Windows

You can also use movable windows to create moving aggregations. For instance, you can
create a six-row rolling average (3 rows before, 3 rows after).

SELECT CUSTOMER_ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
PRODUCT_ID,
QUANTITY,
AVG(QUANTITY) OVER (ORDER BY ORDER_DATE ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS
ROLLING_AVG

FROM CUSTOMER_ORDER

WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

5.4 LEAD and LAG

Two highly useful windowing functions are LEAD() and LAG() , which retrieves the
previous or next value within a defined window.

For example, to simply retrive the previous quantity ordered in a table by

ORDER_DATE , we can use the LAG() function like this.

SELECT *,
LAG (QUANTITY, 1, 0) OVER (ORDER BY ORDER_DATE) AS PREV_QTY
FROM CUSTOMER_ORDER

LAG() will retrieve the quantity of the next record on that ordering.

SELECT *,
LEAD (QUANTITY, 1, 0) OVER (ORDER BY ORDER_DATE) AS NEXT_QTY
FROM CUSTOMER_ORDER

You can also use LEAD() and LAG() with a PARTIION BY , making it possible to silo
the previous quantity only within records that share each given record's PRODUCT_ID
and CUSTOMER_ID .

SELECT *,
LAG (QUANTITY, 1, 0) OVER (PARTITION BY PRODUCT_ID, CUSTOMER_ID ORDER BY ORDER_DATE)
AS PREV_QTY

FROM CUSTOMER_ORDER

ORDER BY CUSTOMER_ORDER_ID

5.5 Ranking with ROW_NUMBER()

The ROW_NUMBER() function can be highly helpful with windowing functions to rank
items.

For example, say I wanted to get the top 3 selling PRODUCTs by CUSTOMER. I can use a
ROW_NUMBER() function to assign a ranking number to each sorted quantity by
CUSTOMER_ID and PRODUCT_ID . Then I can filter for only the first three items.

WITH TOTAL_QTYS AS (
SELECT CUSTOMER_ID, PRODUCT_ID, SUM(QUANTITY) AS TOTAL_QTY
FROM CUSTOMER_ORDER
GROUP BY 1,2
),

PRODUCT_SALES_BY_CUSTOMER AS (
SELECT CUSTOMER_ID, PRODUCT_ID, TOTAL_QTY,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY TOTAL_QTY DESC) AS RANKING
FROM TOTAL_QTYS
)
SELECT * FROM PRODUCT_SALES_BY_CUSTOMER
WHERE RANKING <= 3

Note if you want identical values to receive the same ranking, use the RANK()
function instead of row_number() . Use DENSE_RANK() if you want to force the values
to be consecutive rather than dupes causing ranks to be skipped.

EXERCISE
For the month of March, bring in the rolling sum of quantity ordered (up to each
ORDER_DATE ) by CUSTOMER_ID and PRODUCT_ID .

SELECT CUSTOMER_ORDER_ID,
ORDER_DATE,
CUSTOMER_ID,
PRODUCT_ID,
QUANTITY,
SUM(QUANTITY) OVER(PARTITION BY CUSTOMER_ID, PRODUCT_ID ORDER BY ORDER_DATE) as
total_qty_for_customer_and_product

FROM CUSTOMER_ORDER
WHERE ORDER_DATE BETWEEN '2017-03-01' AND '2017-03-31'

ORDER BY CUSTOMER_ORDER_ID

Section VI - SQL with Python, R, and Java

6.1A Using SQL with Python
When doing SQL with Python, you want to use SQLAlchemy. Below, we query and loop
through the CUSTOMER table which is returned as an iteration of Tuples:

from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///thunderbird_manufacturing.db')
conn = [Link]()

stmt = text("SELECT * FROM CUSTOMER")

results = [Link](stmt)

for r in results:
print(r)

6.1B Using SQL with Python

You can package up interactions with a database into helper functions. Below, we
create a function called get_all_customers() which returns the results as a List of
tuples:

from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///thunderbird_manufacturing.db')
conn = [Link]()

def get_all_customers():
stmt = text("SELECT * FROM CUSTOMER")
return list([Link](stmt))

print(get_all_customers())

6.1C Using SQL with Python

If you want to pass parameters to a query, mind to not insert parameters directly so
you don't accidentally introduce SQL injection. Below, we create a helper function
that retrieves a customer for a given ID from a database.
from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///thunderbird_manufacturing.db')
conn = [Link]()

def get_all_customers():
stmt = text("SELECT * FROM CUSTOMER")
return list([Link](stmt))

def customer_for_id(customer_id):
stmt = text("SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = :id")
return [Link](stmt, id=customer_id).first()

print(customer_for_id(3))

6.1D Using SQL with Pandas

Using SQL with Pandas is pretty straightforward. Use Pandas' read_sql_query()
function to query from a connection object and turn it into a DataFrame .

import pandas as pd
import sqlite3

conn = [Link]('thunderbird_manufacturing.db')
data_frame_from_sql = pd.read_sql_query("SELECT * FROM CUSTOMER", conn)
print(data_frame_from_sql)

6.2 Using SQL with R

Here is how to run a SQL query in R, and save the results to a matrix.

setwd('c:\\my_folder')

library(DBI)
library(RSQLite)

db <- dbConnect(SQLite(), dbname='thunderbird_manufacturing.db')

my_query <- dbSendQuery(db, "SELECT * FROM CUSTOMER")

my_data <- dbFetch(my_query, n = -1)

dbClearResult(my_query)

print(my_data)

remove(my_query)
dbDisconnect(db)
You can get detailed information on how to work with R and SQL in the official DBI
documentation:

DBI Interface: [Link]

DBI PDF: [Link]

6-3 Using SQL with Java/Scala/Kotlin

There are many solutions to make a Java, Scala, or Kotlin application work with a
database. The vanilla way we will learn is to use JDBC (Java Database Connection).

Keep in mind there are many solutions and libraries that abstract away SQL operations,
which can be good or bad depending on how much control you want to maintain:

Hibernate - ORM technology that's been around since 2001 and has a mature
implementation. However, Hibernate is notorious for its strange loading
mechanisms, and can be a hindrance if you want to maintain control of how and
when data is loaded from a database.

jOOQ - A more modern (but commercial) ORM that fluently allows working with
databases in a type-safe manner.

Speedment - Another fast turnaround, fluent API that compiles pure Java code
from table schemas to work with databases.

If you are going to go the vanilla JDBC route, it is a good idea to use a connection
pool so you can persist and reuse several connections safely in a multithreaded
environment. HikariCP is a leading option to achieve this and provides an optimal
DataSource implementation, which is Java's recommended interface for a database
connection pool.

A helpful resource to learning how to work with JDBC is Jenkov's in-depth tutorial:
[Link]

6.3A - Selecting Data with JDBC and HikariCP

To connect to a database using JDBC and HikariCP, you will need the appropriate JDBC
drivers for your database platform (e.g. SQLite) as well as Hikari-CP.

dependencies {
compile '[Link]:sqlite-jdbc:3.19.3'
compile '[Link]:HikariCP:2.6.3'
compile 'org.slf4j:slf4j-simple:1.7.25'
}

Below, we create a simple Java application that creates a Hikari data source with a
minimum of 1 connection and a maximum of 5. Then we create a query and loop through
it's ResultSet .

import [Link];
import [Link];

import [Link];
import [Link];
import [Link];
public class JavaLauncher {

public static void main(String[] args) {

try {
HikariConfig config = new HikariConfig();

[Link]("jdbc:sqlite:/c:/git/oreilly_intermediate_sql_for_data/thunderbird_man

[Link](1);
[Link](5);

HikariDataSource ds = new HikariDataSource(config);

Connection conn = [Link]();

Statement stmt = [Link]();
ResultSet rs = [Link]("SELECT * from CUSTOMER");

while ([Link]()) {
[Link]([Link]("CUSTOMER_ID") + " " +
[Link]("CUSTOMER_NAME"));
}

//release connection back to pool

[Link]();

} catch (Exception e) {
[Link]();
}
}
}

6.3B Passing parameters

If you need to pass parameters to your SQL query, avoid concatenating the values into
the SQL string otherwise you will put your application at risk for SQL injection.
Instead, use a PreparedStatement to safely inject the paramters:

import [Link];
import [Link];

import [Link];
import [Link];
import [Link];

public class JavaLauncher {

public static void main(String[] args) {

try {
HikariConfig config = new HikariConfig();
[Link]("jdbc:sqlite:/c:/git/oreilly_intermediate_sql_for_data/thunderbird_man

[Link](1);
[Link](5);

HikariDataSource ds = new HikariDataSource(config);

Connection conn = [Link]();

// Create a PreparedStatement and populate parameter

PreparedStatement stmt = [Link]("SELECT * FROM CUSTOMER
WHERE CUSTOMER_ID = ?");
[Link](1,3);

ResultSet rs = [Link]();

while ([Link]()) {
[Link]([Link]("CUSTOMER_ID") + " " +
[Link]("CUSTOMER_NAME"));
}

//release connection back to pool

[Link]();

} catch (Exception e) {
[Link]();
}
}
}

6-3C Writing Data

You can also use a PreparedStatement to execute updates against the database. The
PreparedStatement has more advanced features like batching to write large volumes of
data, but here is how to insert a single record.

import [Link];
import [Link];

import [Link];
import [Link];
import [Link];

public class JavaLauncher {

public static void main(String[] args) {

try {
HikariConfig config = new HikariConfig();

[Link]("jdbc:sqlite:/c:/git/oreilly_intermediate_sql_for_data/thunderbird_man
 [Link](1);
[Link](5);

HikariDataSource ds = new HikariDataSource(config);

Connection conn = [Link]();

// Create a PreparedStatement and populate parameter

PreparedStatement stmt =
[Link]("INSERT INTO PRODUCT
(PRODUCT_NAME,PRODUCT_GROUP,PRICE) VALUES (?,?,?)");

[Link](1,"Kry Kall");
[Link](2,"BETA");
[Link](3, [Link](35.0));

[Link]();

//release connection back to pool

[Link]();

} catch (Exception e) {
[Link]();
}
}
}