The avatar for @fgazzelloni
Federica Gazzelloni
Public
SQL Course
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) as num_orders
, 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_items
, mode(orders.nItems) as mode_items
FROM orders
Insert cell
Insert cell
Insert cell
pizzeriaDB
SELECT case when Category = 'Classic' then 'Classic'
else 'Non-Classic' end as "My 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_strores.state, 'Non-Nevada') as bucket
, case when nevada_stores.state is null then 'Non-Nevada'
else 'Nevada'
, count(distinct orders.id) as num_orders
FROM orders
LEFT
JOIN nevada_strores
ON orders.storeId = nevada.id
GROUP BY 1
Insert cell
Insert cell
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE Ingredients like '%tomato%'
Insert cell
Insert cell
pizzeriaDB
SELECT concat(Name, ' ' ,Size,'is really tasty') as my_first_concat
,Name || ' ' || Size as my_second_concat
FROM products

Insert cell
Insert cell
pizzeriaDB
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
pizzeriaDB
SELECT replace(Name, 'Pizza','') as replace_name
, Name
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 orderDate::date
Insert cell
Insert cell
pizzeriaDB
SELECT date_trunc('year', 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