The avatar for @dmaynard
David Maynard
Retired Computer Scientist: SRI-ARC (Engelbart), Xerox PARC, Xerox SDS, Electronic Arts, SGI, Google, Box
Public
Edited
Jun 6, 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)
FROM stores
Join orders
ON stores.id = orders.storeId
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT stores.state
, count(distinct orders.id)
, sum(orders.total) as revenue
FROM stores
Join orders
ON stores.id = orders.storeId
GROUP BY 1
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
, mode(orders.nItems) as mode
FROM orders
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT case when Category = 'Classic' then 'Classic'
else 'Non_Classic' end as 'bucket', count(*) as num_products
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'
when nItems > 3 then '4+ Items'
end as "bucket"
, sum(total) as "revenue"
FROM orders
GROUP BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT coalesce(nevada_stores.state, 'Non-Nevada') as bucket
, case when nevada_stores.state is null then 'Non-Nevada'
else 'Nevada' end as "case bucket"
, count(distinct orders.id) as num_orders
FROM orders
LEFT
JOIN nevada_stores
ON orders.storeId = nevada_stores.id
GROUP BY 1, 2
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE category in ('Classic','Specialty')
Insert cell
Insert cell
pizzeriaDB
SELECT Name, ingredients
FROM products
WHERE ingredients like ('Tomato%')
Insert cell
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
pizzeriaDB
SELECT concat(Name, ' ' , Size )
FROM products
Insert cell
Insert cell
pizzeriaDB
SELECT replace(Name, 'Pizza', ' ') FROM products
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT date_trunc('year', orderDate::date) as year_name
, count(distinct orders.id) as num_orders
FROM orders
GROUP BY 1
ORDER BY 1
Insert cell
Insert cell
pizzeriaDB
SELECT date_part('dayofweek', orderDate::date) as "Day of Week"
, count(distinct orders.id) as num_orders
FROM orders
GROUP BY 1
ORDER BY 1
Insert cell
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
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
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