-
-
Save leonid-shevtsov/dd4db708d0172993d6260a8caf5eefdd to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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