Archive for September 22nd, 2009
From Stack Overflow:
I’ve got a set of data that contains users, their awards and the time they are granted.
When a user earns an award, the award and user is logged along with a date.
What I’d like is a query that finds a set of 3 achievements that were earned within 5 minutes of each other by the same user.
Any thoughts on how to accomplish this?
I’ll go a step further and try to build a general solution to find sets of
N values within a given time frame.
We should return all rows that are parts of all 5-minute time frames containing at least
N values. These timeframes can overlap.
We should build such a timeframe for each record, make sure that there are at least
N values in this timeframe, and if yes, return these values (applying
DISTINCT to get rid of the duplicates).
Let’s create a sample table and build a query to do this: