 create or replace view metrics_reporting.app_category_run_counts_by_user as
 select count(*) as total_app_run_cnt, 
 IFNULL(acm.app_category,'unable to determine') as app_category,
-uau.username, ui.kb_internal_user
+uau.username, ui.user_id, ui.kb_internal_user
 from metrics.user_info ui 
 inner join metrics.user_app_usage uau
 on ui.username = uau.username

#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.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,

# USER CODE CELLS COUNTS AND DISTRIBUTIONS
#IN MEtrics Reporting
create or replace view metrics_reporting.user_code_cell_counts as
select wc.username, ui.user_id,
sum(code_cells_count) as user_code_cells_count
from metrics_reporting.workspaces_current wc
inner join metrics.user_info ui on ui.username = wc.username
where ui.kb_internal_user = 0
