Page History

Overview

SELECT to_char(

Grand_total/1024^4, 'FM999,999,999,999

.000') FROM mars_dataset_details
WHERE 

name = 'tigge-lam' ORDER BY Date DESC LIMIT 1

SELECT to_char(

Number_of_fields, 'FM999,999,999,999

') FROM mars_dataset_details
WHERE name = 'tigge-lam' ORDER BY Date DESC LIMIT 1

SELECT date FROM mars_dataset_details
WHERE name = 'tigge-lam' ORDER BY Date DESC LIMIT 1

SELECT count(DISTINCT auth_user.username) as "Total number of users" FROM auth_user
 RIGHT JOIN users_stats_monthly ON users_stats_monthly.username = auth_user.username
 WHERE users_stats_monthly.dataset='tigge-lam' and users_stats_monthly.month>=date'2013-01-01'
AND auth_user.username IN (
 SELECT username 
 FROM users_stats_monthly
 WHERE dataset='tigge-lam'
 GROUP BY username
 HAVING sum(requests) >= 3)

SELECT to_char(sum(delivered/1024^4), 'FM999,999,999,990.999')

FROM datasets_stats_monthly WHERE datasets_stats_monthly.dataset='tigge-lam' and datasets_stats_monthly.month>=date'2013-01-01';

SELECT to_char(sum(fields),'FM999,999,999,999,999')
FROM datasets_stats_monthly
WHERE datasets_stats_monthly.dataset='tigge-lam' and datasets_stats_monthly.month>=date'2013-01-01';

SELECT to_char(sum(requests), 'FM999,999,999,999,999')
FROM datasets_stats_monthly
WHERE datasets_stats_monthly.dataset='tigge-lam' and datasets_stats_monthly.month>=date'

2013-01-01';

Monthly sums

...

Data volumes

Volume of data retrieved from MARS before post-processing vs volume of data delivered to users after post-processing.

select  to_char(month, 'MM/YYYY') as monthyear, CASTto_char(delivered AS FLOAT)/1024^4/1024^3, 'FM999,999,999,990.999') as "data volumedelivered" from datasets_stats_monthly where dataset='tigge-lam' and month>=date'201301012013-01-01' 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 requests shown here)

select   ;
select to_char(month, 'MM/YYYY') as monthyear, requests, to_char(retrieved/1024^3, 'FM999,999,999,990.999') as "retrieved" from datasets_stats_monthly where dataset='tigge-lam' and month>=date'201301012013-01-01' order by month;

Fields retrieved

Number of requests

(peak values not fully displayed:  03/2016: 2,770     09/2016 : 104,283     10/2016: 223,116(1 field means the retrieved field for given step, level, etc)

select  to_char(month, 'MM/YYYY') as monthyear, fieldsrequests as "user requests" from datasets_stats_monthly where dataset='tigge-lam' and month>=date'201301012013-01-01' order by month

Users

(Users at least with 3 requests ever since)

;

Number of active users

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

Users per

...

country

• as per  2018-06-01
  
  

(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-lam' and users_stats_monthly.month>=date'201301012013-01-01'
GROUP BY profile_country.name
HAVING count(DISTINCT auth_user.username)>=1
ORDER BY total_users DESC;

...