Peak visit counts by datetime period

from the Artful Common Queries page

You have a visits table (id int, start datetime, end datetime), and you wish to track peak visit counts. A simple solution is to self-join on non-matching IDs and overlapping visit times, group by ID, then order by the resulting counts:
SELECT,group_concat( as Overlaps, count( as OverlapCount
FROM visits a
JOIN visits b on < and a.start < b.end and b.start < a.end
ORDER BY OverlapCount DESC;

Last updated 22 May 2009

Return to the Artful Common Queries page