Use CL opinions db to Update Reporters DB dates

[mlissner]

I was just playing with the citation finder on CourtListener, and it failed to return a top result for this query:

https://www.courtlistener.com/?q=941%20F.2d%20309&type=o&order_by=score%20desc&stat_Published=on

That looks up 941 F.2d 309, and the reason that didn't find results is because the case is from 1991, while the key for F.2d says 1993:

"F.2d": {
    "end": "1993-12-31T00:00:00",
    "start": "1924-01-01T00:00:00"
},

So either the case is wrong (I haven't double checked) or reporters-db is wrong. Furthermore, it looks like we don't have the date-level granularity on the fields (it says Dec. 31 and Jan 1), so that'd be nice to fix.

Probably something we can do programmatically from the database, and that we might even be able to easily do for all keys in reporters DB?

[quevon24]

I ran a query using the bulk data to try to find the min and max filed dates for each citation reporter so we can update the dates in reporters-db, this is the file with the results, it includes the cluster_id where you can find the min and max date:

min_max_202503121502.csv

Considering only the year, we need to update the start date of approximately 202 reporters editions.

How granular do we want the update to be? We could simply update the year of the start date and keep the first month and first day for the start date.

Should we also update the end date?

It was interesting to run this query, I found an opinion from 1658: https://www.courtlistener.com/opinion/6784388/go/

This is the query i used to generate the csv:

WITH unique_reporters AS (
    SELECT DISTINCT reporter 
    FROM search_citation
),
filtered_clusters AS (
    SELECT sc.reporter, sc.cluster_id
    FROM search_citation sc
),
date_ranges AS (
    SELECT 
        fc.reporter,
        soc.id AS cluster_id,
        soc.date_filed
    FROM search_opinioncluster soc
    JOIN filtered_clusters fc ON soc.id = fc.cluster_id
),
min_max AS (
    SELECT 
        reporter,
        MIN(date_filed) AS min_date_filed,
        MAX(date_filed) AS max_date_filed
    FROM date_ranges
    GROUP BY reporter
)
SELECT 
    mm.reporter,
    mm.min_date_filed,
    (SELECT cluster_id FROM date_ranges WHERE date_filed = mm.min_date_filed AND reporter = mm.reporter LIMIT 1) AS cluster_id_min,
    mm.max_date_filed,
    (SELECT cluster_id FROM date_ranges WHERE date_filed = mm.max_date_filed AND reporter = mm.reporter LIMIT 1) AS cluster_id_max
FROM min_max mm
ORDER BY mm.reporter ASC;

[quevon24]

@mlissner I don't think this issue exists anymore, but in the future we could improve the data in reporters-db by setting the start and end date based on what we have in courtlistener

[mlissner]

Nice. Yeah, I think we can/should keep this open to update the reporters DB with this data. I'll tweak the title accordingly.