Hi All -
I have a table with timestamp entries as follows:
This table is written to every time a user does certain things in the application (like moving between certain pages in the web application). I want to see an aggregate of activity within a certain range of time.
For example, for each day in a time period (say between july 1 and dec 31), I want to see any activity between the first entry for the day and the last entry for the day grouped by hour.
For Oct 1st, person A showed 5 activity entries between 8:am and 9:am, 12 between 9am and 10am, 7 between 11am and 12 noon, and 22 between 2:pm and 3pm.
Ideally this would show results only for days that have activity, and only hour periods that have activity. This way I can see if there are any big holes in activity during a give day.
Is there a way to do this entirely with SQL?