The avatar for @xnought
xnought
Public
Edited
Mar 12, 2023
1
Insert cell
moviesdb = DuckDBClient.of({
data: FileAttachment("movies-3k.parquet")
})
Insert cell
moviesdb
SELECT * FROM data
Insert cell
Insert cell
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"
Insert cell
Insert cell
moviesdb
SELECT count(*) as count from data
WHERE Rotten_Tomatoes_Rating BETWEEN 0 AND 5 AND IMDB_Rating BETWEEN 6.41 AND 8.52
Insert cell
Insert cell
Insert cell
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"
Insert cell
Insert cell
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
Insert cell
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
Insert cell
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
Insert cell
moviesdb
SELECT
"IMDB_Rating" as keyActive,
count(*)
FROM data
GROUP BY keyActive
Insert cell
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
Insert cell
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
Insert cell
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
Insert cell
Insert cell
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
Insert cell
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
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