 on IFNULL(uau.app_name,'not specified') = acm.app_name
 where ui.exclude = False
 group by ui.institution, app_category, app_run_month;
-
-
-
-
 
 
 ---------------------------------------------

#APP RUNS BY USERS AND WORKSPACES
#IN METRICS REPORTING
create or replace view metrics_reporting.user_app_runs as
select uau.username, count(*) as app_runs_count
from metrics.user_app_usage uau
inner join metrics.user_info ui on ui.username = uau.username
where ui.kb_internal_user = 0

#IN METRICS_REPORTING
create or replace view metrics_reporting.user_info_summary_stats as
select ui.username, ui.display_name, ui.email, ui.orcid,
ui.user_id, ui.kb_internal_user, ui.institution, ui.country,
ui.signup_date, ui.last_signin_date, 
round((UNIX_TIMESTAMP(ui.last_signin_date) - UNIX_TIMESTAMP(ui.signup_date))/86400,2) as days_signin_minus_signup,
ceil((NOW() - last_signin_date)/86400) as days_since_last_signin,
