Public
Edited
Aug 16, 2024
2 forks
Importers
Insert cell
Insert cell
Insert cell
db
with cases as (
select
substring(case_number, 0, 13) as case_number,
ctype as case_type,
cast(strftime('%Y', file_date) as int) as year
from
[chips].c_master
union all
select
case_number,
ctype,
cast(strftime('%Y', file_date) as int) as year
from
[chips].r_master
union all
select
substr(r_case_number, 1, 6) || '0' || substr(r_case_number, 7, 5) as case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[cats].r_case
union all
select
substr(c_case_number, 1, 6) || '0' || substr(c_case_number, 7, 5) as case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[cats].c_case
union all
select
case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[nlrb].filing
)
select
year,
case_type,
count(distinct case_number) as count
from
cases
where
year >= 1984
and year < cast(strftime('%Y', 'now') as int)
group by
year,
case_type
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