The avatar for @seafowl
Seafowl
Seafowl is an analytical database designed for modern data-driven Web applications.
Public
Edited
Aug 3, 2023
Insert cell
Insert cell
Insert cell
import {SeafowlDatabase} from "@seafowl/client"
Insert cell
database = new SeafowlDatabase({host: "https://socfeed-data.splitgraph.io"})
Insert cell
Insert cell
database
SELECT COUNT(*) FROM socrata.dataset_history
Insert cell
database
SELECT sg_image_created, sg_image_tag, COUNT(*) AS total_datasets FROM socrata.dataset_history GROUP BY 1, 2 ORDER BY 1 ASC;
Insert cell
Plot.plot({
marks: [
Plot.line(dataset_count, {x: "sg_image_created", y: "total_datasets"})
]
})
Insert cell
Insert cell
database
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
database
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
database
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
database
SELECT
month,
d.domain,
d.name,
is_added,
d.id,
d.description
FROM socrata.monthly_diff m INNER JOIN socrata.all_datasets d
ON m.id = d.id
ORDER BY 1, 2, 3
Insert cell
Insert cell
viewof week = Inputs.select(allWeeks.map(d => d.week), {label: "Week", value: allWeeks[allWeeks.length - 1].week})
Insert cell
md`
## Total:
**Added: ${d3.sum(domainAddedRemoved.map(d => d.added))}**
**Removed: ${d3.sum(domainAddedRemoved.map(d => d.removed))}**

## By domain:

${domainAddedRemoved.map(d => " * **" + d.domain + "**: added " + d.added + ", removed: " + d.removed + "\n")}

## Details:

${Array.from(
d3.group(weekSummary, d => d.domain), ([key, values]) =>
"### " + key + "\n#### Added\n" +
values.filter(d => d.is_added).map(d => "\n - " + d.name) +
"\n\n#### Removed\n" +
values.filter(d => !d.is_added).map(d => "\n - " + d.name) + "\n\n")}
`
Insert cell
Insert cell
weekSummary = database.sql`
SELECT
d.domain,
d.name,
is_added,
w.id,
d.description
FROM socrata.weekly_diff w INNER JOIN socrata.all_datasets d
ON w.id = d.id
WHERE w.week::text = ${week}
ORDER BY 1, 3, 2
`
Insert cell
domainAddedRemoved = database.sql`
SELECT
d.domain,
SUM(CASE WHEN w.is_added THEN 1 ELSE 0 END) AS added,
SUM(CASE WHEN w.is_added THEN 0 ELSE 1 END) AS removed
FROM socrata.weekly_diff w INNER JOIN socrata.all_datasets d
ON w.id = d.id
WHERE w.week::text = ${week}
GROUP BY 1 ORDER BY 1 ASC
`
Insert cell
allWeeks = database.sql`SELECT DISTINCT week FROM socrata.weekly_diff ORDER BY week ASC`
Insert cell
Insert cell
database
(SELECT
substr(day::text, 0, 11) AS timestamp,
'prev_day' AS direction
FROM socrata.daily_diff
WHERE day < '2022-11-05 00:00:00'::timestamp
ORDER BY day DESC LIMIT 1)

UNION ALL

(SELECT
substr(day::text, 0, 11) AS timestamp,
'next_day' AS direction
FROM socrata.daily_diff
WHERE day > '2022-11-05 00:00:00'::timestamp
ORDER BY day ASC LIMIT 1)

UNION ALL

(SELECT
substr(day::text, 0, 11) AS timestamp,
'equivalent_day' AS direction
FROM socrata.daily_diff
WHERE day <= '2022-11-05 00:00:00'::timestamp
ORDER BY day DESC LIMIT 1)

UNION ALL
(SELECT
substr(week::text, 0, 11) AS timestamp,
'prev_week' AS direction
FROM socrata.weekly_diff
WHERE week < '2022-11-05 00:00:00'::timestamp
ORDER BY week DESC LIMIT 1)

UNION ALL

(SELECT
substr(week::text, 0, 11) AS timestamp,
'next_week' AS direction
FROM socrata.weekly_diff
WHERE week > '2022-11-05 00:00:00'::timestamp
ORDER BY week ASC LIMIT 1)

UNION ALL

(SELECT
substr(week::text, 0, 11) AS timestamp,
'equivalent_week' AS direction
FROM socrata.weekly_diff
WHERE week <= '2022-11-05 00:00:00'::timestamp
ORDER BY week DESC LIMIT 1)

UNION ALL

(SELECT
substr(month::text, 0, 11) AS timestamp,
'prev_month' AS direction
FROM socrata.monthly_diff
WHERE month < '2022-11-05 00:00:00'::timestamp
ORDER BY month DESC LIMIT 1)

UNION ALL

(SELECT
substr(month::text, 0, 11) AS timestamp,
'next_month' AS direction
FROM socrata.monthly_diff
WHERE month > '2022-11-05 00:00:00'::timestamp
ORDER BY month ASC LIMIT 1)

UNION ALL

(SELECT
substr(month::text, 0, 11) AS timestamp,
'equivalent_month' AS direction
FROM socrata.monthly_diff
WHERE month <= '2022-11-05 00:00:00'::timestamp
ORDER BY month DESC LIMIT 1)

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