Public
Edited
Oct 8, 2024
Fork of SQL + Chart
Insert cell
Insert cell
Insert cell
database
select count(*), status_class from links group by status_class;
Insert cell
Insert cell
{
const data = prepData(month_count);
return Plot.plot({
width: 1200,
x: {domain: [new Date("1998-04-01"), new Date("2023-04-01")], grid: true},
y: {grid: true},
marks: [
Plot.areaY(month_count, {
x: "date",
y: "count",
interval: d3.utcMonth,
// fill: "status_class",
// sort: {x: "-y"}
}),
Plot.ruleY([0])
]
})
}
Insert cell
Insert cell
{
const parseDate = d3.utcParse("%Y-%m-%d");
const data = prepData(monthly_by_statusclass);

return Plot.plot({
width: 1200,
y: {grid: true},
color: {legend: true},
marks: [
Plot.rectY(data, {x: "date", y: "count", interval: d3.utcMonth, fill: "status_class"}),
Plot.ruleY([0])
]
})
}
Insert cell
{
const data = prepData(monthly_by_statusclass);

return Plot.plot({
width: 1200,
x: {domain: [new Date("1998-04-01"), new Date("2023-04-01")], grid: true},
y: {
percent: true
},
color: {
legend: true,
domain: ["ERROR", "REDIRECT", "SUCCESS"],
range: ["#DE1E3E", "#FFB516", "#3CAB34"]
},
marks: [
Plot.rectY(data, Plot.stackY({
offset: "normalize",
order: "appearance",
reverse: true
}, {x: "date", y: "count", z: "status_class", fill: "status_class", interval: d3.utcMonth})),
Plot.ruleY([0, 1])
]
})
}
Insert cell
prepData = {
const parseDate = d3.utcParse("%Y-%m-%d");
return (data) =>
data
.filter(({ status_class }) => status_class !== "NO_VALIDATE")
.map(({ count, date, status_class }) => ({
count,
date: parseDate(date),
status_class: ["REDIRECT_GENERIC", "REDIRECT_OTHER"].includes(status_class) ? "REDIRECT" : status_class
}));
}
Insert cell
database
select href, status_class from links order by href;
Insert cell
offenders = {
const groups = {};
const regex = /^https?:\/\/([^/?#]+)(?:[/?#]|$)/i;
for (let i = 0, len = all_data.length; i < len; i++) {
const link = all_data[i];

if (['https:/', 'http://'].includes(link.href)) continue

const hostname = regex.exec(link.href)[1];
if (hostname in groups) {
groups[hostname] = [...groups[hostname], link.status_class];
} else {
groups[hostname] = [link.status_class];
}
}

const topLinked = Object.keys(groups)
.reduce((acc, key) => {
const count = groups[key].length;

return [
...acc,
[key, count]
];
}, [])
.sort((a, b) => b[1] - a[1]);

const sortedBySuccess = Object.keys(groups)
.reduce((acc, key) => {
const count = groups[key].length;
const successCount = groups[key].filter(code => code === "SUCCESS").length;
const successRatio = successCount / count;
if (count >= 10) {
return [
...acc,
[key, successRatio]
]
}
return acc;
}, [])
.sort((a, b) => a[1] - b[1]);

return sortedBySuccess;
}
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