1

Hi,

How do I visualize a count of a field over time on a graph?

Say if I for example want to visualize the total count of users per day on a graph.

The query is SELECT count("user"."id") FROM users, it is run twice every day and the result should be visualized on a graph that also shows the historical results of the query. Is that possible?

2

Welcome to the forum. These are two separate questions:

This part is pretty simple. Just write a query that returns two columns: a timestamp and a user count.

query%20result
query%20result1280×720 47.3 KB

Then configure a line chart visualization like this:

vizualization%20setup
vizualization%20setup1280×720 45.9 KB

This one is more complicated. Redash gets the latest data from your DB when you run a query; and it doesn’t remember old results after that. So to do this, your database will do the heavy lifting. Redash is just there to visualize what your DB kicks out.

Here are two approaches I’ve used in the past:

  1. Write a stored procedure that counts your users twice each day and INSERT’s the results to a new a table. Then query that new table with Redash. The downside here is that your history begins today.
  2. Write a query that can calculate your count of users at an arbitrary time and then GROUP BY a twice-daily interval. The downside here is complexity, as there is no “best approach”. It depends entirely on your database schema.