The avatar for @meuespaco
meuespaco
Public
Edited
Jun 19, 2023
Fork of SQL Course: Session 4
10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT row_number() over (partition by Category order by Launch) as "Row Number"
, *
FROM products
Insert cell
Insert cell
pizzeriaDB
SELECT products.name
, products.size
, products.category
, count(distinct orders.id) as "Number of Orders"
, rank() over (partition by products.category order by "Number of Orders" DESC) as "Order Rank"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1, 2, 3

Insert cell
groceries
SELECT *
, sum(amount) over (rows between 1 preceding and 1 following) as moving
FROM groceries
Insert cell
groceries
SELECT *
, sum(amount) over (partition by category rows between 1 preceding and unbounded following) as moving
FROM groceries
Insert cell
california_daily_orders.csv
SELECT order_date
, orders
, sum(orders) over (
order by order_date asc
range between interval 3 days preceding
and interval 3 days following)::int as moving_avg
FROM california_daily_orders
LIMIT 10
Insert cell
Insert cell
pizzeriaDB
SELECT products.name
, products.size
, products.category
, 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
ORDER BY row_num, year_name
LIMIT 10
Insert cell
Insert cell
pizzeriaDB
WITH utahStores AS (
SELECT *
FROM stores
WHERE state = 'Utah'
)

SELECT date_trunc('month', orders.orderDate::date) as "Month Name"
, count(distinct orders.id) as "Number of Orders"
FROM orders
JOIN utahStores
ON orders.storeId = utahStores.id
GROUP BY 1
Insert cell
california_daily_orders.csv
SELECT order_date
, sum(orders) over (order by order_date asc range between interval 3 days preceding and interval 3 days following)::int as moving_avg
FROM california_daily_orders
GROUP BY 1
Insert cell
california_daily_orders.csv
SELECT order_date
, sum(sum(orders)) over (order by order_date asc range between interval 3 days preceding and interval 3 days following)::int as moving_avg2
FROM california_daily_orders
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
WITH hawaiianOrders 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
WHERE products.name like '%Hawaiian%'
GROUP BY 1
ORDER BY 1 ASC
), returnResults AS (
SELECT *
, avg(num_orders) over (
order by order_date asc
range between interval 3 days preceding
and interval 3 days following
) as moving_avg
FROM hawaiianOrders
)

SELECT *
FROM returnResults

Insert cell
Insert cell
pizzeriaDB
WITH newData AS (
SELECT orders.id
, products.name
, products.Size
FROM orders
JOIN order_items
ON orders.id = order_items.orderId
JOIN products
ON order_items.SKU = products.SKU
WHERE Category = 'Vegetarian'
)

SELECT name
, Size
, count(distinct id)
FROM newData
GROUP BY 1, 2
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
groceries
SELECT avg(amount) as avg_amount
FROM groceries
WHERE category = 'Fruits'
Insert cell
Insert cell
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