The avatar for @fgregg
Forest Gregg
Public
Edited
Dec 12
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select * from ballot limit 50
Insert cell
Insert cell
Insert cell
db
select * from race
Insert cell
Insert cell
db
select * from ballot_race limit 50
Insert cell
Insert cell
Insert cell
db
select
ballot_race.id,
race_id,
race
from
ballot_race
inner join race on race_id = race.id
where
ballot_race.id = ${ballot_race_id}
Insert cell
Insert cell
db
select
ballot_race_id,
count(*)
from
ballot
group by
ballot_race_id
order by
count(*) desc;
Insert cell
Insert cell
db
select
count(*) as possible_voters
from
ballot
inner join ballot_race on ballot.ballot_race_id = ballot_race.id
inner join race on ballot_race.race_id = race.id
where
race = 'Establishing a Near Southwest MHS Program (Vote For=1)';
Insert cell
Insert cell
db
select * from vote limit 50
Insert cell
Insert cell
db
select
count(*) as skipping_voters
from
ballot
inner join ballot_race on ballot.ballot_race_id = ballot_race.id
inner join race on ballot_race.race_id = race.id
left join vote on ballot.id = vote.ballot_id
and race.id = vote.race_id -- IMPORTANT! joining by ballot and race
where
race = 'Establishing a Near Southwest MHS Program (Vote For=1)'
and vote.race_id is null
Insert cell
Insert cell
db
select * from option
Insert cell
Insert cell
db
select
coalesce(option.option, "skipped") AS response,
count(*)
from
ballot
inner join ballot_race on ballot.ballot_race_id = ballot_race.id
inner join race on ballot_race.race_id = race.id
left join vote on ballot.id = vote.ballot_id
and race.id = vote.race_id
left join option on vote.option_id = option.id
where
race = 'Establishing a Near Southwest MHS Program (Vote For=1)'
group by
option.option
Insert cell
Insert cell
db
WITH girl_i_guess_slate_voter AS (
SELECT
ballot_id
FROM
vote
INNER JOIN race ON vote.race_id = race.id
INNER JOIN option ON vote.option_id = option.id
WHERE
(race.race, option.option) IN (
('Thomas E. Hoffman (Vote For=1)', 'Yes'),
('David W. Ellis (Vote For=1)', 'Yes'),
('Stuart F. Lubin (Vote For=1)', 'Yes'),
('Kathy M. Flanagan (Vote For=1)', 'No'),
('Martin S. Agran (Vote For=1)', 'Yes')
)
GROUP BY
ballot_id
HAVING
COUNT(*) = 5
)
SELECT
COUNT(*)
FROM
girl_i_guess_slate_voter;
Insert cell
Insert cell
db
SELECT
option.id
FROM
race
INNER JOIN option ON race.id = option.race_id
WHERE
(race.race, option.option) IN (
('Thomas E. Hoffman (Vote For=1)', 'Yes'),
('David W. Ellis (Vote For=1)', 'Yes'),
('Stuart F. Lubin (Vote For=1)', 'Yes'),
('Kathy M. Flanagan (Vote For=1)', 'No'),
('Martin S. Agran (Vote For=1)', 'Yes')
)
Insert cell
Insert cell
db
WITH girl_i_guess_slate_voter AS (
SELECT
ballot_id
FROM
vote
WHERE
option_id in (
1,
3,
6,
7,
9,
11,
14,
16,
18,
20,
22,
23,
25,
27,
29,
32,
33,
35,
37,
39,
41,
43,
45,
47,
49,
51,
54,
55,
57,
59,
61,
63,
66,
67,
69,
71,
74,
75,
77,
79,
82,
83,
85,
87,
89,
91,
93,
95,
97,
99,
101,
103,
105,
107,
109,
111,
113,
115,
117,
120,
121,
124,
125,
128,
129,
131,
133,
135,
137,
140,
141,
143,
146,
147,
149,
151,
153,
155
)
GROUP BY
ballot_id
HAVING
COUNT(*) = 78
)
SELECT
COUNT(*)
FROM
girl_i_guess_slate_voter;
Insert cell
db = new DatasetteClient("https://puddle.bunkum.us/cvr_2024_11")
Insert cell
import { DatasetteClient } from "@fgregg/datasette-client-with-sql-cells-full-results-csv-stream"
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