2

I would like to create a Grafana panel with our user database (PostgreSQL). This scene from HBO's Silicon Valley already describes it perfectly.

enter image description here

But I can't figure out how to get the "historical count" working.

My rough idea is this:

SELECT
    created as time,
    count(*)
FROM
    user
WHERE 
    created BETWEEN "FIXED START DATE" AND created
ORDER BY 
    created
ASC

But I encounter issues, that I have to add a GROUP BY clause, because of the aggregate function. Which seems legitimate. But how can I do an aggregation with the "history" of existing entities to the time of the graph position?

I would be thankful for every assistant advice!

Lennart Blom
  • 513
  • 3
  • 19

1 Answers1

5

I found a nice solution which works pretty good with Grafana.

The correct term to look for was (count cumulative total) and it uses a window function. (see here: Count cumulative total in Postgresql)

SELECT $__time(created), sum(count(*)) OVER (ORDER BY created) as "Benutzeranzahl"
FROM (
    SELECT DISTINCT ON (email) created, email
    FROM user  ORDER BY email, created
) AS subq
GROUP BY created;

The result looks perfect!

Grafana Count cumulative total

Lennart Blom
  • 513
  • 3
  • 19