The avatar for @dmaynard
David Maynard
Retired Computer Scientist: SRI-ARC (Engelbart), Xerox PARC, Xerox SDS, Electronic Arts, SGI, Google, Box
Public
Edited
Jun 24, 2023
10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT count(*) as num_orders
, products.Name
, products.Size

FROM orders
JOIN order_items
ON id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
GROUP BY products.Name, products.Size
ORDER BY num_orders DESC
Insert cell
Insert cell
orders_by_product_day.csv
SELECT name
, min(orders_by_product_day.orders) as min_orders
, max(orders_by_product_day.orders) as max_items
, median(orders_by_product_day.orders) as median_items
, mode(orders_by_product_day.orders) as mode
, name
FROM orders_by_product_day
GROUP BY name
ORDER BY median_items DESC
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT Name
, Ingredients
, Size
From PRODUCTS
Where ingredients NOT LIKE '%Tomato%' AND Size = 'Small'
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT date_trunc('month', orderDate::date) as month
, SUM(total) as revenue
FROM orders
GROUP BY month
ORDER BY month ASC
Insert cell
Insert cell
pizzeriaDB
SELECT dayname ( orderDate:: date) as 'day_of_week'
, count (*) as num_orders
FROM orders
JOIN order_items
ON id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
WHERE name LIKE('Pepperoni Pizza')
GROUP BY 1
ORDER BY num_orders DESC
Insert cell
Insert cell
pizzeriaDB
WITH daily_orders
AS
(SELECT date_trunc('day', orderDate::date) as 'date'
,count(*) AS num_orders
FROM orders
GROUP BY date_trunc('day', orderDate::date))
SELECT date,
ROUND (avg(num_orders)
over (order by date asc
rows between 27 preceding and current row),0) as avg,
FROM daily_orders

Insert cell
Insert cell
pizzeriaDB
SELECT
products.category, products.Name
, products.Launch
, count(*)
FROM
products
GROUP BY products.category, products.Launch::date, products.name, products.Launch
ORDER by products.Launch::date ASC
Insert cell
Insert cell
pizzeriaDB
SELECT count(*) as num_orders
, products.Name, products.Category
FROM orders
JOIN order_items
ON id = order_items.orderID
JOIN products
ON order_items.SKU = products.SKU
GROUP BY products.Name, products.Category
ORDER BY num_orders DESC
Insert cell
Insert cell
pizzeriaDB
WITH utahStores AS (
SELECT *
FROM stores
WHERE state = 'Utah'
),
HawaiianProducts AS (
SELECT * from products
WHERE products.name like '%Hawaiian%'
)
SELECT DISTINCT
orders.orderDate::date as order_date
, count(distinct orders.id) as num_orders
FROM HawaiianProducts
LEFT JOIN order_items
ON HawaiianProducts.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
JOIN utahStores
ON (orders.storeId = utahStores.id )
-- WHERE products.name like '%Hawaiian%'
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT * FROM stores
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