Add command to show upvotes/downvotes per chat over time

[schafezp]

Command: /historygraph

• Use an SQL command to get number of reacts per day in the particular chat
• Generate an image on the server side showing the reacts
• have bot generate and post image in response to command

Additionally: Have option to show graph for a particular user, for a set of chats, etc.

This command works for showing the reacts per day. I would ideally like it to also differentiate between +1 and -1 react score. Perhaps that would require a second group.

select date_trunc('day',tm.message_time ) "day", count(*) as views
from user_reacted_to_message urtm 
LEFT JOIN telegram_message tm ON tm.message_id=urtm.message_id
where tm.chat_id = %s
group by 1
order by 1;

Inspiration for this query taken from: https://stackoverflow.com/questions/14770829/grouping-timestamps-by-day-not-by-time

[schafezp]

To separate by +1 or -1 use the command

select urtm.react_score, count(urtm.react_score), date_trunc('day',tm.message_time ) "day" from user_reacted_to_message urtm 
LEFT JOIN telegram_message tm ON tm.message_id=urtm.message_id
WHERE tm.chat_id = %s
group by urtm.react_score, "day"
order by urtm.react_score, "day"

If order by react_score is used first it will split the data into two chunks by react_score so seperating the list runs in linear time and can easily shortcut the list once a "+1" is discovered in the react_score slot.

[schafezp]

There is the seperate question from a user design perspective; should the user be able to see upvotes as well as downvotes? Perhaps it penalizes the use of the downvote; or it causes memories to linger on the days where there were many downvotes.

Perhaps users can provide an argument to show the graph with diffs; something like

/historygraph diff