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
Nov 12, 2022
Fork of A2 Example: Movies Data
•
1 fork
2 stars
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
client = {
const data = await datasets["movies.json"]();
const c = await DuckDBClient.of({ movies_raw: data });

// Parse the release date from string into a timestamp
await c.query(`
CREATE VIEW movies AS
SELECT *, strptime("Release Date", '%b %d %Y') as "Parsed Release Date"
FROM movies_raw
`);
return c;
}
Insert cell
client
SELECT * FROM movies
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
client
CREATE OR REPLACE VIEW movies_enhanced AS
WITH
movies_with_years AS (
SELECT *, extract(year FROM "Parsed Release Date")::INT as year
FROM movies
),
filtered_movies AS (
SELECT *, ("Worldwide Gross" - "Production Budget") as "Profit"
FROM movies_with_years
WHERE
1995 <= year AND year <= 2010 AND
"Worldwide Gross" > 0
),
c AS (
SELECT "Major Genre", count()::INT AS cnt
FROM filtered_movies
GROUP BY "Major Genre"
),
movies_with_genre AS (
SELECT
CASE
-- Black Comedy -> Comedy
WHEN "Major Genre" = 'Black Comedy' THEN 'Comedy'
-- Null or < 100 films -> Other
WHEN "Major Genre" IS NULL OR (SELECT cnt FROM c WHERE "Major Genre" = m."Major Genre") < 100 THEN 'Other'
ELSE "Major Genre" END
AS "Genre",
*
FROM filtered_movies AS m
)
SELECT *
FROM movies_with_genre
Insert cell
// https://observablehq.com/@observablehq/derived-views-with-duckdb
client_enhanced = (moviesView, client)
Insert cell
client_enhanced
SELECT * FROM movies_enhanced
Insert cell
Insert cell
client_enhanced
SELECT "Genre", count(*)::INT as cnt
FROM movies_enhanced
GROUP BY "Genre"
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
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