Track The Time that Players get online

[ghost]

Is your feature request related to a problem? Please describe.

Just wanna know when certain players get online so i can plan some things

Describe the solution you'd like

Track The time (for example 5:23pm everyday) that users log in everyday on average

Describe alternatives you've considered

N/A

Additional context

Example Screenshot:

[AuroraLS3]

TODO

Most of the complexity of this ticket is in building the query.

• select session starts and ends of a player
• remove date information and only keep the clock
• apply timezone setting
• The difficult portion: Average when the player is online
  • Make a window function sum +1 on session start, -1 on end over the clock and take a weighed average using the middlepoint of each segment - for example

    00:00: 0
    08:14: 1 // session started
    08:16: 2 // session started
    09:00: 1 // session ended
    10:12: 0 // session ended
    ... etc
    
    Middlepoints: 
    00:00-08:14 - 04:07 (247min): 0, 
    08:14-08:16 - 08:15 (495min): 1, 
    08:16-09:00 - 08:38 (518min): 2, 
    09:00-10:12 - 9:36 (576min): 1
    
    Weighed average: 527min: ~ 8:43
    

    • The window function described above requires the clock times to be ordered.
    • Example window function that queries players online graph from plan_sessions data, "just" do this with the clock numbers

    SELECT 
    DATE,
    SUM(VALUE) OVER(ORDER BY date) AS players_online
    FROM (
        SELECT session_start AS DATE, 1 AS value FROM plan_sessions
        UNION ALL
        SELECT session_end AS DATE, -1 AS value FROM plan_sessions
    ) q1
    

[AuroraLS3]

Tested averaging, unfortunately it produces awfully inaccurate results when the player joins often in the morning and evening

One option to convey this information is to add more graphs to the punchcard, eg. line graphs for frequency: