The avatar for @observablehq
Observable
The end-to-end solution for building and hosting better data apps, dashboards, and reports.
Published
Edited
May 4, 2022
20 forks
Importers
12 stars
2
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select *
from sales
-- lets grab 1 day worth of data (the entire dataset is a month's worth
WHERE utc_timestamp > TIMESTAMP '2020-09-10'
AND utc_timestamp < TIMESTAMP '2020-09-11'
-- lets limit to purchases made in US Dollars to simplify exchange rates
AND currency = 'USD'
Insert cell
// the results are stored in the variable:
oneday
Insert cell
Insert cell
db = DuckDBClient.of({
sales: FileAttachment("1000000-bandcamp-sales@1.parquet") ,
})
Insert cell
Insert cell
Insert cell
db.describe("sales")
Insert cell
Insert cell
db
select count(*)::INT as count
from sales;
Insert cell
Insert cell
db
select * from sales
limit 1000;
Insert cell
// when we save the result of a query in a named SQL Cell we can access it via JavaScript
sample[0]
Insert cell
Plot.plot({
marks: [
Plot.barX(sample, Plot.groupY({x: "count"}, {y: "artist_name", sort: {y: "x", reverse: true, limit: 10}}))
],
marginLeft: 200
})
Insert cell
Insert cell
getArtUrl = u => `https://f4.bcbits.com/img/${u}`
Insert cell
getItemUrl = (u) => `https://${u.account}.bandcamp.com` + (u.type ? `/${u.type}` : "") + (u.slug ? `/${u.slug}` : "")
Insert cell
Insert cell
db
select * from sales
where currency = 'USD'
order by amount_paid desc
limit 100
Insert cell
Insert cell
topdollarUrls = topdollar.map(d => {
return {
...d,
url: getItemUrl(d),
art_url: getArtUrl(d.art_url)
}
})
Insert cell
Insert cell
Inputs.table(topdollarUrls, {
columns: ["artist_name", "item_description", "amount_paid", "currency", "url"],
format: { url: r => html`<a href="${r}">${r}</a>` },
width: { amount_paid: "60px", currency: "40px", artist_name: "200px" },
})
Insert cell
Insert cell
Plot.plot({
marks: [
Plot.ruleY([0]),
Plot.image(topdollarUrls, {
x: "utc_timestamp",
y: "amount_paid",
src: "art_url"
})
],
insetTop: 10,
insetLeft: 10,
})
Insert cell
Insert cell
Insert cell
db
select type, physical, count(*)::INT as count
from sales
group by type, physical
Insert cell
Insert cell
Plot.plot({
marks: [
Plot.barY(bytype, {x: "type", y: "count", fill: "physical"})
],
color: { legend: true, tickFormat: d => d ? "physical" : "digital" },
marginLeft: 60
})
Insert cell
Insert cell
db
select currency, count(*)::INT as count, sum(item_price)::INT as prices, sum(amount_paid)::INT as revenue
from sales

group by currency
order by count desc
Insert cell
Plot.plot({
marks: [
Plot.barX(bycurrency, {x: "count", y: "currency", sort: { "y": "x", reverse: true }})
]
})
Insert cell
Insert cell
exchangeRates = d3.json("https://open.er-api.com/v6/latest/USD")
Insert cell
bycurrencyUSD = bycurrency.map(d => {
return {
...d,
prices_usd: Math.round(d.prices / exchangeRates.rates[d.currency]),
revenue_usd: Math.round(d.revenue / exchangeRates.rates[d.currency]),
}
})
Insert cell
Inputs.table(bycurrencyUSD)
Insert cell
Now we can plot monetary values
Insert cell
Plot.plot({
marks: [
Plot.barX(bycurrencyUSD, {x: "revenue_usd", y: "currency", fillOpacity: 0.55, fill: "green", sort: { "y": "x", reverse: true }}),
Plot.barX(bycurrencyUSD, {x: "prices_usd", y: "currency", fillOpacity: 0.55, fill: "steelblue", sort: { "y": "x", reverse: true }}),
]
})
Insert cell
Insert cell
process = (data) => {
return data.map(d => {
return {
...d,
price_usd: d.item_price/exchangeRates.rates[d.currency], // item_price in USD
paid_usd: d.amount_paid/exchangeRates.rates[d.currency], // amount_paid in USD
url: getItemUrl(d), // full url to item
art_url: getArtUrl(d.art_url) // full url to artwork
}
})
}
Insert cell
processed = process(sample)
Insert cell
Inputs.table(processed)
Insert cell
Plot.plot({
caption: "histogram of how much people paid",
marks: [
Plot.rectY(processed, Plot.binX({y: "sum"}, {x: "paid_usd"}))
],
})
Insert cell
Plot.plot({
caption: "histogram of how much people paid",
facet: {
data: processed,
x: "type_label"
},
marks: [
Plot.frame(),
Plot.rectY(processed, Plot.binX({y: "sum"}, {x: "paid_usd"}))
],
grid: true,
inset: 5,
width: width
})
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