Public
Edited
Mar 12, 2023
1
moviesdb = DuckDBClient.of({
data: FileAttachment("movies-3k.parquet")
})
moviesdb
SELECT * FROM data
moviesdb
-- Okay! So this query is supposed to
SELECT
-- if inside range, compute, else store counts in -1 for all other
CASE WHEN IMDB_Rating BETWEEN 1 AND 9.5
-- bin for 400 pixels (1 pixel wide bins and 400 of them)
THEN FLOOR((IMDB_Rating - 1) / 0.02125)::INT
ELSE -1 END AS "keyActive",
count(*)::INT AS cnt FROM data
GROUP BY "keyActive"
moviesdb
SELECT count(*) as count from data
WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 5 AND IMDB_Rating BETWEEN 6.41 AND 8.52
moviesdb
-- I want to get the counts
SELECT
-- inside the range? count it as index -1, or otherwise attribute the count to the pixel index
CASE WHEN IMDB_Rating BETWEEN 1 AND 9.5
-- active pixel bins for 400 pixels
THEN FLOOR((IMDB_Rating - 1) / 0.02125)::INT
ELSE -1 END AS "keyActive",
-- passive bins for rotten tomatoes
FLOOR((Rotten_Tomatoes_Rating - 0) / 5)::INT AS "keyPassive",
-- aggregate counts
count(*)::INT AS count,
FROM data
-- enforece passive range/extent
WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 100
GROUP BY "keyActive", "keyPassive"
ORDER BY "keyPassive"
moviesdb
SELECT
CASE WHEN IMDB_Rating BETWEEN 0 AND 10
THEN FLOOR((IMDB_Rating - 0) / 1)::INT
ELSE -1 END AS "keyActive",
FLOOR((Rotten_Tomatoes_Rating - 0) / 20)::INT AS key,
count(*)::INT AS cnt,
FROM data
WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 100
GROUP BY "keyActive", key
ORDER BY key
moviesdb
-- (field - start / step) -> bins
-- step size <- (stop - start / numBins)
-- numBins <- 400
-- stop <- 400
-- start <- 0
-- there are only 9 bins when there should be 10
-- it should go from 0 to 10
SELECT FLOOR((IMDB_Rating - 0) / 1)::INT as key, count(*) from data
WHERE IMDB_Rating BETWEEN 0 AND 10
GROUP BY key
ORDER BY key
moviesdb
SELECT CASE WHEN IMDB_Rating BETWEEN 0 AND 10 THEN FLOOR((IMDB_Rating - 0) / 1)::INT ELSE -1 END AS "keyActive", count(*)::INT AS cnt FROM data GROUP BY "keyActive" ORDER BY keyActive
moviesdb
SELECT
"IMDB_Rating" as keyActive,
count(*)
FROM data
GROUP BY keyActive
moviesdb
SELECT
("Rotten_Tomatoes_Rating" - 0) / 20 as key
from data
WHERE "Rotten_Tomatoes_Rating" BETWEEN 0 AND 100 AND "Rotten_Tomatoes_Rating"
GROUP BY key
ORDER BY key
moviesdb
SELECT CASE WHEN IMDB_Rating BETWEEN 1 AND 9.5 THEN FLOOR((IMDB_Rating - 1) / 0.02125)::INT ELSE -1 END AS "keyActive", count(*)::INT AS cnt, FLOOR((Rotten_Tomatoes_Rating - 0) / 5)::INT AS key FROM data WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 100 GROUP BY "keyActive", key
moviesdb
SELECT CASE WHEN IMDB_Rating BETWEEN 1 AND 9.5 THEN FLOOR((IMDB_Rating - 1) / 0.02125)::INT ELSE -1 END AS "keyActive", count(*)::INT AS cnt FROM data GROUP BY "keyActive"
ORDER BY keyActive
moviesdb
SELECT LEAST(9, FLOOR((IMDB_Rating - 1) / 0.9)::INT)::INT AS "keyActive", count(*) FROM data
WHERE IMDB_Rating BETWEEN 1 AND 10 GROUP BY "keyActive" ORDER BY keyActive
moviesdb
SELECT
CASE WHEN IMDB_Rating BETWEEN 1 AND 10
THEN FLOOR((IMDB_Rating - 1) / 0.9)::INT
ELSE -1 END AS "keyActive",
FLOOR((Rotten_Tomatoes_Rating - 0) / 10)::INT AS key,
count(*)::INT AS cnt,
FROM data
WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 100
GROUP BY "keyActive", key
ORDER BY key, keyActive
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.
