View Source

Overview

SELECT to_char(sum(fields),'FM999,999,999,999,999')  as "Total fields", to_char(sum(requests), 'FM999,999,999,999,999') as "Total requests", to_char(sum(delivered/1024^4), 'FM999.999') as "Delivered volume (TB)"
FROM  datasets_stats_monthly
WHERE datasets_stats_monthly.dataset='tigge-global' and datasets_stats_monthly.month>=date'20060101';

Monthly sums

Data volume [TB]

Delivered: data volume delivered to user after post-processing(e.g. interpolation) applied if asked for
Retrieved: data volume retrieved from MARS before post-processing
- retrieved data volume is normally equal or bigger then the delivered one with exception when user asked for higher then archived grid resolution resulting in artificially bigger interpolated files

select  to_char(month, 'MM/YYYY') as monthyear, CAST(delivered AS FLOAT)/1024^4 as "Delivered" from datasets_stats_monthly where dataset='tigge-global' and month>=date'20061001' order by month ;
select  to_char(month, 'MM/YYYY') as monthyear, CAST(retrieved AS FLOAT)/1024^4 as "Retrieved" from datasets_stats_monthly where dataset='tigge-global' and month>=date'20061001' order by month

User requests

(1 original user request could be split to more resulting MARS database retrieval requests shown in the graph below. It depends on MARS data design for given datasets. On average number of the original users requests is about 1/4 of the resulting "user requests" hitting MARS shown here)

select  to_char(month, 'MM/YYYY') as monthyear, requests from datasets_stats_monthly where dataset='tigge-global' and month>=date'20061001' order by month;

Fields retrieved

(1 field means the retrieved field for given step, level, etc)

select  to_char(month, 'MM/YYYY') as monthyear, fields from datasets_stats_monthly where dataset='tigge-global' and month>=date'20061001' order by month

Users

(Users at least with 3 requests ever since)

SELECT to_char(month, 'MM/YYYY') as monthyear, count(DISTINCT username)as "active users"
FROM  users_stats_monthly 
WHERE dataset='tigge-global' AND month>=date'20061001' 
AND username IN (
      SELECT username                        
      FROM  users_stats_monthly
      WHERE dataset='tigge-global'
      GROUP BY username
      HAVING sum(requests) >= 3
      )
GROUP BY month
ORDER BY month;

Users per country [%]

(Users at least with 3 requests ever since)

SELECT profile_country.name, count(DISTINCT auth_user.username) as total_users
    FROM  profile_country
         RIGHT JOIN profile_profile ON profile_profile.country_id = profile_country.id
         RIGHT JOIN auth_user ON auth_user.id = profile_profile.user_id
         RIGHT JOIN users_stats_monthly ON users_stats_monthly.username = auth_user.username
    WHERE users_stats_monthly.dataset='tigge-global' and users_stats_monthly.month>=date'20061001'
GROUP BY profile_country.name
ORDER BY total_users DESC;