The avatar for @visnup
Visnu Pitiyanuvath
Public
Edited
Dec 26, 2022
1 fork
1 star
1
Insert cell
Insert cell
Insert cell
noahs.sqlite
select replace(phone, '-', '') as phone, name
from customers
where length(name) >= 10
Insert cell
lastNames = plausible
.map((p) => ({
...p,
lastName: p.name.split(" ")[1]
}))
.filter((p) => p.lastName.length === 10)
Insert cell
mapped = lastNames.map((p) => ({
...p,
mapped: [...p.lastName.toLowerCase()].map((c) => e161.get(c)).join("")
}))
Insert cell
investigator = mapped.find((p) => p.mapped.includes(p.phone))
Insert cell
e161 = new Map(
Object.entries({
abc: 2,
def: 3,
ghi: 4,
jkl: 5,
mno: 6,
pqrs: 7,
tuv: 8,
wxyz: 9,
" ": 0
}).flatMap(([keys, number]) => [...keys].map((k) => [k, number]))
)
Insert cell
Insert cell
noahs.sqlite
select o.orderid,
datetime(o.ordered, 'start of year') as year,
o.ordered,
p.desc,
c.customerid,
c.name,
substr(c.name, 1, 1) || substr(c.name, instr(c.name, ' ') + 1, 1) as initials,
c.phone
from orders o
join customers c on c.customerid = o.customerid
join orders_items i on i.orderid = o.orderid
join products p on p.sku = i.sku
where year = datetime('2017-01-01')
and initials = 'JD'
and p.desc = 'Coffee, Drip'
Insert cell
noahs.sqlite
select desc from products
where desc like '%Coffee%'
Insert cell
Insert cell
Insert cell
Insert cell
noahs.sqlite
select *
from customers
where customerid = ${contractor[0].customerid}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
noahs.sqlite
select *, cast(strftime('%m', birthdate) as decimal) as month
from customers
where citystatezip = ${neighborhood[0].citystatezip}
and (month = 3 or month = 4)
Insert cell
neighbor = inNeighborhood.find((c) =>
starts.some((y) => c.birthdate.startsWith(`${y}-`))
)
Insert cell
Insert cell
noahs.sqlite
select o.customerid, o.orderid, o.ordered, i.sku, p.desc
from orders o
join orders_items i on i.orderid = o.orderid
join products p on p.sku = i.sku
where o.customerid = ${neighbor.customerid}
and o.ordered < '2018-01-01'
Insert cell
Insert cell
noahs.sqlite
select
c.customerid,
c.name,
c.phone,
count(*)
from orders o
join orders_items i on i.orderid = o.orderid
join customers c on c.customerid = o.customerid
where i.sku like 'BKY%'
and cast(strftime('%H', o.shipped) as decimal) < 5
group by 1
order by 4 desc
limit 5
Insert cell
Insert cell
noahs.sqlite
select c.name, phone, count(*), sum(i.qty * i.unit_price)
from customers c
join orders o on o.customerid = c.customerid
join orders_items i on i.orderid = o.orderid
where c.citystatezip like 'Queens Village%'
and i.sku like 'PET%'
group by 1, 2
order by 4 desc
Insert cell
Insert cell
noahs.sqlite
select o.customerid, c.name, c.phone, count(*), sum(i.unit_price - p.wholesale_cost)
from orders_items i
join products p on p.sku = i.sku
join orders o on o.orderid = i.orderid
join customers c on c.customerid = o.customerid
where i.unit_price < p.wholesale_cost
group by 1, 2, 3
order by 4 desc
limit 10
Insert cell
Insert cell
noahs.sqlite
select
o.shipped,
i.sku,
p.desc,
datetime(o.shipped, '-1 hour') as shipped_min,
datetime(o.shipped, '1 hour') as shipped_max
from orders o
join orders_items i on i.orderid = o.orderid
join products p on p.sku = i.sku
where o.customerid = 8342
and p.desc like '%(%)%'
Insert cell
noahs.sqlite
with

colors as (
select
o.customerid,
o.orderid,
o.shipped,
datetime(o.shipped, '-1 hour') as shipped_min,
datetime(o.shipped, '1 hour') as shipped_max,
i.sku,
p.desc,
substring(p.desc, 0, instr(p.desc, ' (')) as desc_partial
from orders o
join orders_items i on i.orderid = o.orderid
join products p on p.sku = i.sku
where o.customerid = 8342
and p.desc like '%(%)%'
)

select
o1.customerid,
o1.shipped,
o1.desc,
o2.customerid as customerid_other,
c.name, c.phone,
o2.shipped as shipped_other,
p.desc as desc_other
from colors o1
join orders o2 on o2.shipped between o1.shipped_min and o1.shipped_max and o2.orderid != o1.orderid
join orders_items i on i.orderid = o2.orderid
join products p on p.sku = i.sku and substring(p.desc, 0, instr(p.desc, ' (')) = o1.desc_partial
join customers c on c.customerid = o2.customerid
Insert cell
Insert cell
noahs.sqlite
select *
from products
where sku like 'COL%'
order by sku
Insert cell
noahs.sqlite
select c.customerid, c.name, c.phone, count(distinct i.sku)
from orders_items i
join orders o on o.orderid = i.orderid
join customers c on c.customerid = o.customerid
where i.sku like 'COL%'
group by 1, 2, 3
order by 4 desc
limit 20
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