The avatar for @asg017
Alex Garcia
Software engineer, available for freelance work! • he/him/his
Published
SQLite Blog
Edited
Sep 13, 2022
2 stars
3
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
import { DatasetteClient } from "@ambassadors/datasette-client"
Insert cell
db = new DatasetteClient("https://sqlite-extension-examples.fly.dev/data")
Insert cell
Insert cell
db
select url_version(), url('https://alexgarcia.xyz/');
Insert cell
Insert cell
db
select * from washingtonpost_links;
Insert cell
Insert cell
db
select url_host(url), count(*)
from washingtonpost_links
where url_valid(url)
group by 1
order by 2 desc;
Insert cell
Insert cell
db
select url, text
from washingtonpost_links
where not url_valid(url)
order by 1
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select
path_at(url_path(url), 0) as news_section,
count(*)
from washingtonpost_links
-- filter out invalid URLs
where news_section is not null
group by 1
order by 2 desc
Insert cell
Insert cell
Insert cell
Insert cell
db
select
-- validate as a real date with sqlite's date() function
date(
-- get '2022-09-06' from 'https://www.washingtonpost.com/national-security/2022/09/06/trump-nuclear-documents/'
printf('%s-%s-%s',
path_at(url_path(url), 1),
path_at(url_path(url), 2),
path_at(url_path(url), 3)
)
) as date,
count(*)
from washingtonpost_links
-- filter out invalid dates
where date is not null
group by 1
order by 1;
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select
distinct path_at(url_path(url), -1) as slug,
(
select json_group_array(line)
from lines(path_at(url_path(url), -1), '-')
) as slug_words
from washingtonpost_links
where path_at(url_path(url), 1) = '2022'
order by 1;
Insert cell
Insert cell
db
select
slug_words.line as word,
count(*)
from washingtonpost_links
join lines(path_at(url_path(url), -1), '-') as slug_words
where path_at(url_path(url), 1) = '2022'
group by 1
order by 2 desc
limit 15
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
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