The avatar for @ashish-soni08
Ashish Soni
Public
SQL Course
Edited
Jun 17, 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_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 *
FROM products
WHERE Ingredients like '%omato%'
Insert cell
Insert cell
pizzeriaDB
SELECT *
FROM products
WHERE Ingredients not like '%omato%'
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
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 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
SELECT case when date_part('dayofweek', orderDate::date) == 0 then 'Sunday'
when date_part('dayofweek', orderDate::date) == 1 then 'Monday'
when date_part('dayofweek', orderDate::date) == 2 then 'Tuesday'
when date_part('dayofweek', orderDate::date) == 3 then 'Wednesday'
when date_part('dayofweek', orderDate::date) == 4 then 'Thursday'
when date_part('dayofweek', orderDate::date) == 5 then 'Friday'
when date_part('dayofweek', orderDate::date) == 6 then 'Saturday' end as year_name
, count(distinct id) as num_orders
FROM orders
GROUP BY 1
ORDER BY 2 DESC
Insert cell
Insert cell
pizzeriaDB
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