Public
Edited
Oct 24, 2023
Fork of CASE Part 2
1 fork
Insert cell
Insert cell
battingFile = FileAttachment("Batting.csv").csv({typed:true})
Insert cell
collegeFile = FileAttachment("CollegePlaying.csv").csv({typed:true})
Insert cell
peopleFile = FileAttachment("People.csv").csv({typed:true})
Insert cell
managersFile = FileAttachment("Managers.csv").csv({typed:true})
Insert cell
hallFile = FileAttachment("HallOfFame.csv").csv({typed:true})
Insert cell
db = DuckDBClient.of({
college:collegeFile,
batting:battingFile,
people:peopleFile,
managers:managersFile,
hall:hallFile
})
Insert cell
db
SELECT playerID,
CASE count(DISTINCT schoolID)
WHEN 1 THEN 'ONE COLLEGE'
WHEN 2 THEN 'TWO COLLEGES'
WHEN 3 THEN 'THREE COLLEGES'
END colleges
FROM college
GROUP BY playerID
Insert cell
db
SELECT playerID,CASE(
SELECT count(DISTINCT schoolID)
FROM college
WHERE playerID = people.playerID
)
WHEN 1 THEN 'ONE COLLEGE'
WHEN 2 THEN 'TWO COLLEGES'
WHEN 3 THEN 'THREE COLLEGES'
ELSE 'NO COLLEGE'
END
FROM people
Insert cell
db
SELECT playerID,
CASE
WHEN (SELECT count(*)
FROM managers
WHERE playerID = people.playerID) = 0 THEN 'NEVER MANAGED'
ELSE (SELECT count(*)
FROM managers
WHERE playerID = people.playerID)
END

FROM people
Insert cell
db
SELECT DISTINCT playerID,nameFirst,nameLast,
CASE (SELECT max(inducted)
from hall
WHERE playerID = people.playerID)
WHEN 'Y' THEN 'inducted'
WHEN 'N' THEN 'considered'
ELSE 'not considered'
END
FROM people

Insert cell
db
SELECT *
FROM hall
WHERE playerID = 'abbotji01'
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