<commit_before>-- Script to get portal usage stats

-- Active Members

select count(*) as "Active Non Tutorial Members" from ma_member_attribute where member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and name='username' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n');

-- GPO IdP Members

select count(*) "Active Non Tutorial GPO IdP Members" from ma_member_attribute where name = 'eppn' and value like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com');

-- Non GPO IdP Members

select count(*) "Active Non Tutorial Non GPO IdP Members" from ma_member_attribute where name = 'eppn' and value not like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com');

-- Non GPO Idp, Non SAVI, Non NTUA, Non .br - so basically InCommon

select count(*) "Active InCommon Members" from ma_member_attribute where name = 'eppn' and value not like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and lower(value) not like '%ntua.gr' and lower(value) not like '%savitestbed.ca' and lower(value) not like '%.br';

-- International users

select count(*) as "Active International Members" from ma_member_attribute where member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and name='username' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id in (select member_id from ma_member_attribute where name = 'email_address' and lower(value) not like '%.com' and lower(value) not like '%.edu' and lower(value) not like '%.mil' and lower(value) not like '%.gov' and lower(value) not like '%.org' and lower(value) not like '%.net');

-- Most popular institutions (using EPPN)

select trim(leading '@' from substring(value from '@.*$')) as "Most common member institutions", count(*) as members from ma_member_attribute where name = 'eppn' group by "Most common member institutions" order by members desc limit 30;

-- Most popular institutions using email, non tutorial
select trim(leading '@' from substring(value from '@.*$')) as "Most common member institutions by email", count(*) as members from ma_member_attribute where name = 'email_address' and value not like '%gpolab.bbn.com' group by "Most common member institutions" order by members desc limit 100;

-- Most popular countries / top level domains
select distinct substring(lower(value) from '%.#"_+#"' for '#') as "Most common TLDs", count(*) as members from ma_member_attribute where name = 'email_address' group by substring(lower(value) from '%.#"_+#"' for '#') order by members desc limit 25;

-- Active users in the last 4 months
select count(distinct member_id) as "Active users in last 4 months" from last_seen where ts > now() - interval '4 months';

select count(*) as "New Portal users in last 4 months" from logging_entry where message like 'Activated GENI user%' and event_time > now() - interval '4 months';<commit_msg>minor fixes to statistics queries<commit_after>-- Script to get portal usage stats

-- Active Members

select count(*) as "Active Non Tutorial Members" from ma_member_attribute where member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and name='username' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n');

-- GPO IdP Members

select count(*) "Active Non Tutorial GPO IdP Members" from ma_member_attribute where name = 'eppn' and value like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com');

-- Non GPO IdP Members

select count(*) "Active Non Tutorial Non GPO IdP Members" from ma_member_attribute where name = 'eppn' and value not like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com');

-- Non GPO Idp, Non SAVI, Non NTUA, Non .br - so basically InCommon

select count(*) "Active InCommon Members" from ma_member_attribute where name = 'eppn' and value not like '%gpolab%' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and lower(value) not like '%ntua.gr' and lower(value) not like '%savitestbed.ca' and lower(value) not like '%.br';

-- International users

select count(*) as "Active International Members" from ma_member_attribute where member_id not in (select member_id from ma_member_attribute where name = 'email_address' and value like '%gpolab.bbn.com') and name='username' and member_id not in (select member_id from ma_member_attribute where name = 'member_enabled' and value = 'n') and member_id in (select member_id from ma_member_attribute where name = 'email_address' and lower(value) not like '%.com' and lower(value) not like '%.edu' and lower(value) not like '%.mil' and lower(value) not like '%.gov' and lower(value) not like '%.org' and lower(value) not like '%.net');

-- Most popular institutions (using EPPN)

select trim(leading '@' from substring(lower(value) from '@.*$')) as "Most common member institutions", count(*) as members from ma_member_attribute where name = 'eppn' group by "Most common member institutions" order by members desc limit 30;

-- Most popular institutions using email, non tutorial
select trim(leading '@' from substring(lower(value) from '@.*$')) as "Most common member institutions by email", count(*) as members from ma_member_attribute where name = 'email_address' and value not like '%gpolab.bbn.com' group by "Most common member institutions by email" order by members descq limit 100;

-- Most popular countries / top level domains
select distinct substring(lower(value) from '%.#"_+#"' for '#') as "Most common TLDs", count(*) as members from ma_member_attribute where name = 'email_address' group by substring(lower(value) from '%.#"_+#"' for '#') order by members desc limit 25;

-- Active users in the last 4 months
select count(distinct member_id) as "Active users in last 4 months" from last_seen where ts > now() - interval '4 months';

select count(*) as "New Portal users in last 4 months" from logging_entry where message like 'Activated GENI user%' and event_time > now() - interval '4 months';
{'fraction_non_alphanumeric': 0.09899100081810744, 'fraction_numerical': 0.00327242977911099, 'mean_word_length': 5.35064935064935, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4296507', 'n_tokens_mistral': 2116, 'n_tokens_neox': 2087, 'n_words': 933}