View Source

Overview

actual archive size: XXX
actual number of registered users: XXX

SELECT to_char(sum(delivered/1024^4), 'FM999.999') as "Delivered data volume [TB]", to_char(sum(fields),'FM999,999,999,999,999')  as "Number of retrieved fields", to_char(sum(requests), 'FM999,999,999,999,999') as "Number of user requests"
FROM  datasets_stats_monthly
WHERE datasets_stats_monthly.dataset='yopp' and datasets_stats_monthly.month>=date'20170701';

^*valid for the whole archiving period since 2015-05-01

Monthly sums

Data [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 should be normally equal or bigger then the delivered one
- it could be also smaller when user asks for higher then archived grid resolution (resulting in artificially bigger interpolated files - situation which should be rather avoided)

select  to_char(month, 'MM/YYYY') as monthyear, CAST(delivered AS FLOAT)/1024^4 as "delivered" from datasets_stats_monthly where dataset='yopp' and month>=date'20170701' 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='yopp' and month>=date'20170701' order by month

Users

user requests: number of MARS retrieval requests based on original user request (each user request leads usually to more MARS retrieval requests depending on the required period, parameters, level types etc)
active users: users with at least 3 requests in given month

select  to_char(month, 'MM/YYYY') as monthyear, requests as "user requests" from datasets_stats_monthly where dataset='yopp' and month>=date'20170701' order by month;

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

Users per country [%]

as per 1.5. 2018
only countries with more than XXX are shown

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='yopp' and users_stats_monthly.month>=date'20170701'
GROUP BY profile_country.name
HAVING sum(users_stats_monthly.requests) > 5000
ORDER BY total_users DESC