The avatar for @fotisz
Fotis
Public
Edited
Jun 23, 2023
10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT concat(products.name,' ',products.size) as product,
count(distinct orders.id) as num_orders
FROM products
LEFT JOIN order_items
ON products.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
LEFT JOIN stores
ON orders.storeId = stores.id
GROUP BY product
ORDER BY num_orders DESC
Insert cell
Insert cell
pizzeriaDB
SELECT min(orders.nItems) as min_items
, max(orders.nItems) as max_items
, avg(orders.nItems) as avg_items
, median(orders.nItems) as median_items
, mode(orders.nItems) as mode_items
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT products.name as Product_List
FROM products
WHERE Ingredients not like '%omato%' AND Size = 'Small'
Insert cell
Insert cell
pizzeriaDB
SELECT products.name, count(distinct orders.id) as product_orders
FROM products
LEFT JOIN order_items
ON products.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
GROUP BY products.name
HAVING product_orders > 100000
Insert cell
Insert cell
pizzeriaDB
SELECT date_trunc('month', orderDate::date) as YYYYMMDD
, sum(total) as Total
FROM orders
GROUP BY 1
ORDER BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT case when date_part('dayofweek', orderDate::date) == 0 then 'Sunday'
when date_part('dayofweek', orderDate::date) == 1 then 'Monday'
when date_part('dayofweek', orderDate::date) == 2 then 'Tuesday'
when date_part('dayofweek', orderDate::date) == 3 then 'Wednesday'
when date_part('dayofweek', orderDate::date) == 4 then 'Thursday'
when date_part('dayofweek', orderDate::date) == 5 then 'Friday'
when date_part('dayofweek', orderDate::date) == 6 then 'Saturday' end as year_name
, count(distinct id) as num_orders
FROM products
LEFT JOIN order_items
ON products.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
WHERE products.name = 'Pepperoni Pizza'
GROUP BY 1
ORDER BY 2 DESC
Insert cell
Insert cell
pizzeriaDB
WITH Num_Orders AS (
SELECT 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
ORDER BY 1 ASC
), returnResults AS (
SELECT *
, avg(num_orders) over (
order by order_date asc
range between interval 14 days preceding
and interval 14 days following
) as moving_avg
FROM Num_Orders
)

SELECT *
FROM returnResults
Insert cell
Insert cell
pizzeriaDB
SELECT products.name
, products.category
, sum(distinct orders.id) as total_orders
, date_part('year', orders.orderDate::date)::string as year_name
, row_number() over (partition by year_name order by orderDate asc) as row_num
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY products.name, products.category, orderDate
ORDER BY row_num, year_name
Insert cell
Insert cell
pizzeriaDB
WITH utahStores AS (
SELECT *
FROM stores
WHERE state = 'Utah'
)
SELECT orders.orderDate::date as order_date
, count(distinct orders.id) as num_orders
FROM products
LEFT JOIN order_items
ON products.SKU = order_items.SKU
LEFT JOIN orders
ON order_items.orderId = orders.id
LEFT JOIN utahStores
ON orders.storeId = utahStores.id
WHERE products.name like '%Hawaiian%'
GROUP BY 1
ORDER BY 1 ASC
Insert cell
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

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