Aggregated Heatmap of User Activity by 24H buckets on Y-axis and the day of the week on X-axis

Sorry new user so limited to 2 links and 2 embedded media items…

What Grafana version and what operating system are you using?

grafana version 9.4.1 local installation
macOS Ventura version 13.0.1

What are you trying to achieve?

I want to plot user activity over time as aggregated counts by weekday and 1h buckets per day. Much like on the Ethereum gas fees tracker:

How are you trying to achieve it?

I want to let Grafana do the math so this is the query and result:

SELECT created_at, 1 as count
FROM your_database

What happened?

This is the result for the Hourly heatmap plugin:

The result is nearly what I want but I want to aggregate the days into weekdays and show cumulative counts for those days in the visualization. So it has a max of 7 days on the X-axis and I can select the timeframe on which to apply the query

Either I get a table that doesn’t aggregate as I need a datetime column OR I get a table that does aggregate as such:

  DATE_FORMAT(created_at, '%W') AS day_of_week,
  DATE_FORMAT(created_at, '%H') AS hour_of_day,
  DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour_window,
  COUNT(*) AS count
FROM typical_orders
WHERE created_at >= DATE_SUB('2022-10-17', INTERVAL 7 DAY)
GROUP BY day_of_week, hour_of_day, hour_window
ORDER BY FIELD(day_of_week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), hour_of_day, hour_window;


Did you follow any online instructions? If so, what is the URL?

I’ve installed the Hourly Heatmap plugin and sources such as these:

It would seem this must be possible somehow, but how? We’re so close…