The avatar for @credmond
Charles Redmond
Public
Edited
Oct 9, 2023
Fork of Sets
•
1 fork
5
Insert cell
Insert cell
db
SELECT playerID,round(sum(HR)) AS careerHR
FROM batting
GROUP BY playerID
HAVING careerHR > 600
ORDER BY careerHR DESC
Insert cell
db
SELECT DISTINCT playerID,round(sum(HR)) as careerHR
FROM batting AS b
WHERE 600 < (
SELECT sum(HR) as careerHR
from batting
where playerID = b.playerID
)
GROUP BY playerID
ORDER BY careerHR desc
Insert cell
db
SELECT DISTINCT playerID
FROM managers
WHERE 300 < (
SELECT sum(HR)
FROM batting
WHERE playerID = managers.playerID
)
Insert cell
db
SELECT DISTINCT playerID
FROM batting
WHERE 10 = (
SELECT count(yearID)
from managers
WHERE playerID = batting.playerID
)
Insert cell
db
SELECT playerID
FROM managers
GROUP BY playerID
HAVING count(yearID) > (
SELECT count(yearID)
FROM batting
WHERE playerID = managers.playerId
) AND EXISTS (
SELECT playerID
FROM batting
WHERE playerID = managers.playerID
)
Insert cell
db
SELECT DISTINCT playerID
FROM managers
WHERE NOT EXISTS (
SELECT playerID
FROM batting
WHERE playerID = managers.playerID
)
Insert cell
db
SELECT DISTINCT managers.playerID
FROM managers
INNER JOIN
(SELECT playerID
FROM batting
GROUP BY playerID
HAVING sum(HR) > 300) as b
ON managers.playerID = b.playerID

Insert cell
db
SELECT 'weak' AS name, 0 AS low, 199 AS high
UNION
SELECT 'medium' AS name, 200 AS low, 399 AS high
UNION
SELECT 'strong' AS name, 400 AS low, 800 AS high
Insert cell
db
SELECT name,count(*)
from (
SELECT playerID,sum(HR) AS careerHR
FROM batting
GROUP BY playerID
) AS b INNER JOIN (
SELECT 'weak' AS name, 0 AS low, 199 AS high
UNION
SELECT 'medium' AS name, 200 AS low, 399 AS high
UNION
SELECT 'strong' AS name, 400 AS low, 800 AS high
) AS g
ON b.careerHR between g.low AND g.high
group by name

Insert cell
db
WITH bighitters AS (
SELECT playerID,sum(HR) as careerHR
from batting
group by playerID
)
SELECT playerID
from bighitters
where careerHR > 600
Insert cell
db
SELECT (
SELECT nameFirst
FROM people
WHERE playerID = batting.playerID
) AS firstName,
(SELECT nameLast
FROM people
WHERE playerID = batting.playerID) AS lastName,
HR,teamID,yearID
FROM batting
Insert cell
Insert cell
db = DuckDBClient.of({
batting:FileAttachment("Batting.csv"),
hall:FileAttachment("HallOfFame.csv"),
managers:FileAttachment("Managers.csv"),
people:FileAttachment("People.csv"),
teams:FileAttachment("Teams.csv")
})
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