The avatar for @mythmon
Michael Cooper
Developer @Observable
Public
Edited
Jan 2, 2024
2 forks
2 stars
1
Insert cell
Insert cell
db
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
Insert cell
Insert cell
(await db.query(`SELECT name, phone FROM customers`))
.map((d) => ({
...d,
lastName: d.name
.split(" ")
.filter((p) => p != "Jr.")
.filter((p) => p != "II")
.at(-1),
phoneDigits: d.phone.replace(/[^\d]/g, "")
}))
.map((d) => ({
...d,
lastNameDigits: Array.from(
d.lastName.toLowerCase(),
(c) => phoneLetters[c]
).join("")
}))
.find((d) => d.phoneDigits.endsWith(d.lastNameDigits)).phone
Insert cell
chr = (c) => c.charCodeAt(0)
Insert cell
phoneLetters = Object.fromEntries(
d3
// for every letter, as a number
.range(chr("a"), chr("z") + 1)
.map((d) => [
// map back to a letter
String.fromCharCode(d),
Math.floor(
(d -
// what position is this letter in the alphabet?
"a".charCodeAt(0) -
// z and s are one step further back than they would otherwise be.
(d === "z".charCodeAt(0) ? 2 : d >= "s".charCodeAt(0) ? 1 : 0)) /
// there are 3 letters on most buttons
3
// letters start on the 2 button
) + 2
])
)
Insert cell
Insert cell
Insert cell
db
WITH
coffees as (
SELECT p.sku FROM products p WHERE LOWER(p.desc) LIKE '%coffee%'
),
bagels as (
SELECT p.sku FROM products p WHERE LOWER(p.desc) LIKE '%bagel%'
),
coffeeOrderItems as (
SELECT oi.orderid FROM orders_items oi JOIN bagels ON bagels.sku = oi.sku
),
bagelOrderItems as (
SELECT oi.orderid FROM orders_items oi JOIN bagels ON bagels.sku = oi.sku
),
coffeeAndBagelOrders as (
SELECT o.orderid, o.customerid, boi.*, coi.*
FROM orders o
FULL JOIN bagelOrderItems boi ON boi.orderid = o.orderid
FULL JOIN coffeeOrderItems coi ON coi.orderid = o.orderid
WHERE boi.orderid IS NOT NULL
AND coi.orderid IS NOT NULL
)

SELECT cabo.*, p.*
FROM coffeeAndBagelOrders cabo
JOIN orders_items oi ON cabo.orderid = oi.orderid
JOIN products p on p.sku = oi.sku

ORDER BY oi.
orderid

-- SELECT c.name, c.phone
-- FROM customers c
-- JOIN orders o ON o.customerid = c.customerid
-- JOIN coffeeAndBagelOrders cabo ON cabo.customerid = c.customerid
-- WHERE c.name LIKE 'J% D%'

Insert cell
db
SELECT c.name, c.phone, o.orderid, p.desc
FROM customers c
JOIN orders o ON c.customerid = o.customerid
JOIN orders_items oi ON o.orderid = oi.orderid
JOIN products p ON p.sku = oi.sku
WHERE c.name LIKE 'J% D%'
AND (p.desc = 'Coffee, Drip' OR p.desc ILIKE '%bagel%')
Insert cell
answer2 = Array.from(
d3.group(puzzle2Data, (d) => d.orderid).values(),
(ds) => ({
name: ds[0].name,
phone: ds[0].phone,
orderid: ds[0].orderid,
products: ds.map((d) => d.desc)
})
).filter((d) => d.products.length >= 2)[0].phone
Insert cell
Insert cell
Insert cell
Insert cell
ariesExtent = ["03-23", "04-19"]
Insert cell
dogYearMod = 2
Insert cell
jdsNeighborhood = db
.query(`SELECT c.citystatezip FROM customers c WHERE c.phone = '${answer2}'`)
.then((rows) => rows[0].citystatezip)
Insert cell
db
select distinct c.name, c.phone
from customers c
JOIN orders o ON c.customerid = o.customerid
JOIN orders_items oi ON o.orderid = oi.orderid
WHERE CAST(SUBSTR(c.birthdate, 0, 5) AS INTEGER) % 12 == ${dogYearMod}
AND SUBSTR(c.birthdate, 6) >= ${ariesExtent[0]} AND SUBSTR(c.birthdate, 6) <= ${ariesExtent[1]}
AND c.citystatezip = ${jdsNeighborhood}
Insert cell
Insert cell
Insert cell
Insert cell
db
SELECT c.name, c.phone, sum(oi.qty) as lifetime_count
FROM customers c
JOIN orders o ON c.customerid = o.customerid
JOIN orders_items oi ON o.orderid = oi.orderid
WHERE extract('hour' FROM shipped) < 5
AND oi.sku LIKE 'BKY%'
group by c.name, c.phone
order by lifetime_count desc
limit 1
Insert cell
Insert cell
db
SELECT distinct c.name, c.phone, c.citystatezip
FROM customers c
JOIN orders o ON c.customerid = o.customerid
JOIN orders_items oi ON o.orderid = oi.orderid
JOIN products p ON p.sku = oi.sku
WHERE c.citystatezip ILIKE '%Queens Village%' -- "a woman in Queens Village"
AND oi.sku LIKE 'PET%' -- "she only had ten or eleven cats"
AND p.desc LIKE '%Senior Cat%' -- "they were getting quite old"
AND o.ordered < DATE '2020-01-01' -- ¯\_(ツ)_/¯
Insert cell
Insert cell
db
SELECT p.wholesale_cost, oi.unit_price, o.ordered, c.name, c.phone
FROM orders_items oi
JOIN products p ON p.sku = oi.sku
JOIN orders o ON o.orderid = oi.orderid
JOIN customers c ON c.customerid = o.orderid
WHERE p.wholesale_cost > oi.unit_price
Insert cell
db
WITH orders as (
SELECT
c.name,
c.phone,
o.orderid,
SUM(oi.unit_price * oi.qty) as revenue,
SUM(p.wholesale_cost * oi.qty) as expense
FROM customers c
JOIN orders o ON o.customerid = c.customerid
JOIN orders_items oi ON oi.orderid = o.orderid
JOIN products p ON p.sku = oi.sku
GROUP BY c.name, c.phone, o.orderid
)

SELECT name, phone, count(*) as count, SUM(revenue) - SUM(expense) as lost
FROM orders
WHERE revenue < expense
GROUP BY name, phone
ORDER BY count DESC
Insert cell
Insert cell
Insert cell
db
WITH
colored_products AS (
SELECT
sku,
regexp_extract(p.desc, '(.+) \(([a-z]+)\)', 1) as product,
regexp_extract(p.desc, '.+ \(([a-z]+)\)', 1) as color
FROM products p
WHERE regexp_matches(p.desc, '.+ \([a-z]+\)')
),

colored_orders_by_day AS (
SELECT
o.orderid,
strftime('%Y-%m-%d', o.ordered) as day,
cp.sku,
cp.product,
cp.color,
o.customerid,
o.ordered
FROM orders o
JOIN orders_items oi ON oi.orderid = o.orderid
JOIN colored_products cp ON cp.sku = oi.sku
),

same_day_orders AS (
SELECT
o1.day,
o1.product,
c1.name as name1,
c1.phone as phone1,
o1.color as color1,
c2.name as name2,
c2.phone as phone2,
o2.color as color2,
abs(date_diff('minute', o1.ordered, o2.ordered)) as delta,
o1.ordered as ordered1,
o2.ordered as ordered2
FROM colored_orders_by_day o1
JOIN colored_orders_by_day o2 ON o1.day = o2.day
JOIN customers c1 ON c1.customerid = o1.customerid
JOIN customers c2 ON c2.customerid = o2.customerid
WHERE o1.customerid < o2.customerid
AND o1.product = o2.product
AND o1.color != o2.color
)

SELECT name1, phone1, name2, phone2, delta
FROM same_day_orders
WHERE ${answer6[0].name} IN (name1, name2)
ORDER BY delta ASC
Insert cell
Insert cell
Insert cell
db
SELECT c.name, c.phone, count(distinct p.sku) as count
FROM customers c
JOIN orders o USING (customerid)
JOIN orders_items oi USING (orderid)
JOIN products p USING (sku)
WHERE p.sku LIKE 'COL%'
GROUP BY c.name, c.phone
ORDER BY count DESC
LIMIT 1
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