SQL Query that generates activity for a player

[TomLewis]

Ahoy!

I have been building a web UI and wanted to pull some data directly from the plan database, ive built a player profile for all players on my server, and I really want to have the activity status of each player on their profile, but I cant see it set in the player table, so im assuming its generated based off their session data dynamically?

Since ive built my own API that im calling from a custom frontend, I wouldnt be using your API (as im not using java) and I dont need the full JSON of each player, Do you have a single SQL query that you use to get a players activity? or do you pull X amount of sessions and do it in code?

[AuroraLS3]

Here's the SQL that is used in the plugin (generated with https://github.com/plan-player-analytics/Plan/blob/master/Plan/common/src/main/java/com/djrapitops/plan/storage/database/queries/analysis/ActivityIndexQueries.java#L83-L104)

SELECT 5.0 - 5.0 * AVG(1.0 / (PI()/2.0 * (ax_q1.active_playtime*1.0/$1) +1.0)) AS activity_index,
       ax_u.id AS user_id,
       ax_u.uuid
FROM
  (SELECT ax_ux.user_id,
          COALESCE(active_playtime, 0) AS active_playtime
   FROM plan_user_info ax_ux
   LEFT JOIN
     (SELECT user_id,
             SUM(session_end-session_start-afk_time) AS active_playtime
      FROM plan_sessions
      WHERE server_id=
          (SELECT plan_servers.id
           FROM plan_servers
           WHERE plan_servers.uuid=$2
           LIMIT 1)
        AND session_end>=$3
        AND session_start<=$4
      GROUP BY user_id) ax_sx ON ax_sx.user_id=ax_ux.user_id
   UNION ALL SELECT ax_ux.user_id,
                    COALESCE(active_playtime, 0) AS active_playtime
   FROM plan_user_info ax_ux
   LEFT JOIN
     (SELECT user_id,
             SUM(session_end-session_start-afk_time) AS active_playtime
      FROM plan_sessions
      WHERE server_id=
          (SELECT plan_servers.id
           FROM plan_servers
           WHERE plan_servers.uuid=$2
           LIMIT 1)
        AND session_end>=$5
        AND session_start<=$6
      GROUP BY user_id) ax_sx ON ax_sx.user_id=ax_ux.user_id
   UNION ALL SELECT ax_ux.user_id,
                    COALESCE(active_playtime, 0) AS active_playtime
   FROM plan_user_info ax_ux
   LEFT JOIN
     (SELECT user_id,
             SUM(session_end-session_start-afk_time) AS active_playtime
      FROM plan_sessions
      WHERE server_id=
          (SELECT plan_servers.id
           FROM plan_servers
           WHERE plan_servers.uuid=$2
           LIMIT 1)
        AND session_end>=$7
        AND session_start<=$8
      GROUP BY user_id) ax_sx ON ax_sx.user_id=ax_ux.user_id) ax_q1
JOIN plan_users ax_u ON ax_u.id=ax_q1.user_id
GROUP BY ax_u.id, ax_u.uuid
WHERE ax_u.uuid=$9

If you want to know what this query is doing there's a much more readable Java version of same calculation here https://github.com/plan-player-analytics/Plan/blob/master/Plan/common/src/main/java/com/djrapitops/plan/delivery/domain/mutators/ActivityIndex.java (Calculated on line 116)

The java code also includes thresholds for different Activity Group names

Parameter	Explanation
$1	Playtime threshold in milliseconds (From the config under Time.Thresholds)
$2	UUID string of the server calculating activity on. If you want for whole network, find-and-replace instruction below
$3 & $6	Epoch date milli for calculation - 7 days ms
$4	Epoch date milli for calculation
$5 & $8	Epoch date milli for calculation - 14 days ms
$7	Epoch date milli for calculation - 21 days ms
$9	Player uuid string

Epoch date millis are required for calculation since activity index takes last 3 weeks from the date. The activity can change over time.

---

To get activity index on the whole network (can differ from server based one):
Replace all 3

      server_id=
          (SELECT plan_servers.id
           FROM plan_servers
           WHERE plan_servers.uuid=$2
           LIMIT 1)
        AND 

with nothing

[TomLewis]

What an amazing reply!

Look at this beast! This is a next level SQL join! I love it!

This was helpful to see the outputs to words https://github.com/plan-player-analytics/Plan/blob/master/Plan/common/src/main/java/com/djrapitops/plan/delivery/domain/mutators/ActivityIndex.java#L60-L63

So the only thing I need to confirm is $3 & $6 dynamically creates the Epoch date millisecond for 7 days, same for $5 & $8 and $7, and then $4 is current Epoch date millisecond?

Thank you so much for spending the time to explain this to me, no wonder I couldnt figure it out myself!

[AuroraLS3]

Yes the mentioned parameters are like

dateMs
dateMs - 7 * daysMs
dateMs - 14 * daysMs
dateMs - 21 * daysMs

So that the parameter replaced into the query is also an epoch millisecond