Public
Edited
Jun 24, 2023
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

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more