The avatar for @approfir
approfir
Public
Edited
Jun 7, 2023
Fork of SQL Course: Session 3
10
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT stores.state
, count(distinct orders.id) as numOrders
FROM stores
JOIN orders
ON stores.id = orders.storeID
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT stores.state
, count(distinct orders.id) as numOrders
, sum(orders.total) as totalRevenue
FROM stores
JOIN orders
ON stores.id = orders.storeID
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT min(nItems) as minItems
, max(nItems) as maxItems
, avg(nItems) as avgItems
, mode(nItems) as modeItems
, median(nItems) as medianItems
FROM orders
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT CASE WHEN Category = 'Classic' THEN 'Classic'
ELSE 'Non-Classic' END as 'CategoryType'
, count(*) as numProducts
FROM products
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT CASE WHEN nItems = 1 THEN '1 item'
WHEN nItems = 2 OR nItems = 3 THEN '2-3 items'
ELSE '4+ items' END as 'numItemsBucket'
, sum(total) as 'Revenue'
FROM orders
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT coalesce(nevada_stores.state, 'Non-NV') as bucket
, count(distinct orders.id) as numOrders
FROM orders
LEFT
JOIN nevada_stores
ON nevada_stores.id = orders.storeId
GROUP BY 1
Insert cell
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE category in ('Classic', 'Specialty')
Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE ingredients LIKE 'Tomato%'
Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE ingredients NOT LIKE 'Tomato%'
Insert cell
Insert cell
pizzeriaDB
SELECT concat(products.name, ' ', products.size) as nameSize
, Name || ' ' || Size as nameSize_oldway
FROM products
Insert cell
Insert cell
pizzeriaDB
SELECT concat(products.name, ' ', products.size) as nameSize
, replace(nameSize, 'Pizza', '') as newNameSize
FROM products
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT date_trunc('year', orderDate::date) as year_name
, count(distinct orders.id) as numOrders
FROM orders
GROUP BY 1
ORDER BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT date_part('dayofweek', orderDate::date) as dayofweek
, count(distinct orders.id) as numOrders
FROM orders
GROUP BY 1
ORDER BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT date_part('dayofweek', orderDate::date) as dayofweek
, CASE WHEN dayofweek = 0 THEN 'Sunday'
WHEN dayofweek = 1 THEN 'Monday'
WHEN dayofweek = 2 THEN 'Tuesday'
WHEN dayofweek = 3 THEN 'Wednesday'
WHEN dayofweek = 4 THEN 'Thursday'
WHEN dayofweek = 5 THEN 'Friday'
WHEN dayofweek = 6 THEN 'Saturday'
END as 'CalendarDay'
, count(distinct orders.id) as numOrders
FROM orders
GROUP BY 1
ORDER BY 3 DESC
Insert cell
Insert cell
pizzeriaDB
--SELECT orders.customerID
--, DATEDIFF('day', MIN(orderDate)::date, MAX(orderDate)::date) as 'orderDateRange'
--FROM orders
--GROUP BY 1
--ORDER BY 2 DESC

SELECT median(day_diff)
FROM (
SELECT customerId
, date_diff('day', min(orderDate::date), max(orderDate::date)) as day_diff
FROM orders
GROUP BY 1
)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
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