The avatar for @urvi
urvi
Public
Edited
Mar 19, 2023
Fork of SQL + Chart
•
1 fork
3 stars
1
Insert cell
Insert cell
Insert cell
the_oscar_award.csv
SELECT * FROM the_oscar_award;
Insert cell
Insert cell
the_oscar_award.csv
SELECT film, COUNT(*) as num_wins
FROM the_oscar_award
WHERE winner = 'True'
GROUP BY film
ORDER BY num_wins DESC;
Insert cell
Insert cell
the_oscar_award.csv
SELECT name, COUNT(*) as num_noms, SUM(case when winner = 'True' then 1 else 0 end) as num_wins
FROM the_oscar_award
GROUP BY name
ORDER BY num_wins DESC, num_noms DESC;
Insert cell
Insert cell
the_oscar_award.csv
SELECT category, COUNT(category) as num_noms
FROM the_oscar_award
GROUP BY category
ORDER BY num_noms DESC;
Insert cell
Insert cell
the_oscar_award.csv
SELECT film, COUNT(*) as num_noms
FROM the_oscar_award
WHERE winner = 'False'
GROUP BY film
ORDER BY num_noms DESC;
Insert cell
Insert cell
the_oscar_award.csv
SELECT year_film, COUNT(*) as num_noms, SUM(case when winner = 'True' then 1 else 0 end) as num_wins
FROM the_oscar_award
GROUP BY year_film
ORDER BY year_film;
Insert cell
Insert cell
the_oscar_award.csv
SELECT year_film, name, num_noms, RANK() OVER (PARTITION BY year_film ORDER BY num_noms DESC) as rank
FROM (
SELECT year_film, name, COUNT(*) as num_noms
FROM the_oscar_award
WHERE winner = 'False'
GROUP BY year_film, name
) as subquery;
Insert cell
Insert cell
the_oscar_award.csv
SELECT distinct year_film, name, num_noms, AVG(num_noms) OVER (
PARTITION BY name
ORDER BY year_film
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as moving_avg
FROM (
SELECT distinct year_film, name, COUNT(*) as num_noms
FROM the_oscar_award
WHERE winner = 'False'
GROUP BY year_film, name
) as subquery;
Insert cell
Insert cell
the_oscar_award.csv
SELECT year_film, film, category, num_noms, 100.0 * num_noms / SUM(num_noms) OVER (PARTITION BY year_film, category) as pct_noms
FROM (
SELECT year_film, film, category, COUNT(*) as num_noms
FROM the_oscar_award
WHERE winner = 'False'
GROUP BY year_film, film, category
) as subquery;
Insert cell
Insert cell
the_oscar_award.csv
SELECT year_film, category, 100.0 * SUM(case when winner = 'True' then 1 else 0 end) / COUNT(*) OVER (PARTITION BY year_film, category) as win_pct
FROM the_oscar_award
group by year_film, category;
Insert cell
Select a data source…
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Plot.plot({
marks: [
Plot.barX(urvi, Plot.groupY({x: "count"}, {y: "category", sort: {y: "x", reverse: true, limit: 10}})),
Plot.ruleX([0])
]
})
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