Platform
Solutions
Resources
Pricing
Sign in
Sign up
David Maynard
Retired Computer Scientist: SRI-ARC (Engelbart), Xerox PARC, Xerox SDS, Electronic Arts, SGI, Google, Box
Workspace
Fork
Public
By
David Maynard
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
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.
Try it for free
Learn more
Compare fork
Fork
View
Export
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
pizzeriaDB
Edit
Add comment
Copy import
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Add comment
Select
Duplicate
Copy link
Embed
Delete
Cells
pizzeriaDB
File attachments
orders-1.parquet
Parquet
orders_by_product_day.csv
CSV
customers-1.csv
CSV
california_daily_orders.csv
CSV
orderItems-1.parquet
Parquet
nevada_daily_orders.csv
CSV
stores-1.csv
CSV
nevada_stores.csv
CSV
products-1.csv
CSV
california_stores.csv
CSV
Databases
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Copy import
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Copy import
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
Edit
Add comment
Copy import
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML
color
Edit
Add comment
Copy import
Select
Duplicate
Copy link
Embed
Delete
JavaScript
Markdown
HTML