Finding maximum number of rows created per hour?

Question

I have a table to store product reviews like this:

Id -int
ProductId -int
Timestamp -datetime
Comments -text

Is there an easy way to count and determine the rate of reviews a product has received in any 60 minute timespan? ie. Widget1 maximum reviews/hour is 55.

working with sql05.

Answer 1

Another option would be to use the SQL DATEPART functions like this:

SELECT
    DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
    DATEPART(HOUR, TimeStamp),
    COUNT(*)
FROM    
    dbo.Products
GROUP BY
    DAY(TimeStamp), MONTH(TimeStamp), YEAR(TimeStamp),
    DATEPART(HOUR, TimeStamp)
ORDER BY
    COUNT(*) DESC

This gives you not just the maximum number of views for any given hour, but all of them, sorted by the frequency. Mind you: other than RexM's solution, this is based on the "hour" part of your "timestamp" - so if you have quite a few views at 7:59 and another burst at 8:01, in my solution, those wouldn't be shown together (since one is hour=7 and the other is hour=8).

If you need the "any 60-minute timespan" approach, use RexM's basic idea (DATEDIFF with minutes <= 60).

Marc

Answer 2

If you're working with a set 60 minute time block (e.g. the last 60 minutes from now) then it's reasonably easy:

SELECT TOP 1
    PR.ProductID,
    COUNT(*)
FROM ProductReviews PR
WHERE PR.Timestamp BETWEEN DATEADD( minute, -60, GETDATE() ) AND GETDATE()
GROUP BY PR.ProductID
ORDER BY COUNT(*) DESC

If you're wanting it for any 60 minute interval then it gets more complex!

Answer 3

I don't know of an easy way to calculate that metric, but hopefully this will help. Without some sort of SQL cursor, I would generate a SQL table of possible intervals, with start and end timestamps (2009-09-02T00:00 to 2009-09-02T00:59, 2009-09-02T00:01 to 2009-09-02T01:00, etc) and then cross join using LINQ to SQL:

var rates = from r in db.Reviews
            from i in db.Intervals
            where i.Begin <= r.Timestamp && r.Timestamp <= i.End
            group r by i.Begin into reviews
            select reviews.Count();
var maxRate = rates.Max();

I haven't tried the code, but it should get you started. You could improve the performance by restricting how far back to check (last 7 days, 30 days, etc) or generate fewer intervals (starting on the quarter hour, perhaps).

Answer 4

If you're instead wanting "which product got the most reviews in a 60 minute block between dateX and dateY" then it gets a little more complex.

One way to think of it is "for each review in that time period how many other reviews are there for the same product ID in the following 60 minutes". Once you have that logic the query becomes clearer:

SELECT TOP 1
    PR.ProductID,
    -- start of 60 minute block
    PR.Timestamp,
    ReviewCount = (
        SELECT COUNT(*)
        FROM ProductReviews PR1
        -- from parent time
        WHERE PR1.Timestamp >= PR.Timestamp
        -- until 60 mins later
        AND PR1.Timestamp <= DATEADD( minute, 60, PR.Timestamp )
        -- that matches ProductID
        AND PR1.ProductID = PR.ProductID
    )
FROM ProductReviews PR
-- in the last 24 hours
WHERE PR.Timestamp > ( GETDATE() - 1 )
ORDER BY ReviewCount DESC

Hows that?

Answer 5

If you were happy with fixed-hours for your windows, I may consider doing this a little bit smarter, by using a trigger. The trigger would insert/update into a 'log' table, and just differentiate between inserting or updating based on the current time.

You could combine with this any of the other approaches, it would add a nice caching layer.