Package com.djrapitops.plan.storage.database.queries.analysis

Class NetworkActivityIndexQueries

java.lang.Object
com.djrapitops.plan.storage.database.queries.analysis.NetworkActivityIndexQueries
public class NetworkActivityIndexQueries extends Object
Queries for Activity Index that attempts to gain insight into player activity levels.

Old formula for activity index was not linear and difficult to turn into a query due to conditional multipliers. Thus a new formula was written.

T - Time played after someone is considered active on a particular week t1, t2, t3 - Time played that week

Activity index takes into account last 3 weeks.

Activity for a single week is calculated using A(t) = (1 / (pi/2 * (t/T) + 1)). A(t) is based on function f(x) = 1 / (x + 1), which has property f(0) = 1, decreasing from there, but not in a straight line. You can see the function plotted here

To fine tune the curve pi/2 is used since it felt like a good curve.

Activity index A is calculated by using the formula: A = 5 - 5 * [A(t1) + A(t2) + A(t3)] / 3

Plot for A and limits

New Limits for A would thus be < 1: Inactive > 1: Irregular > 2: Regular > 3: Active > 3.75: Very Active

  • Method Details

    • fetchRegularPlayerCount

      public static Query<Integer> fetchRegularPlayerCount(long date, long playtimeThreshold)

    • selectActivityIndexSQL

      public static String selectActivityIndexSQL()

    • selectActivityIndexSQL

      public static String selectActivityIndexSQL(Collection<ServerUUID> onServers)

    • setSelectActivityIndexSQLParameters

      public static void setSelectActivityIndexSQLParameters(PreparedStatement statement, int index, long playtimeThreshold, long date) throws SQLException
      Throws:
      SQLException

    • fetchActivityGroupCount

      public static Query<Integer> fetchActivityGroupCount(long date, long playtimeThreshold, double above, double below)

    • fetchActivityIndexGroupingsOn

      public static Query<Map<String,Integer>> fetchActivityIndexGroupingsOn(long date, long threshold)

    • fetchActivityIndexGroupingsOn

      public static Query<Map<String,Integer>> fetchActivityIndexGroupingsOn(long date, long threshold, Collection<Integer> userIds, List<ServerUUID> serverUUIDs)

    • countNewPlayersTurnedRegular

      public static Query<Integer> countNewPlayersTurnedRegular(long after, long before, Long threshold)

    • countRegularPlayersTurnedInactive

      public static Query<Integer> countRegularPlayersTurnedInactive(long start, long end, Long threshold)
      Parameters:
      start - Start of the tracking, those regular will be counted here.
      end - End of the tracking, those inactive will be count here.
      threshold - Playtime threshold
      Returns:
      Query how many players went from regular to inactive in a span of time.

    • averagePlaytimePerRegularPlayer

      public static Query<Long> averagePlaytimePerRegularPlayer(long after, long before, Long threshold)

    • averageSessionLengthPerRegularPlayer

      public static Query<Long> averageSessionLengthPerRegularPlayer(long after, long before, Long threshold)

    • averageAFKPerRegularPlayer

      public static Query<Long> averageAFKPerRegularPlayer(long after, long before, Long threshold)

    • activityIndexForNewPlayers

      public static Query<Collection<ActivityIndex>> activityIndexForNewPlayers(long after, long before, Long threshold)

    • averageActivityIndexForRetainedPlayers

      public static Query<ActivityIndex> averageActivityIndexForRetainedPlayers(long after, long before, Long threshold)

    • activityIndexForAllPlayers

      public static Query<Map<Integer,ActivityIndex>> activityIndexForAllPlayers(long date, long playtimeThreshold)