Describing Sessions

From StatsJam

1 Describing ingimp Sessions
2 Number of Sessions Per User
3 Session Length, In Time
- 3.1 Session Length (Time) Data Discussion
4 Image Summaries
5 Session Data Discussion

[edit] Describing ingimp Sessions

Here we characterize sessions, where define a session to be a single instance of the application being opened and closed. The following are some questions that can be asked about sessions:

Number of times each user has used ingimp
Time spent in application (minus idle time)
Command counts in a session
- Number of commands
- Number of unique commands
Number of images open in a session

Because of slow render times, see Command Counts in Sessions for statistics on the number of commands used in a session.

For these analyses, we'll use the definition of a significant user as defined in Defining Significant Users and filter our results to only log files generated by these users.

[edit] Number of Sessions Per User

Let's first begin by summarizing the number of times ingimp users have used ingimp.

Show/hide playground Show/hide query Link to this result

  <query>
     SELECT
        COUNT(log_num) AS " "
      FROM
        interaction_log
      WHERE
        log_num IN (
          SELECT
            log_num
          FROM
            interaction_log
          WHERE
            interaction_log.user_id IN (SELECT user_id FROM significant_users)
        )
      GROUP BY
        user_id
  </query>
  <view type="hist" caption="Histogram of Number of Sessions Per User"/>

Histogram of Number of Sessions Per User

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      ROUND(AVG(num_logs)) AS "Mean Number of Logs Per User",
      ROUND(MEDIAN(num_logs)) AS "Median Number of Logs Per User",
      ROUND(STDDEV(num_logs)) AS "SD Number of Logs Per User",
      ROUND(MIN(num_logs)) AS "Minimum Number of Logs Per User",
      ROUND(MAX(num_logs)) AS "Maximum Number of Logs Per User",
      COUNT(num_logs) AS "n"
    FROM
     (SELECT
        COUNT(log_num) AS num_logs
      FROM
        interaction_log
      WHERE
        log_num IN (
          SELECT
            log_num
          FROM
            interaction_log
          WHERE
            interaction_log.user_id IN (SELECT user_id FROM significant_users)
        )
      GROUP BY
        user_id) AS session_count_per_user_table
  </query>
  <view type="table" caption="Summary Stats for Number of Sessions Per User"/>

Summary Stats for Number of Sessions Per User
Mean Number of Logs Per User	Median Number of Logs Per User	SD Number of Logs Per User	Minimum Number of Logs Per User	Maximum Number of Logs Per User	n
20.0	11.0	27.0	2.0	178.0	212

From the above data, we see that over 50% of the users have used ingimp 10 or more times, with the average number of uses being 18.

[edit] Session Length, In Time

The notion of "time spent working" is nuanced because the user can leave the application idle for some time, skewing results if we just look at the amount of time the application is open. The first thing we should do, then, is to define "idle" time so we can remove the idle time from the total time the application is open. We define this concept in Defining Idle Time and choose 120 seconds between commands as our definition of idle time.

With this definition of idle, here are summary stats of session lengths in time, across all logs.

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      ROUND(SUM(session_len_mins)/60.0) AS "Total Time Spent in ingimp, in Hours"
    FROM
     (SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS session_len_mins
      FROM
        interaction_log,
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num AND
        session_time_table.log_num = interaction_log.log_num
     ) AS session_len_table
  </query>
  <view type="table" caption="Total (Corrected) Time Used By All Users (Hours)"/>

  <query>
    SELECT
      ROUND(SUM(session_len_mins)/60.0) AS "Total Time Spent in ingimp, in Hours"
    FROM
     (SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS session_len_mins
      FROM
        interaction_log,
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num AND
        session_time_table.log_num = interaction_log.log_num
     ) AS session_len_table
  </query>
  <view type="table" caption="Total (Corrected) Time Used By All Users (Hours)"/>

Total (Corrected) Time Used By All Users (Hours)
Total Time Spent in ingimp, in Hours
961.0

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
      SELECT
        log_date AS "Log Date",
        (session_time_secs - idle_time_secs) / 60.0 AS "Session Length, Minutes"
      FROM
        interaction_log,
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num AND
        session_time_table.log_num = interaction_log.log_num
  </query>
  <view type="plot_date" caption="Scatter Plot for Corrected Session Length, in Minutes, Across All Log Files"/>

  <query>
      SELECT
        log_date AS "Log Date",
        (session_time_secs - idle_time_secs) / 60.0 AS "Session Length, Minutes"
      FROM
        interaction_log,
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num AND
        session_time_table.log_num = interaction_log.log_num
  </query>
  <view type="plot_date" caption="Scatter Plot for Corrected Session Length, in Minutes, Across All Log Files"/>

Scatter Plot for Corrected Session Length, in Minutes, Across All Log Files

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
      SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS " "
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
  </query>
  <view type="hist" caption="Histogram for Corrected Session Length, in Minutes, Across All Log Files"/>

  <query>
      SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS " "
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
  </query>
  <view type="hist" caption="Histogram for Corrected Session Length, in Minutes, Across All Log Files"/>

Histogram for Corrected Session Length, in Minutes, Across All Log Files

Looking at the data, there are some outliers, but most session lengths are less than 60 minutes long. So let's look at some graphs of session lengths less than 60 minutes long.

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      log_date AS "Log Date",
      session_length_mins AS "Session Length, Minutes"
    FROM
      interaction_log,
     (SELECT
        session_time_table.log_num,
        (session_time_secs - idle_time_secs) / 60.0 AS session_length_mins
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS session_lengths_table
    WHERE
      session_lengths_table.log_num = interaction_log.log_num AND
      session_length_mins &lt; 60
  </query>
  <view type="plot_date" caption="Scatter Plot for Corrected Session Length, in Minutes, For Sessions Less than 60 Minutes Long, Across All Log Files"/>

  <query>
    SELECT
      log_date AS "Log Date",
      session_length_mins AS "Session Length, Minutes"
    FROM
      interaction_log,
     (SELECT
        session_time_table.log_num,
        (session_time_secs - idle_time_secs) / 60.0 AS session_length_mins
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS session_lengths_table
    WHERE
      session_lengths_table.log_num = interaction_log.log_num AND
      session_length_mins &lt; 60
  </query>
  <view type="plot_date" caption="Scatter Plot for Corrected Session Length, in Minutes, For Sessions Less than 60 Minutes Long, Across All Log Files"/>

Scatter Plot for Corrected Session Length, in Minutes, For Sessions Less than 60 Minutes Long, Across All Log Files

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      session_length_mins AS " "
    FROM
      (SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS session_length_mins
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS session_lengths_table
    WHERE
      session_length_mins &lt; 60
  </query>
  <view type="hist" caption="Histogram for Corrected Session Length, in Minutes, For Sessions Less Than 60 Minutes Long, Across All Log Files"/>

  <query>
    SELECT
      session_length_mins AS " "
    FROM
      (SELECT
        (session_time_secs - idle_time_secs) / 60.0 AS session_length_mins
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS session_lengths_table
    WHERE
      session_length_mins &lt; 60
  </query>
  <view type="hist" caption="Histogram for Corrected Session Length, in Minutes, For Sessions Less Than 60 Minutes Long, Across All Log Files"/>

Histogram for Corrected Session Length, in Minutes, For Sessions Less Than 60 Minutes Long, Across All Log Files

Looking at the graphs and data, it appears ingimp is typically used for 15 minutes or less. Let's reduce these data to summaries:

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      ROUND(AVG(corrected_time_secs)/60.0) AS "Corrected Mean",
      ROUND(MEDIAN(corrected_time_secs)/60.0) AS "Corrected Median",
      ROUND(STDDEV(corrected_time_secs)/60.0) AS "Corrected SD",
      ROUND(MIN(corrected_time_secs)/60.0) AS "Corrected Minimum Time",
      ROUND(MAX(corrected_time_secs)/60.0) AS "Corrected Maximum Time",
      ROUND(AVG(session_time_secs)/60.0) AS "Uncorrected Mean",
      ROUND(MEDIAN(session_time_secs)/60.0) AS "Uncorrected Median",
      ROUND(STDDEV(session_time_secs)/60.0) AS "Uncorrected SD",
      ROUND(MIN(session_time_secs)/60.0) AS "Uncorrected Minimum Time",
      ROUND(MAX(session_time_secs)/60.0) AS "Uncorrected Maximum Time",
      ROUND(AVG(idle_time_secs)/60.0) AS "Idle Time Mean",
      ROUND(MEDIAN(idle_time_secs)/60.0) AS "Idle Time Median",
      ROUND(STDDEV(idle_time_secs)/60.0) AS "Idle Time SD",
      ROUND(MIN(idle_time_secs)/60.0) AS "Minimum Idle Time",
      ROUND(MAX(idle_time_secs)/60.0) AS "Maximum Idle Time",
      COUNT(idle_time_secs) AS "n"
    FROM
     (SELECT
        session_time_table.log_num,
        session_time_secs - idle_time_secs AS corrected_time_secs,
        session_time_secs,
        idle_time_secs
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS corrected_session_times_table
  </query>
  <view type="table" caption="Summary Stats for Session Length, in Minutes, Across All Log Files"/>

  <query>
    SELECT
      ROUND(AVG(corrected_time_secs)/60.0) AS "Corrected Mean",
      ROUND(MEDIAN(corrected_time_secs)/60.0) AS "Corrected Median",
      ROUND(STDDEV(corrected_time_secs)/60.0) AS "Corrected SD",
      ROUND(MIN(corrected_time_secs)/60.0) AS "Corrected Minimum Time",
      ROUND(MAX(corrected_time_secs)/60.0) AS "Corrected Maximum Time",
      ROUND(AVG(session_time_secs)/60.0) AS "Uncorrected Mean",
      ROUND(MEDIAN(session_time_secs)/60.0) AS "Uncorrected Median",
      ROUND(STDDEV(session_time_secs)/60.0) AS "Uncorrected SD",
      ROUND(MIN(session_time_secs)/60.0) AS "Uncorrected Minimum Time",
      ROUND(MAX(session_time_secs)/60.0) AS "Uncorrected Maximum Time",
      ROUND(AVG(idle_time_secs)/60.0) AS "Idle Time Mean",
      ROUND(MEDIAN(idle_time_secs)/60.0) AS "Idle Time Median",
      ROUND(STDDEV(idle_time_secs)/60.0) AS "Idle Time SD",
      ROUND(MIN(idle_time_secs)/60.0) AS "Minimum Idle Time",
      ROUND(MAX(idle_time_secs)/60.0) AS "Maximum Idle Time",
      COUNT(idle_time_secs) AS "n"
    FROM
     (SELECT
        session_time_table.log_num,
        session_time_secs - idle_time_secs AS corrected_time_secs,
        session_time_secs,
        idle_time_secs
      FROM
       (SELECT
          event_record.log_num,
          MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
        FROM
          event_record
        WHERE
          event_record.elapsed_time_usec &gt; 0
        GROUP BY
          log_num
        ) AS session_time_table,
        (SELECT
          significant_logs.log_num,
          CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
        FROM
          significant_logs
          LEFT OUTER JOIN
         (SELECT
            log_num,
            SUM(time_diff_in_secs) AS sum_idle_time
          FROM
           (SELECT
              earlier_table.log_num,
              (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
            FROM
              event_record AS earlier_table,
              event_record AS later_table
            WHERE
              later_table.log_num IN (SELECT log_num FROM significant_logs) AND
              later_table.log_num = earlier_table.log_num AND
              later_table.command_num = (earlier_table.command_num + 1) AND
              later_table.elapsed_time_usec &gt; 0 AND
              earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
            ) AS time_diff_table
          WHERE
            time_diff_in_secs &gt; 120
          GROUP BY
            log_num
          ) AS inner_idle_time_table
          ON significant_logs.log_num = inner_idle_time_table.log_num
        ) AS idle_time_table
      WHERE
        session_time_table.log_num = idle_time_table.log_num
      ) AS corrected_session_times_table
  </query>
  <view type="table" caption="Summary Stats for Session Length, in Minutes, Across All Log Files"/>

Summary Stats for Session Length, in Minutes, Across All Log Files
Corrected Mean	Corrected Median	Corrected SD	Corrected Minimum Time	Corrected Maximum Time	Uncorrected Mean	Uncorrected Median	Uncorrected SD	Uncorrected Minimum Time	Uncorrected Maximum Time	Idle Time Mean	Idle Time Median	Idle Time SD	Minimum Idle Time	Maximum Idle Time	n
15.0	5.0	47.0	0.0	1611.0	67.0	10.0	260.0	0.0	5850.0	52.0	2.0	249.0	0.0	5619.0	3917

For completeness, we include uncorrected session length times and idle times.

The results of this query indicate people do not use ingimp for long stretches of time. At the time of this writing, the average session length is 14 minutes, with a median of 5 minutes. The uncorrected time average (that is, without idle time taken out) is 58 minutes with a median of 9 minutes. So most sessions are rather short, in length. Let's look at these data on a per-user basis.

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      ROUND(AVG(corrected_user_mean)) AS "Mean Mean Corrected Time",
      ROUND(MEDIAN(corrected_user_mean)) AS "Median Mean Corrected Time",
      ROUND(STDDEV(corrected_user_mean)) AS "SD Mean Corrected Time",
      ROUND(AVG(corrected_user_median)) AS "Mean Median Corrected Time",
      ROUND(MEDIAN(corrected_user_median)) AS "Median Median Corrected Time",
      ROUND(STDDEV(corrected_user_median)) AS "SD Median Corrected Time",
      COUNT(corrected_user_mean) AS "n"
    FROM
      (SELECT
        AVG(corrected_time_secs)/60.0 AS corrected_user_mean,
        MEDIAN(corrected_time_secs)/60.0 AS corrected_user_median,
        AVG(session_time_secs)/60.0 AS uncorrected_user_mean,
        MEDIAN(session_time_secs)/60.0 AS uncorrected_user_median,
        AVG(idle_time_secs)/60.0 AS user_idle_time_mean,
        MEDIAN(idle_time_secs)/60.0 AS user_idle_time_median
      FROM
        interaction_log,
       (SELECT
          session_time_table.log_num,
          session_time_secs - idle_time_secs AS corrected_time_secs,
          session_time_secs,
          idle_time_secs
        FROM
         (SELECT
            event_record.log_num,
            MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
          FROM
            event_record
          WHERE
            event_record.elapsed_time_usec &gt; 0
          GROUP BY
            log_num
          ) AS session_time_table,
          (SELECT
            significant_logs.log_num,
            CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
          FROM
            significant_logs
            LEFT OUTER JOIN
           (SELECT
              log_num,
              SUM(time_diff_in_secs) AS sum_idle_time
            FROM
             (SELECT
                earlier_table.log_num,
                (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
              FROM
                event_record AS earlier_table,
                event_record AS later_table
              WHERE
                later_table.log_num IN (SELECT log_num FROM significant_logs) AND
                later_table.log_num = earlier_table.log_num AND
                later_table.command_num = (earlier_table.command_num + 1) AND
                later_table.elapsed_time_usec &gt; 0 AND
                earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
              ) AS time_diff_table
            WHERE
              time_diff_in_secs &gt; 120
            GROUP BY
              log_num
            ) AS inner_idle_time_table
            ON significant_logs.log_num = inner_idle_time_table.log_num
          ) AS idle_time_table
        WHERE
          session_time_table.log_num = idle_time_table.log_num
        ) AS corrected_session_times_table
      WHERE
        corrected_session_times_table.log_num = interaction_log.log_num
      GROUP BY
        user_id
      ) AS user_session_times_table
  </query>
  <view type="table" caption="Summary Stats for Session Length, in Minutes, Across Users"/>

  <query>
    SELECT
      ROUND(AVG(corrected_user_mean)) AS "Mean Mean Corrected Time",
      ROUND(MEDIAN(corrected_user_mean)) AS "Median Mean Corrected Time",
      ROUND(STDDEV(corrected_user_mean)) AS "SD Mean Corrected Time",
      ROUND(AVG(corrected_user_median)) AS "Mean Median Corrected Time",
      ROUND(MEDIAN(corrected_user_median)) AS "Median Median Corrected Time",
      ROUND(STDDEV(corrected_user_median)) AS "SD Median Corrected Time",
      COUNT(corrected_user_mean) AS "n"
    FROM
      (SELECT
        AVG(corrected_time_secs)/60.0 AS corrected_user_mean,
        MEDIAN(corrected_time_secs)/60.0 AS corrected_user_median,
        AVG(session_time_secs)/60.0 AS uncorrected_user_mean,
        MEDIAN(session_time_secs)/60.0 AS uncorrected_user_median,
        AVG(idle_time_secs)/60.0 AS user_idle_time_mean,
        MEDIAN(idle_time_secs)/60.0 AS user_idle_time_median
      FROM
        interaction_log,
       (SELECT
          session_time_table.log_num,
          session_time_secs - idle_time_secs AS corrected_time_secs,
          session_time_secs,
          idle_time_secs
        FROM
         (SELECT
            event_record.log_num,
            MAX(event_record.elapsed_time_usec)/1000000.0 AS session_time_secs
          FROM
            event_record
          WHERE
            event_record.elapsed_time_usec &gt; 0
          GROUP BY
            log_num
          ) AS session_time_table,
          (SELECT
            significant_logs.log_num,
            CASE WHEN sum_idle_time IS NULL THEN 0 ELSE sum_idle_time END AS idle_time_secs
          FROM
            significant_logs
            LEFT OUTER JOIN
           (SELECT
              log_num,
              SUM(time_diff_in_secs) AS sum_idle_time
            FROM
             (SELECT
                earlier_table.log_num,
                (later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
              FROM
                event_record AS earlier_table,
                event_record AS later_table
              WHERE
                later_table.log_num IN (SELECT log_num FROM significant_logs) AND
                later_table.log_num = earlier_table.log_num AND
                later_table.command_num = (earlier_table.command_num + 1) AND
                later_table.elapsed_time_usec &gt; 0 AND
                earlier_table.elapsed_time_usec &gt; 0 -- needed because of an errant log file (log_num 4639)
              ) AS time_diff_table
            WHERE
              time_diff_in_secs &gt; 120
            GROUP BY
              log_num
            ) AS inner_idle_time_table
            ON significant_logs.log_num = inner_idle_time_table.log_num
          ) AS idle_time_table
        WHERE
          session_time_table.log_num = idle_time_table.log_num
        ) AS corrected_session_times_table
      WHERE
        corrected_session_times_table.log_num = interaction_log.log_num
      GROUP BY
        user_id
      ) AS user_session_times_table
  </query>
  <view type="table" caption="Summary Stats for Session Length, in Minutes, Across Users"/>

Summary Stats for Session Length, in Minutes, Across Users
Mean Mean Corrected Time	Median Mean Corrected Time	SD Mean Corrected Time	Mean Median Corrected Time	Median Median Corrected Time	SD Median Corrected Time	n
14.0	8.0	24.0	8.0	5.0	9.0	212

In aggregating across all users, we need to first summarize on a per-user basis. The query above performs these summaries on average times per user and median times per user. That is, we'll take the mean and median time of each user's individual mean and median times.

These summaries show that the mean mean session length across users is 14 minutes. The median median time is 5 minutes. Again, these data show rather short session lengths across all users.

[edit] Session Length (Time) Data Discussion

At the time of this writing, average session lengths are less than 15 minutes long across all logs, with median median times across users 5 minutes. These data indicate that a user's typical session lies somewhere between 5 and 15 minutes, suggesting inimp is, on average, being used for rather small tasks.

[edit] Image Summaries

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      log_date AS "Log Date",
      num_images AS "Number of Images"
    FROM
      interaction_log,
      (SELECT
        log_num,
        COUNT(image_id) AS num_images
      FROM
        (SELECT
          DISTINCT log_num,
          image_id
        FROM
          document_images) AS doc_images_table
      GROUP BY
        log_num) AS image_log_count_table
    WHERE
      interaction_log.log_num = image_log_count_table.log_num AND
      interaction_log.log_num IN (SELECT log_num FROM significant_logs)
  </query>
  <view type="plot_date" caption="Scatterplot for Number of Images Per Log File"/>

Scatterplot for Number of Images Per Log File

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      num_images AS " "
    FROM
      (SELECT
        log_num,
        COUNT(image_id) AS num_images
      FROM
        (SELECT
          DISTINCT log_num,
          image_id
        FROM
          document_images) AS doc_images_table
      GROUP BY
        log_num) AS image_log_count_table
    WHERE
      image_log_count_table.log_num IN (SELECT log_num FROM significant_logs)
  </query>
  <view type="hist" caption="Histogram for Number of Images Per Session"/>

Histogram for Number of Images Per Session

From looking at the data above, most users open less than 30 images in a session, so let's crop those graphics to 30 images or less:

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      log_date AS "Log Date",
      num_images AS "Number of Images"
    FROM
      interaction_log,
      (SELECT
        log_num,
        COUNT(image_id) AS num_images
      FROM
        (SELECT
          DISTINCT log_num,
          image_id
        FROM
          document_images) AS doc_images_table
      GROUP BY
        log_num) AS image_log_count_table
    WHERE
      num_images &lt; 30 AND
      interaction_log.log_num = image_log_count_table.log_num AND
      interaction_log.log_num IN (SELECT log_num FROM significant_logs)
  </query>
  <view type="plot_date" caption="Scatterplot for Number of Images Per Log File"/>

Scatterplot for Number of Images Per Log File

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      num_images AS " "
    FROM
      (SELECT
        log_num,
        COUNT(image_id) AS num_images
      FROM
        (SELECT
          DISTINCT log_num,
          image_id
        FROM
          document_images) AS doc_images_table
      GROUP BY
        log_num) AS image_log_count_table
    WHERE
      num_images &lt; 30 AND
      image_log_count_table.log_num IN (SELECT log_num FROM significant_logs)
  </query>
  <view type="hist" caption="Histogram for Number of Images Per Session"/>

Histogram for Number of Images Per Session

Summarizing these data:

Show/hide playground Show/hide query Link to this result

  <query>
    SELECT
      ROUND(AVG(num_images)) AS "Mean Number of Images",
      ROUND(MEDIAN(num_images)) AS "Median Number of Images",
      ROUND(STDDEV(num_images)) AS "SD Number of Images",
      COUNT(num_images) AS "n"
    FROM
     (SELECT
        num_images
      FROM
        (SELECT
          log_num,
          COUNT(image_id) AS num_images
        FROM
          (SELECT
            DISTINCT log_num,
            image_id
          FROM
            document_images) AS doc_images_table
        GROUP BY
          log_num) AS image_log_count_table
      WHERE
        image_log_count_table.log_num IN (SELECT log_num FROM significant_logs)
      ) AS images_per_log_file_table
  </query>
  <view type="table"/>

Mean Number of Images	Median Number of Images	SD Number of Images	n
4.0	2.0	8.0	3511

Above is summary stats for number of images opened per session.

Now on a per-user basis:

Show/hide playground Show/hide query Link to this result

This form gives you a chance to modify the query and quickly see the results but it does not update the wiki page..

<query>
    SELECT
      ROUND(AVG(mean_images_per_user)) AS "Mean Mean Number of Images",
      ROUND(MEDIAN(mean_images_per_user)) AS "Median Mean Number of Images",
      ROUND(STDDEV(mean_images_per_user)) AS "SD Mean Number of Images",
      ROUND(AVG(median_images_per_user)) AS "Mean Median Number of Images",
      ROUND(MEDIAN(median_images_per_user)) AS "Median Median Number of Images",
      ROUND(STDDEV(median_images_per_user)) AS "SD Median Number of Images",
      COUNT(mean_images_per_user) AS "n"
    FROM
     (SELECT
        AVG(num_images) AS mean_images_per_user,
        MEDIAN(num_images) AS median_images_per_user
      FROM
        interaction_log,
       (SELECT
          log_num,
          num_images
        FROM
          (SELECT
            log_num,
            COUNT(image_id) AS num_images
          FROM
            (SELECT
              DISTINCT log_num,
              image_id
            FROM
              document_images) AS doc_images_table
          GROUP BY
            log_num) AS image_log_count_table
        WHERE
          image_log_count_table.log_num IN (SELECT log_num FROM significant_logs)
        ) AS images_per_log_file_table
      WHERE
        interaction_log.log_num = images_per_log_file_table.log_num
      GROUP BY
        interaction_log.user_id) AS per_user_image_table
  </query>
  <view type="table" caption="Summary Stats for Number of Images Per User"/>

  <query>
    SELECT
      ROUND(AVG(mean_images_per_user)) AS "Mean Mean Number of Images",
      ROUND(MEDIAN(mean_images_per_user)) AS "Median Mean Number of Images",
      ROUND(STDDEV(mean_images_per_user)) AS "SD Mean Number of Images",
      ROUND(AVG(median_images_per_user)) AS "Mean Median Number of Images",
      ROUND(MEDIAN(median_images_per_user)) AS "Median Median Number of Images",
      ROUND(STDDEV(median_images_per_user)) AS "SD Median Number of Images",
      COUNT(mean_images_per_user) AS "n"
    FROM
     (SELECT
        AVG(num_images) AS mean_images_per_user,
        MEDIAN(num_images) AS median_images_per_user
      FROM
        interaction_log,
       (SELECT
          log_num,
          num_images
        FROM
          (SELECT
            log_num,
            COUNT(image_id) AS num_images
          FROM
            (SELECT
              DISTINCT log_num,
              image_id
            FROM
              document_images) AS doc_images_table
          GROUP BY
            log_num) AS image_log_count_table
        WHERE
          image_log_count_table.log_num IN (SELECT log_num FROM significant_logs)
        ) AS images_per_log_file_table
      WHERE
        interaction_log.log_num = images_per_log_file_table.log_num
      GROUP BY
        interaction_log.user_id) AS per_user_image_table
  </query>
  <view type="table" caption="Summary Stats for Number of Images Per User"/>

Summary Stats for Number of Images Per User
Mean Mean Number of Images	Median Mean Number of Images	SD Mean Number of Images	Mean Median Number of Images	Median Median Number of Images	SD Median Number of Images	n
3.0	3.0	3.0	2.0	2.0	2.0	211

The above data indicate that users typically operate on 2-4 images per session.

[edit] Session Data Discussion

As of the time of this writing, the data summaries above paint a very clear picture of the typical ingimp user:

Their sessions are rather brief, typically less than 15 minutes long
The typical number of commands applied is less than 120
Users typically employ less than 12 distinct commands in their sessions
Users typically work on only 2-4 images in a particular session

Keeping in mind that these data are culled from ~200 users and almost 4,000 log files, users of ingimp are seemingly using the application for quick, targetted tasks on a few images at a time -- these people are not using it extensively for hours on hand.

Retrieved from "http://ingimp.org/statsjam/index.php/Describing_Sessions"