SQL Aggregate and Grouping

# Checks that R and each package are installed and new enough for this course.
# Stops with a list of everything that needs fixing, and how to fix it.
check_course_versions <- function(required, area) {
  problems <- character(0)
  to_install <- character(0)
  found <- character(0)
  for (name in names(required)) {
    need <- required[[name]]
    if (name == "R") {
      have <- getRversion()
      if (have < need) {
        problems <- c(problems, sprintf(
          "R is version %s; this course needs %s or newer. Install the current R from https://cran.r-project.org/ (see https://howisonlab.github.io/datawrangling/current/rtidyverse/rstudio_installation.html), then reinstall your packages.",
          have, need))
      }
      found <- c(found, paste("R", have))
    } else if (!nzchar(system.file(package = name))) {
      problems <- c(problems, sprintf("%s is not installed.", name))
      to_install <- c(to_install, name)
    } else {
      have <- packageVersion(name)
      if (have < need) {
        problems <- c(problems, sprintf(
          "%s is version %s; this course needs %s or newer.", name, have, need))
        to_install <- c(to_install, name)
      } else if (isNamespaceLoaded(name) && getNamespaceVersion(name) != have) {
        problems <- c(problems, sprintf(
          "%s %s is installed, but this R session is still using %s. Restart R.",
          name, have, getNamespaceVersion(name)))
      }
      found <- c(found, paste(name, have))
    }
  }
  if (length(problems) > 0) {
    fix <- "In RStudio choose Session > Restart R"
    if (length(to_install) > 0) {
      fix <- paste0(fix, ", then run this in the Console:\n  install.packages(c(",
                    paste0('"', to_install, '"', collapse = ", "), "))\n")
    }
    stop("Course setup (", area, ") found problems:\n  - ",
         paste(problems, collapse = "\n  - "), "\n\n",
         fix, "\nThen run the setup chunk(s) again.", call. = FALSE)
  }
  cat("Course setup OK (", area, "): ", paste(found, collapse = ", "), "\n", sep = "")
}

check_course_versions(c(
  R = "4.5.0",         # CRAN builds Mac/Windows packages for the current and previous R only
  tidyverse = "2.0.0",
  dplyr = "1.2.0",     # recode_values() and replace_values()
  tidyr = "1.3.0",     # separate_wider_delim()
  gt = "1.0.0",        # needed by gtExtras
  gtExtras = "0.6.1"   # works with ggplot2 4
), "R")

library(tidyverse)
library(gt)
library(gtExtras)

Course setup OK (R): R 4.6.1, tidyverse 2.0.0, dplyr 1.2.1, tidyr 1.3.2, gt 1.3.0, gtExtras 0.6.2

# needs check_course_versions() from the Course setup (R) chunk
check_course_versions(c(
  DBI = "1.0.0",
  duckdb = "1.5.5"     # duckdb(shared_home = FALSE)
), "SQL")

library(DBI)
library(duckdb)
options(duckdb.enable_rstudio_connection_pane = TRUE)

# a temporary in-memory database, nothing saved to a file
drv <- duckdb(shared_home = FALSE)
con <- dbConnect(drv)

Course setup OK (SQL): DBI 1.3.0, duckdb 1.5.6

SQL Aggregate and Group

First we load our toy data on books.

books_with_weights <- tribble(
  ~title, ~series, ~format, ~weight_in_grams,
  "Harry Potter 1", "Harry Potter", "paperback", 1456,
  "Two Towers", "Lord of the Rings","paperback", 1800,
  "Harry Potter 3", "Harry Potter","hardcover", 2604,
  "Harry Potter 2", "Harry Potter","hardcover", 2900,
  "Return of the King", "Lord of the Rings", "hardcover", 1784
)

# Write the dataset to DuckDB
dbWriteTable(con, "books_with_weights", books_with_weights, overwrite = TRUE)

Recall that aggregation takes a set of rows and reduces them to a single value. In SQL this happens in the SELECT clause. Remember that the SELECT clause happens after the WHERE clause … so we are always aggregating the rows that are left.

We can obtain the total weight of all the books using the SUM function in the SELECT clause and an AS alias to name the result. We will build up to that query a step at a time. First, the whole table:

SELECT *
FROM books_with_weights

5 records

title	series	format	weight_in_grams
Harry Potter 1	Harry Potter	paperback	1456
Two Towers	Lord of the Rings	paperback	1800
Harry Potter 3	Harry Potter	hardcover	2604
Harry Potter 2	Harry Potter	hardcover	2900
Return of the King	Lord of the Rings	hardcover	1784

Then just the column we want to aggregate:

SELECT weight_in_grams
FROM books_with_weights

5 records

weight_in_grams
1456
1800
2604
2900
1784

The aggregate function works to reduce this column to a single value.

SELECT SUM(weight_in_grams) AS total_weight
FROM books_with_weights

1 records

total_weight
10544

If we have a WHERE clause this changes the rows that are involved (because SELECT executes after WHERE). First check which rows are left:

SELECT *
FROM books_with_weights
WHERE series = 'Harry Potter'

3 records

title	series	format	weight_in_grams
Harry Potter 1	Harry Potter	paperback	1456
Harry Potter 3	Harry Potter	hardcover	2604
Harry Potter 2	Harry Potter	hardcover	2900

Then aggregate just those rows:

SELECT SUM(weight_in_grams) AS total_weight
FROM books_with_weights
WHERE series = 'Harry Potter'

1 records

total_weight
6960

Three forms of COUNT

In SQL counting is done with COUNT. The grain of the books_with_weights data frame here is “one row represents a book”, so counting the rows produces a count of books. In SQL we count rows using COUNT(*).

SELECT SUM(weight_in_grams) AS total_weight,
       COUNT(*) AS book_count
FROM books_with_weights

1 records

total_weight	book_count
10544	5

SQL has three forms of COUNT, but one is forbidden

1. COUNT(*) which counts the number of rows.
2. COUNT(DISTINCT column) which counts the number of distinct values in a column.
3. COUNT(column) counts the number of values that are not NULL.

COUNT(column) is the forbidden one. I will always grade that as wrong. COUNT(column) is forbidden because it is subject to subtle and confusing errors (and communicates poor understanding of what is being done).

Consider the differences between the three forms of COUNT on this list:

id, name
--------
1, Domhog
2, NULL
3, Domhog
4, Shuyen

• COUNT(*) would give the answer 4 (there are 4 rows).
• COUNT(DISTINCT name) would give the answer 2 (just ‘Domhog’ and ‘Shuyen’)
• COUNT(name) would give the answer 3 (there are 3 non-null values in the name column.)

I advise people to avoid COUNT(column) because the semantics are confusing in a query. I mean that what people are intending to accomplish is unclear. More, though, I haven’t come across a time when it is necessary.

Whenever you are tempted to use COUNT(column), ask yourself whether COUNT(*) is clearer and is actually what you mean. Most counting queries are questions about the number of rows (after some conditions/joins), not the number of non-NULL values.

ps. I’m very interested to find an example where COUNT(column) is the ideal thing, so please email me if you see one. Only thing I can think of is something like counting records with relationships. e.g. a table like people(id, name, sig_other_id) where those not in a relationship have NULL for their sig_other_id.

--How many people have sig others?
SELECT COUNT(sig_other_id) AS num_in_relationships
FROM people

But I think even that would be much clearer as:

SELECT COUNT(*) as num_in_relationships
FROM people
WHERE people.sig_other_id IS NOT NULL

So you can always re-write COUNT(column) as COUNT(*) with a WHERE ... IS NOT NULL clause. Much clearer.

If we wanted to know how many different manufacturers have cars in the gtcars dataset, in R tidyverse we used n_distinct(manufacturer) but in SQL we would use COUNT(DISTINCT manufacturer)

data(gtcars)

gtcars_renamed <- gtcars %>% 
  select(manufacturer = mfr, everything())

# Write the dataset to DuckDB
dbWriteTable(con, "gtcars", gtcars_renamed, overwrite = TRUE)

SELECT COUNT(DISTINCT manufacturer) AS num_manufacturers
FROM gtcars

1 records

num_manufacturers
19

A warning on MIN and MAX vs ORDER BY LIMIT 1

SQL offers aggregate functions called MIN() and MAX(). These seem straightforward, they find the minimum or maximum value in a column.

SELECT MIN(weight_in_grams) AS lightest_book
FROM books_with_weights

1 records

lightest_book
1456

Which will, correctly, return 1456, the lowest value in the column.

However questions about the ‘lowest’, ‘highest’, ‘earliest’ usually also want another part of the row with that value. e.g., ‘What’s the title of the lightest book?’, ‘Who bought the earliest ticket?’, or ‘Which product had the lowest price?’

These queries are better answered using ORDER BY and LIMIT 1. Using MIN and MAX the intuitive way can silently give a totally incorrect answer. DuckDB will refuse to run this query. Some other databases (SQLite, and MySQL in its older default settings) will run it: the result will look believable, but will have actually chosen an arbitrary value for title.

SELECT title,
       MIN(weight_in_grams) AS lightest_book
FROM books_with_weights

Error in `DBI::dbSendQuery()`:
! Binder Error: column "title" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(title)" if the exact value of "title" is not important.

LINE 1: SELECT title,
               ^
ℹ Context: rapi_prepare
ℹ Error type: BINDER

This bears repeating: If you use MIN or MAX in the SELECT clause with any other column you will not get the value in the row that had the lowest value, you will get an arbitrary value from the other column. I know that this is hard to believe. When an aggregate function is used in SELECT then we must be collapsing a group of rows … if we put a “bare” column name in the SELECT together with an aggregate function, then the system needs a way to pick a value. If the term is in the GROUP BY then it is clear, since all the values must be the same, but if the column is not then it will pick one arbitrarily …. or DuckDB asks us to force that behavior by using the ANY_VALUE(column) function, or throws an error and refuses to run.

While you should use ORDER BY and LIMIT 1, you can use MIN or MAX but you have to use it via a sub-query. Here think of the question as two steps: ‘What is the minimum weight? And what are the titles and weights of the books which have that weight?’

First get the minimum value:

SELECT MIN(weight_in_grams) FROM books_with_weights

1 records

min(weight_in_grams)
1456

That returns a single value which you then can use. You get the database to use it inside another query by surrounding the query with parens (this is called a sub-query; there is more on these in Arranging SQL transforms into a pipeline below).

SELECT title, weight_in_grams AS lightest_book
FROM books_with_weights
WHERE weight_in_grams = (
  SELECT MIN(weight_in_grams) FROM books_with_weights
)

1 records

title	lightest_book
Harry Potter 1	1456

But hey if you use ORDER BY and LIMIT you can easily get the top three or bottom whatever (as long as ties don’t matter), so just don’t use MIN or MAX in the SELECT clause, unless it’s a sub-query. Here are the three lightest books:

SELECT title, weight_in_grams
FROM books_with_weights
ORDER BY weight_in_grams ASC
LIMIT 3

3 records

title	weight_in_grams
Harry Potter 1	1456
Return of the King	1784
Two Towers	1800

I would only use MIN or MAX when ties really matter, and only in a sub-query. Even then there is a more direct approach to ranking, see Window Aggregates below.

GROUP BY

Recall that grouping is akin to sorting a dataframe, then drawing lines across it where the value in the grouping column changes.

title	series	format	weight_in_grams
Harry Potter 1	Harry Potter	paperback	1456
Harry Potter 3	Harry Potter	hardcover	2604
Harry Potter 2	Harry Potter	hardcover	2900
Two Towers	Lord of the Rings	paperback	1800
Return of the King	Lord of the Rings	hardcover	1784

Note

SQL only shows you the result of a finished query; there are no intermediate results along the way. So it pays to build queries up a step at a time: start with SELECT * FROM table, run it, check the rows, then add one clause and run it again. Keep an eye on the number of rows at each step.

On these pages each step is in its own cell, so you can see each intermediate result. In your own notebook you can also keep the earlier steps in the same cell, separated by semicolons (;). All of the queries run, but only the result of the last one is shown.

In SQL we will build up grouping queries using ORDER BY to see the rows before they are collapsed. To find the total weight of the books in each series, we first sort by series:

SELECT *
FROM books_with_weights
ORDER BY series

5 records

title	series	format	weight_in_grams
Harry Potter 1	Harry Potter	paperback	1456
Harry Potter 3	Harry Potter	hardcover	2604
Harry Potter 2	Harry Potter	hardcover	2900
Two Towers	Lord of the Rings	paperback	1800
Return of the King	Lord of the Rings	hardcover	1784

We will then change the ORDER BY to GROUP BY and copy the grouping column to the start of the SELECT. This gives one row per group:

SELECT series
FROM books_with_weights
GROUP BY series

2 records

series
Harry Potter
Lord of the Rings

Finally we add the aggregate function:

SELECT series, SUM(weight_in_grams) AS total_weight
FROM books_with_weights
GROUP BY series

2 records

series	total_weight
Lord of the Rings	3584
Harry Potter	6960

As before we can have multiple aggregate functions collapsing the same groups.

SELECT series, 
       SUM(weight_in_grams) AS total_weight,
       COUNT(*) AS book_count
FROM books_with_weights
GROUP BY series

2 records

series	total_weight	book_count
Harry Potter	6960	3
Lord of the Rings	3584	2

COUNT(DISTINCT column) in group

We can also look within a column and count the number of distinct things, as a way of collapsing the group. Switching to the cars data, we can count the number of different manufacturers for each year. Again we start by sorting, so we can see the groups (here just the columns we need):

SELECT year, manufacturer, model
FROM gtcars
ORDER BY year

Displaying records 1 - 10

year	manufacturer	model
2014	Lamborghini	Gallardo
2014	Ferrari	458 Italia
2015	Ferrari	458 Speciale
2015	Ferrari	458 Spider
2015	Audi	R8
2015	Ferrari	California
2015	Lamborghini	Aventador
2015	Ferrari	FF
2015	Ferrari	F12Berlinetta
2015	Ferrari	LaFerrari

Then change to GROUP BY, giving one row per year:

SELECT year
FROM gtcars
GROUP BY year

4 records

year
2015
2014
2017
2016

And add the aggregate:

SELECT year, COUNT(DISTINCT manufacturer) AS manufacturer_count
FROM gtcars
GROUP BY year

4 records

year	manufacturer_count
2015	3
2014	2
2016	13
2017	8

Post Group and aggregate (ORDER BY and HAVING)

GROUP BY happens after FROM and WHERE (but before SELECT). Since WHERE happens early in the query, we can’t use it to exclude results from these results. And that is commonly a need, for queries that ask “Does any group have more than 1?”

We could pipeline these results into a temporary table, and then use WHERE on that, but SQL has a short cut here: the HAVING clause, which is a second WHERE that executes against the results of a query with a GROUP BY. For example we could find years in which more than 5 manufacturers produced models:

SELECT year, COUNT(DISTINCT manufacturer) AS manufacturer_count
FROM gtcars
GROUP BY year
HAVING COUNT(DISTINCT manufacturer) > 5

2 records

year	manufacturer_count
2017	8
2016	13

We can use ORDER BY to change the order that the aggregated results are displayed (which is to say that ORDER BY happens after GROUP BY and even after HAVING).

SELECT year, COUNT(DISTINCT manufacturer) AS manufacturer_count
FROM gtcars
GROUP BY year
HAVING COUNT(DISTINCT manufacturer) > 5
ORDER BY year

2 records

year	manufacturer_count
2016	13
2017	8

GROUP BY ALL

Above I made a big deal out of the step from ORDER BY to GROUP BY having two parts: change to GROUP BY and copy all the fields to the start of the SELECT. This is important, but it does mean that all the columns are repeated twice, which when queries are edited can lead to annoying mistakes.

DuckDB offers an improvement to SQL to address this, GROUP BY ALL which means that we can put our grouping columns in a single place (the SELECT) and then GROUP BY ALL uses all of the non-aggregate columns in the SELECT.

With our books data, grouping by both series and format:

SELECT series,
       format,
       SUM(weight_in_grams) AS total_weight
FROM books_with_weights
GROUP BY ALL
-- same as GROUP BY series, format

4 records

series	format	total_weight
Lord of the Rings	hardcover	1784
Harry Potter	hardcover	5504
Harry Potter	paperback	1456
Lord of the Rings	paperback	1800

The DuckDB documentation uses this example (it is just for illustration; there is no star_wars_locations table to run it against).

SELECT
    systems,
    planets,
    cities,
    cantinas,
    sum(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;
-- GROUP BY systems, planets, cities, cantinas

Check out the duckdb documentation for other SQL improvements: https://duckdb.org/2022/05/04/friendlier-sql.html. But be mindful that these techniques may not be available in other dialects of SQL.

Arranging SQL transforms into a pipeline

Unlike R tidyverse and Python Pandas, SQL does not lend itself cleanly to creating transformation pipelines. Historically, SQL databases tended to just have the final schema in place, and other tools were used to transform the data on the way into the database. This is known as “Extract-Transform-Load” or ETL https://en.wikipedia.org/wiki/Extract,_transform,_load

In recent years, though, efforts to undertake transformations using SQL have accelerated, drawing on the very large data capabilities that are part of modern SQL databases.

Within a single query, SQL has two standard ways to use the results of one query inside another: sub-queries and CTEs.

products <- tribble(
  ~product_num, ~product_name, ~price_in_dollars,
  "SPR-004456", "Skateboard", 121L,
  "SPR-003356", "Basketball", 34L,
  "SPR-004499", "Tennis balls", 12L,
  "FTW-098756", "Tennis shoes", 79L
)

dbWriteTable(con, "products", products, overwrite = TRUE)

Sub-queries

A sub-query is a whole query, in parentheses, placed inside another query. We saw one above, finding the lightest book by putting SELECT MIN(weight_in_grams) FROM books_with_weights inside a WHERE clause. A sub-query can also go where a table name would normally go, in the FROM clause; we will see that in Window Aggregates below.

Sub-queries are a sort of unnamed temporary table. Because the syntax sticks a whole query right in the middle of another query, they are generally only used for short elements. Generally any complicated query should not be done as a sub-query, instead we turn to the CTE.

CTEs (aka WITH)

A CTE does the transformations at query time; you can think of this as a preamble to an SQL query which creates temporary tables.

WITH products_split_cte AS (
      SELECT STRING_SPLIT(product_num, '-')[1] AS product_code,
         STRING_SPLIT(product_num, '-')[2] AS product_num_no_code,
         product_name
      FROM products
  )
SELECT *
FROM products_split_cte
WHERE product_code = 'FTW'

1 records

product_code	product_num_no_code	product_name
FTW	098756	Tennis shoes

You can even create multiple transformations via WITH, each referencing the last. For example, here we first split out the product codes, creating a temporary products_split_cte then we use that table to count the number of items with each code, creating a temporary product_counts_cte. Then we query product_counts_cte as though it was a table in the database.

WITH
  products_split_cte AS (
      SELECT STRING_SPLIT(product_num, '-')[1] AS product_code,
         STRING_SPLIT(product_num, '-')[2] AS product_num_no_code,
         product_name
      FROM products
  ),
  product_counts_cte AS (
      SELECT product_code, COUNT(*) AS product_count
      FROM products_split_cte
      GROUP BY product_code
  )

SELECT *
FROM product_counts_cte
WHERE product_code = 'SPR'

1 records

product_code	product_count
SPR	3

You can use multiple ctes in the WITH block to build out a complex pipeline.

Note

When we use the WITH block to create temporary tables those are called CTEs. CTE stands for Common Table Expression, which doesn’t really explain much. The usual explanation is that these temporary table definitions are “common” to the rest of the query, in the sense of being available to any other part of it. See https://stackoverflow.com/questions/43564050/why-is-common-table-expression-called-common

The key thing here is to recognize that when people say, “Use a CTE” they mean “define a temporary table using the WITH block at the top of your query.

I find it useful to signal that I’m referencing a temporary table by adding the suffix _cte to the end of the tables I create this way.

Finally: you can also save intermediate results as tables, or use tools like DBT that manage a pipeline of tables; see Saving pipeline steps as tables in SQL Transforms Extras.

Window Aggregates

In R window aggregates (which return more than one row per group) are a fairly straightforward extension of the syntax used for basic group bys. For example, we can use slice_max to get the three most powerful cars (highest horsepower, hp) in each year.

gtcars %>% 
  group_by(year) %>% 
  slice_max(n = 3, order_by = hp, with_ties = FALSE) %>% 
  ungroup() %>% 
  select(model, year, hp)

In SQL, though, the concepts are the same but the syntax varies quite a bit from simple GROUP BY.

We have to use two steps. The first gets our rankings within a group (for each and every row), the second uses those rankings to exclude rows below a certain rank.

Getting the rank within a group uses the new syntax of OVER (PARTITION BY ... ORDER BY ...). We use ORDER BY hp DESC so that the most powerful car in each year gets rank 1. PARTITION BY divides the rows into groups just like GROUP BY, but it doesn’t collapse each group to one row: every row stays, and gets its rank within its group.

As with GROUP BY, first sort so we can see the groups and the order within them:

SELECT model,
       year,
       hp
FROM gtcars 
ORDER BY year, hp DESC

Displaying records 1 - 10

model	year	hp
458 Italia	2014	562
Gallardo	2014	550
LaFerrari	2015	949
F12Berlinetta	2015	731
Aventador	2015	700
FF	2015	652
Huracan	2015	610
458 Speciale	2015	597
458 Spider	2015	562
California	2015	553

Then add the ranking:

SELECT model,
       year,
       hp,
       row_number() OVER (PARTITION BY year ORDER BY hp DESC) AS rank_in_year
FROM gtcars 

Displaying records 1 - 10

model	year	hp	rank_in_year
458 Italia	2014	562	1
Gallardo	2014	550	2
LaFerrari	2015	949	1
F12Berlinetta	2015	731	2
Aventador	2015	700	3
FF	2015	652	4
Huracan	2015	610	5
458 Speciale	2015	597	6
458 Spider	2015	562	7
California	2015	553	8

The second step should be straightforward, using WHERE rank_in_year <= 3. But remember the order in which the parts of a query execute: FROM, then WHERE, then GROUP BY and HAVING, and only then SELECT. Window functions are calculated in the SELECT, so neither WHERE nor HAVING can see rank_in_year yet. If you try, DuckDB will tell you “WHERE clause cannot contain window functions!”

So we need a second query that works on the results of the first one: a small pipeline. We can use either of the options from Arranging SQL transforms into a pipeline.

With a sub-query, we copy the working ranking query into where the table name would normally go, like this:

SELECT *
FROM (
  SELECT model,
       year,
       hp,
       row_number() OVER (PARTITION BY year ORDER BY hp DESC) AS rank_in_year
  FROM gtcars
)
WHERE rank_in_year <= 3
ORDER BY year, rank_in_year

Displaying records 1 - 10

model	year	hp	rank_in_year
458 Italia	2014	562	1
Gallardo	2014	550	2
LaFerrari	2015	949	1
F12Berlinetta	2015	731	2
Aventador	2015	700	3
488 GTB	2016	661	1
Corvette	2016	650	2
Wraith	2016	624	3
GTC4Lusso	2017	680	1
GT	2017	647	2

Or with a CTE, we give the ranking query a name in the WITH block, then query it as though it were a table:

WITH hp_ranks_cte AS (
  SELECT model,
       year,
       hp,
       row_number() OVER (PARTITION BY year ORDER BY hp DESC) AS rank_in_year
  FROM gtcars
)
SELECT *
FROM hp_ranks_cte
WHERE rank_in_year <= 3
ORDER BY year, rank_in_year

Displaying records 1 - 10

model	year	hp	rank_in_year
458 Italia	2014	562	1
Gallardo	2014	550	2
LaFerrari	2015	949	1
F12Berlinetta	2015	731	2
Aventador	2015	700	3
488 GTB	2016	661	1
Corvette	2016	650	2
Wraith	2016	624	3
GTC4Lusso	2017	680	1
GT	2017	647	2

Note

DuckDB (along with Snowflake, BigQuery, and Databricks) offers a third filtering clause, QUALIFY, which executes after the window functions are calculated. So WHERE filters rows, HAVING filters groups, and QUALIFY filters the results of window functions:

SELECT model,
       year,
       hp,
       row_number() OVER (PARTITION BY year ORDER BY hp DESC) AS rank_in_year
FROM gtcars
QUALIFY rank_in_year <= 3
ORDER BY year, rank_in_year

Many SQL dialects (including PostgreSQL, MySQL, and SQL Server) don’t have QUALIFY, so it is worth knowing the sub-query and CTE approaches above.

As with R tidyverse, you can also use more sophisticated ranking functions instead of row_number, such as RANK or DENSE_RANK see https://duckdb.org/docs/sql/functions/window_functions.html

Exercises

These continue with the pizzaplace data from the SQL Transforms review exercises. Remember that one row is one pizza sold, and id identifies the order that the pizza was part of. Load it into DuckDB again (each notebook starts with an empty database):

dbWriteTable(con, "pizzaplace", gt::pizzaplace, overwrite = TRUE)

Build each query up a step at a time, and keep an eye on the number of rows.

1. How many pizzas were sold during the year? How many orders were there? How many different kinds of pizza (name) are on the menu? Answer all three in a single query.

2. What was the total revenue (price) for each type of pizza? Show the highest first.

3. Which sizes sold fewer than 1,000 pizzas?

4. How many orders were placed in each hour of the day? Reuse the STRING_SPLIT on time from review exercise 2 to make order_hour, and use GROUP BY ALL. When are the busiest hours?

5. Using a CTE, first make a table with one row per order, showing the number of pizzas and the total price of each order. Then use it to find the average number of pizzas per order, the average order total, and the largest order (most pizzas).

6. Stretch: find the best-selling pizza (most sold) within each type. You will need to count first, then rank.

7. Stretch: which three dates had the most orders? Can you think of a reason?

Then do the eight exercises on SUM and COUNT on SQLZoo

SQLZoo runs a different database, not DuckDB, so the DuckDB shortcuts on this page (GROUP BY ALL and QUALIFY) won’t work there. Use the standard forms: list the grouping columns in GROUP BY, and use a sub-query to filter on a ranking.