I have a 'log' table which has two columns user_id and a ts (timestamp value of activity event for user):
I can use the following query to get a list of each user_id and their last activity event:
SELECT user_id, max(ts) as maxts
FROM "analytics"
GROUP BY user_id
ORDER BY maxts desc
What I would like to achieve:
Using the above data, I'd like to display it as a bar chart of 10 bars by grouping users according to their last active time max(ts). So I'd like get the values for each "bar" (number of users) where each bar would have an equal time span.
My work so far:
I thought some of it might be easier to be done via a script as opposed to natively via a SQL query.
Using a script I can get the the min and max time, and split it up into 10 equal time periods. I suppose I can then write a union query where each query does a WHERE ts < $timePeriodStart AND ts < $timePeriodEnd.
However this does seem a bit clunky and I'm wondering if there is a more effective solution. Unfortunately using a web search I couldn't find much about generating such values, hence my question.
