CODEWITHSUNDEEP
mysqlsqldatetimeaggregate
david k
david k

Reputation: 23

count events within N days of first occurrence

I have a series of rows in a table (pageviews of various articles) with columns for URL and timestamps, and I would like to see the total number of pageviews for each article within N days of its first pageview.

I can use "GROUP BY url" to view the total pageviews on each article, and I can use a WHERE clause to only count pageviews within a certain hard-coded date range, but I need to customize that date range for each unique URL.

I believe that I can use MIN() to find the first occurrence of each URL, but I need to somehow use those MIN() values together with a WHERE clause. I think I need to use a JOIN, but I can't seem to figure out that syntax. Help greatly appreciated!

Upvotes: 2

Views: 105

Answers (1)

peterm
peterm

Reputation: 92805

It's almost exactly as you've explained it.

A version with a JOIN

SELECT v.url, COUNT(*) views
  FROM pageviews v JOIN (
    SELECT url, MIN(viewed_at) first_viewed_at
      FROM pageviews
     GROUP BY url
  ) f
   ON v.url = f.url
 WHERE v.viewed_at < f.first_viewed_at + INTERVAL 3 DAY
 GROUP BY v.url;

A version with a correlated subquery

SELECT url, COUNT(*) views
  FROM pageviews v
 WHERE viewed_at < (
   SELECT MIN(viewed_at)
     FROM pageviews
    WHERE url = v.url
   ) + INTERVAL 3 DAY
GROUP BY v.url;

Here is a dbfiddle demo

Upvotes: 2

Related Questions