The avatar for @cmudig
CMU Data Interaction Group
We are a research group at the CMU HCII. Our mission is to empower people to analyze and communicate data with interactive systems.
Public
Edited
Sep 29, 2023
6 forks
Importers
49 stars
2
Insert cell
Insert cell
Insert cell
Insert cell
client1 = {
// create a new client
const c = await DuckDBClient.of();

// create a table called `dt` with three columns
await c.query(`CREATE TABLE dt(u STRING, x INTEGER, y FLOAT)`);
// insert values into `dt`
await c.query(
`INSERT INTO dt VALUES ('a', 1, 5), ('b', 2, 6), ('c', 3, 7), ('d', 4, 8);`
);

return c;
}
Insert cell
Insert cell
Inputs.table(client1.describeTables())
Insert cell
Inputs.table(client1.describeColumns({ table: "dt" }))
Insert cell
Insert cell
client1
SELECT u FROM dt
Insert cell
Insert cell
client2 = DuckDBClient.of({
beer: FileAttachment("beer.csv"),
breweries: FileAttachment("breweries.csv")
})
Insert cell
Insert cell
Inputs.table(client2.describeColumns({ table: "beer" }))
Insert cell
Inputs.table(client2.describeColumns({ table: "breweries" }))
Insert cell
Insert cell
client2
SELECT * FROM beer
Insert cell
Insert cell
client2
SUMMARIZE beer
Insert cell
Insert cell
Insert cell
client2
SELECT name, style, ibu
FROM beer
Insert cell
client2
SELECT name AS 'Beer Name'
FROM beer
Insert cell
Insert cell
client2
SELECT name, style, ibu
FROM beer
LIMIT 5
Insert cell
Insert cell
client2
SELECT name, style, abv
FROM beer
ORDER BY abv
LIMIT 5
Insert cell
Insert cell
client2
SELECT name, style, abv
FROM beer
ORDER BY abv DESC
LIMIT 5
Insert cell
Insert cell
client2
SELECT *
FROM beer
WHERE style = 'Kölsch'
Insert cell
Insert cell
client2
SELECT *
FROM beer
WHERE name LIKE '%Hop%'
Insert cell
Insert cell
Insert cell
client2
SELECT *, 3 * abv + log10(ibu) / 3 AS intensity
FROM beer
ORDER BY intensity DESC
Insert cell
Insert cell
client2
SELECT count()::INT as count
FROM beer
Insert cell
Insert cell
client2
SELECT count(DISTINCT name)::INT as count
FROM beer
Insert cell
Insert cell
client2
SELECT
avg(abv),
avg(ibu),
avg(3 * abv + log10(ibu) / 3) AS 'avg(intensity)',
corr(abv, ibu),
count()::INT AS count
FROM beer
Insert cell
Insert cell
client2
SELECT
style,
avg(abv),
avg(ibu),
avg(3 * abv + log10(ibu) / 3) AS "avg(intensity)",
count()::INT AS count
FROM beer
GROUP BY style
HAVING count() > 20 -- let's not trust results with little support...
ORDER BY "avg(intensity)" DESC
Insert cell
Insert cell
Insert cell
client2
SELECT
style,
arg_max(name, 3 * abv + log10(ibu) / 3) as "name",
max(3 * abv + log10(ibu) / 3) AS "intensity"
FROM beer
GROUP BY style
HAVING count() > 20
ORDER BY "intensity" DESC
Insert cell
Insert cell
client2
WITH beers_intensity AS (
SELECT
*,
3 * abv + log10(ibu) / 3 AS "intensity"
FROM beer
)
SELECT
style,
arg_max(name, intensity),
avg(intensity)
FROM beers_intensity
GROUP BY style
HAVING count() > 20
ORDER BY avg(intensity) DESC
Insert cell
Insert cell
client2
CREATE OR REPLACE VIEW beers_intensity AS
SELECT
*,
3 * abv + log10(ibu) / 3 AS "intensity"
FROM beer
Insert cell
client_with_intensity = (beerView, client2)
Insert cell
client_with_intensity
SELECT * FROM beers_intensity
Insert cell
Insert cell
Insert cell
client2
SELECT
name, style, abv, ibu,
avg(3 * abv + log10(ibu) / 3) OVER(PARTITION BY style) AS "avg(intensity)"
FROM beer
Insert cell
Insert cell
client2
WITH beers_intensity AS (
SELECT
*,
3 * abv + log10(ibu) / 3 AS "intensity"
FROM beer
)
SELECT
name, style, abv, ibu,
COALESCE((intensity - avg(intensity) OVER(w))
/ stddev_samp(intensity) OVER(w), 0)
AS "zscore"
FROM beers_intensity
WINDOW w as (PARTITION BY style)
ORDER BY zscore DESC
Insert cell
Insert cell
client2
WITH beers_intensity AS (
SELECT
*,
3 * abv + log10(ibu) / 3 AS "intensity"
FROM beer
),
beer_zscore AS (
SELECT
*,
COALESCE((intensity - avg(intensity) OVER(w))
/ stddev_samp(intensity) OVER(w), 0)
AS "zscore",
rank() OVER(PARTITION BY style ORDER BY intensity DESC) AS rank,
count() OVER(w) AS count
FROM beers_intensity
WINDOW w AS (PARTITION BY style)
ORDER BY zscore DESC
)
SELECT name, style, abv, ibu, zscore
FROM beer_zscore
WHERE rank = 1 AND count > 20
Insert cell
Insert cell
client2
SELECT arg_max(name, abv), max(abv)
FROM beer
Insert cell
client2
SELECT name, abv
FROM beer
WHERE abv = (
SELECT max(abv)
FROM beer
)
Insert cell
Insert cell
client2
SELECT style, name, abv
FROM beer AS beer_parent
WHERE abv = (
SELECT max(abv)
FROM beer
WHERE beer.style = beer_parent.style
)
Insert cell
Insert cell
client2
SELECT arg_max(style, abv), arg_max(name, abv), max(abv)
FROM beer
GROUP BY style
Insert cell
Insert cell
client2
SELECT *
FROM breweries
Insert cell
Insert cell
client2
SELECT *
FROM beer JOIN breweries USING (brewery_id)
LIMIT 5
Insert cell
Insert cell
client2
SELECT *
FROM beer JOIN breweries ON (beer.brewery_id = breweries.brewery_id)
LIMIT 5
Insert cell
Insert cell
client2
SELECT *
FROM beer NATURAL JOIN breweries
LIMIT 5
Insert cell
Insert cell
client2
SELECT *
FROM beer, breweries
WHERE beer.brewery_id = breweries.brewery_id
LIMIT 5
Insert cell
Insert cell
client2
SELECT
beer.name,
beer.style,
beer.abv,
beer.ibu,
breweries."brewery-name",
breweries.city,
breweries.state
FROM beer, breweries
WHERE beer.brewery_id = breweries.brewery_id
LIMIT 5
Insert cell
Insert cell
client2
SELECT
beer.style,
count(DISTINCT breweries."brewery-name")::INT as breweries
FROM beer, breweries
WHERE beer.brewery_id = breweries.brewery_id
GROUP BY beer.style
ORDER BY breweries DESC
Insert cell
Insert cell
Insert cell
Insert cell
client_with_intensity
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more