Skip to content

Instantly share code, notes, and snippets.

@leonid-shevtsov
Created December 4, 2024 21:07
Show Gist options
  • Save leonid-shevtsov/dd4db708d0172993d6260a8caf5eefdd to your computer and use it in GitHub Desktop.
Save leonid-shevtsov/dd4db708d0172993d6260a8caf5eefdd to your computer and use it in GitHub Desktop.
-- Build a sequence of all samples
WITH pingSequence as (
select pingDate
from sample
),
-- For each sample, mark whether it is tagged, and whether the previous was tagged
taggingStatus as (
select pingDate,
tagging.samplePingDate is not null isTagged,
lag(tagging.samplePingDate is not null) over (
order by pingDate
) isPrevTagged
FROM pingSequence
left join tagging on tagging.samplePingDate = pingDate
and tagging.tagUuid = "tagUUID"
),
-- Assign group numbers on status changes
groupNumbers as (
select pingDate,
isTagged,
count(*) filter (
where isTagged
and not isPrevTagged
or not isTagged
and isPrevTagged
) over (
order by pingDate
) groupNumber
from taggingStatus
),
-- Count size of every group
groupCounts as (
select count(*) groupLen
from groupNumbers
where isTagged
group by groupNumber
) -- Finally, gather stats for group sizes
select groupLen duration,
count(*) frequency
from groupCounts
group by groupLen
order by groupLen asc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment