1.1 --- a/src/sql/queries.sql	Wed Oct 31 22:59:35 2007 +0100
     1.2 +++ b/src/sql/queries.sql	Wed Oct 31 23:01:45 2007 +0100
     1.3 @@ -19,7 +19,8 @@
     1.4  > select * from UserLogger;
     1.5  
     1.6  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     1.7 -[1]
     1.8 +[1] who has used isac ?
     1.9 +
    1.10  select UserName, Argument as UserUrl, Time as Begin,
    1.11  unix_timestamp((select Time from UserLogger where step='UI_STOP_SESSION' and session=ul1.session)) - unix_timestamp(ul1.time) as Duration,
    1.12  (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session) as Examples
    1.13 @@ -27,7 +28,8 @@
    1.14  where step='LO_START_SESSION';
    1.15  
    1.16  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.17 -[2]
    1.18 +[2] which examples has a user calculated (over all sessions) ?
    1.19 +
    1.20  select session, Argument as ID, FormulaTo as Example, Time as Begin,
    1.21  unix_timestamp((select Time from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1)) - unix_timestamp(ul1.time) as Duration,
    1.22  (select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
    1.23 @@ -37,28 +39,32 @@
    1.24  where UserName='x' and Step='LO_OPEN_WORKSHEET';
    1.25  
    1.26  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.27 -[3]
    1.28 +[3] what did a user do during a certain session ?
    1.29 +
    1.30  select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.31  from UserLogger
    1.32 -where Session=0
    1.33 +where Session=2
    1.34  order by Time;
    1.35  
    1.36  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.37 -[4]
    1.38 +[4] what did a user do during a certain example ?
    1.39 +
    1.40  select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.41  from UserLogger
    1.42 -where Worksheet='1_0'
    1.43 +where Worksheet='2_1'
    1.44  order by Time;
    1.45  
    1.46  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.47 -[5]
    1.48 +[5] how difficult are the examples ?
    1.49 +
    1.50  select Argument as ID, FormulaTo as Example, Success, Success-1 as Failure
    1.51  from UserLogger
    1.52  where Step='LO_OPEN_WORKSHEET'
    1.53  order by Success, Failure;
    1.54  
    1.55  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.56 -[6]
    1.57 +[6] who are the most frequent users of isac ?
    1.58 +
    1.59  select UserName, Argument as UserUrl,
    1.60  (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session limit 1) as Examples,
    1.61  (select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,