Generating daily usage statistics

Sometimes you'll want to know how many logins there're on your web site and how the daily usage profile looks like. Usually the first attempt to evaluate this is to copy some log files or unload some database table which holds the login information and then import it to Excel or a similar spreadsheet generator.

But in many cases it can be done by the database directly. Here's a DB2 example. Assuming there's a table "events.logins" holding the login events with a date and a time column, then this query would bring up a daily usage profile based on the average data of the last 7 days:

select
tmptable.hr  as Hour_of_day,
tmptable.cnt as Num_Logins,
substr(
  repeat('#', tmptable.cnt * 60 / (

    select
      max( maxquery.cnt ) as max
    from (

      select
        hour(time)         as hr,
        count(hour(time))  as cnt
      from
        events.logins
      where
        date >= (current_date - 7 days)
      group by
        hour(time)
    ) maxquery

  )
),1,60)  as Logins_Profile
from (

select
  hour(time)        as hr,
  count(hour(time)) as cnt
from
  events.logins
where
  date >= (current_date - 7 days)
group by
  hour(time)

) tmptable
order by
tmptable.hr
;

This query includes two temporary tables. The upper one is used to evaluate the maximum logins per hour, which is necessary to normalize all counts. The second is generating the raw data, means the hour of the day and the number of logins. The result looks like this:

HOUR_OF_DAY NUM_LOGINS  LOGINS_PROFILE
----------- ----------- ------------------------------------------------------------
        0          36 ##
        1          43 ##
        2          52 ###
        3          55 ###
        4          61 ####
        5          73 ####
        6         119 ########
        7         294 ####################
        8         625 ##########################################
        9         846 #########################################################
       10         880 ############################################################
       11         812 #######################################################
       12         663 #############################################
       13         673 #############################################
       14         685 ##############################################
       15         662 #############################################
       16         548 #####################################
       17         359 ########################
       18         190 #############
       19         123 ########
       20         112 #######
       21          87 #####
       22          75 #####
       23          48 ###

24 record(s) selected.