getSequenceAwareDailyActivitySQL sequences per-species instead of the global media stream

[Chouffe]

Summary

getSequenceAwareDailyActivitySQL (src/main/database/queries/species.js) groups the time-gap sequence window with PARTITION BY scientificName:

WINDOW w AS (PARTITION BY scientificName ORDER BY ts, mediaID)
... SUM(is_new) OVER (PARTITION BY scientificName ORDER BY ts, mediaID ...) AS seq_id
... GROUP BY scientificName, hour, seq_id

But the reference sequence semantics (groupMediaIntoSequences + calculateSequenceAwareSpeciesCounts in src/main/services/sequences/) group media into sequences over the global media stream (one media → one sequence, by deployment + time gap + video), and only then take per-species MAX within each sequence. Sequencing per-species is wrong: a media of another species that falls between two media of species X should bridge the gap, keeping X's media in one sequence. Per-species partitioning instead splits them into two sequences and over-counts.

This is the same bug that was fixed for getSequenceAwareSpeciesCountsSQL and getSequenceAwareTimeseriesSQL in #570 (rewritten to sequence at the media level, then join per-species counts). It was caught there by a randomized parity fuzz test. The fix surfaces at larger gaps, where bridging across species is more likely.

Why it slipped through

Daily-activity is SQL-only — there is no calculateSequenceAwareDailyActivity JS reference and no parity test, so nothing compares it against the intended semantics. (getSequenceAwareHeatmapSQL is NOT affected — it correctly partitions the window at the media level by (latitude, longitude).)

Fix

Apply the same media-level pattern as #570: sequence a media-level CTE (one row per media, PARTITION BY nothing for global / or the appropriate location grouping, ORDER BY ts, mediaID), assign seq_id, then join per-(species, media) counts and take MAX per (species, hour, seq_id). Add a parity oracle + test (e.g. derive an expected value via the existing calculateSequenceAwareSpeciesCounts building blocks) and a fuzz case, mirroring test/main/database/queries/sequenceAwareSQL.fuzz.test.js.

Severity

Medium — wrong daily-activity (hourly) counts on multi-species studies with positive sequence gaps; magnitude grows with gap size.