Help needed in Arctos: Tracking the taxonomic growth of our collection

[falco-rk]

We often use the growth in the number of species in our collection as a metric to track our growth over time. In our old database, this was "easy" to do because we added taxa individually so we could just look at how many new taxa records were generated in a year. Since that is not how Arctos works, is there a way to compare our taxonomic breakdown by date?

I would like to make this plot:

Where gray bars represent taxa new to the collection and teal bars indicate new representatives of taxa already in the collection. The blue lines track the cumulative growth of taxa in the OGL Collection (right axes).

This is gonna be a thing we will need to do on at least an annual basis, but possibly more frequently.

@happiah-madson @dustymc

[dustymc]

@mkoo might be able to address this (pretty bars and all) via https://github.com/ArctosDB/internal/issues/364.

Or:

select
    extract(year from entereddate + interval '6 month') as fiscal_year,
    count(distinct(identification_taxonomy.taxon_name_id)) numTaxa,
    count(distinct(flat.guid)) numRecords
from
    flat
    inner join identification on flat.collection_object_id=identification.collection_object_id
    inner join identification_taxonomy on identification.identification_id=identification_taxonomy.identification_id
where
    guid_prefix='OGL:Genomic'
group by
    fiscal_year
order by
    fiscal_year
;

 fiscal_year | numtaxa | numrecords 
-------------+---------+------------
        2024 |    5311 |      34117
        2025 |      20 |        359