Public
Edited
Jun 17, 2023
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT products.name
, products.size
, COUNT(DISTINCT orders.id) as "Number of Orders"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY Name, Size
ORDER BY 3 DESC
Insert cell
Insert cell
pizzeriaDB
WITH order_by_product_day as (
SELECT products.name
, orders.OrderDate::date as order_date
, COUNT(DISTINCT orders.id) as num_orders
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1, 2
)

SELECT name
, avg(num_orders) as "Avg Daily Orders"
, max(num_orders) as "Max Daily Orders"
, min(num_orders) as "Min Daily Orders"
, median(num_orders) as "Median Daily Orders"
, mode(num_orders) as "Mode Daily Orders"
FROM order_by_product_day
GROUP BY 1

-- See DuckDB functions: https://duckdb.org/docs/sql/aggregates.html
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT Name
, Ingredients
, products.Size
FROM products
WHERE Ingredients NOT LIKE 'Tomato%'
AND products.Size = 'Small'
Insert cell
Insert cell
pizzeriaDB
SELECT products.Name
, products.Size
, COUNT(DISTINCT orders.id) as "Number of Orders"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1, 2
HAVING "Number of Orders" >= 100000
Insert cell
Insert cell
pizzeriaDB
SELECT date_part('month', orderDate::date) as Month
, sum(total) as "Monthly Revenue ($)"
FROM orders
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT products.Name
, dayname(orders.orderDate::date) as "Day of Week"
, COUNT(DISTINCT orders.id) as "Number of Orders"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
WHERE products.Name LIKE 'Pepperoni%'
GROUP BY 1, 2
ORDER BY 3 DESC

-- See additional date functions: https://duckdb.org/docs/sql/functions/date
Insert cell
Insert cell
pizzeriaDB
WITH ordersByDay AS (
SELECT OrderDate::date as "Order Date"
, COUNT(DISTINCT id) as "Number of Orders"
FROM orders
GROUP BY 1
ORDER BY 1 ASC
), returnResults AS (
SELECT *
, avg("Number of Orders") over (
order by "Order Date" asc
range between interval 27 days preceding
and interval 0 days following
) as "28-Day Moving Average"
FROM ordersByDay
)

SELECT *
FROM returnResults

Insert cell
Insert cell
pizzeriaDB
WITH categorySummary AS (
SELECT products.Category as "Product Category"
, products.Name as "Product Name"
, products.Launch::date as "Launch Date"
, COUNT(DISTINCT orders.id) as "Total Orders"
, sum(orders.total) as "Total Revenue ($)"
, COUNT(DISTINCT orders.customerId) as "Total Customers"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1, 2, 3
), categorySummaryResults AS (
SELECT *
, row_number() OVER (PARTITION BY "Product Category" ORDER BY "Launch Date" ASC) as "Launch Order"
FROM categorySummary
)

SELECT *
FROM categorySummaryResults
WHERE "Launch Order" = 1
Insert cell
Insert cell
pizzeriaDB
WITH UtahStores AS (
SELECT DISTINCT stores.state
, orders.orderDate::date as order_date
, name
, count(distinct orders.id) as num_orders
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
JOIN stores
ON orders.storeId = stores.id
WHERE stores.state = 'Utah'
GROUP BY 1, 2, 3
)
SELECT state
, order_date
, num_orders
FROM UtahStores
WHERE name like '%Hawaiian%'

Insert cell
Insert cell
pizzeriaDB
WITH customerOrderHistory AS (
SELECT customerId
, orders.id
, group_concat(order_items.SKU, ',') as full_order
FROM orders
JOIN order_items
ON orders.id = order_items.orderId
GROUP BY 1, 2
), returnResults AS(
SELECT customerId
, full_order
, COUNT(DISTINCT id) as full_order_tally
FROM customerOrderHistory
GROUP BY 1, 2
ORDER BY 1
)

SELECT *
, min(full_order_tally) OVER (PARTITION BY customerId) as cust_min
, max(full_order_tally) OVER (PARTITION BY customerId) as cust_max
, avg(full_order_tally) OVER (PARTITION BY customerId) as cust_avg
, median(full_order_tally) OVER (PARTITION BY customerId ORDER BY full_order_tally ASC) as cust_median
FROM returnResults

Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more