def realm_summary_table(realm_minutes: Dict[(str, float)]) -> str:
    now = timezone_now()
    query = SQL("\n        SELECT\n            realm.string_id,\n            realm.date_created,\n            realm.plan_type,\n            coalesce(wau_table.value, 0) wau_count,\n            coalesce(dau_table.value, 0) dau_count,\n            coalesce(user_count_table.value, 0) user_profile_count,\n            coalesce(bot_count_table.value, 0) bot_count\n        FROM\n            zerver_realm as realm\n            LEFT OUTER JOIN (\n                SELECT\n                    value _14day_active_humans,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'realm_active_humans::day'\n                    AND end_time = %(realm_active_humans_end_time)s\n            ) as _14day_active_humans_table ON realm.id = _14day_active_humans_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = '7day_actives::day'\n                    AND end_time = %(seven_day_actives_end_time)s\n            ) as wau_table ON realm.id = wau_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = '1day_actives::day'\n                    AND end_time = %(one_day_actives_end_time)s\n            ) as dau_table ON realm.id = dau_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'active_users_audit:is_bot:day'\n                    AND subgroup = 'false'\n                    AND end_time = %(active_users_audit_end_time)s\n            ) as user_count_table ON realm.id = user_count_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'active_users_audit:is_bot:day'\n                    AND subgroup = 'true'\n                    AND end_time = %(active_users_audit_end_time)s\n            ) as bot_count_table ON realm.id = bot_count_table.realm_id\n        WHERE\n            _14day_active_humans IS NOT NULL\n            or realm.plan_type = 3\n        ORDER BY\n            dau_count DESC,\n            string_id ASC\n    ")
    cursor = connection.cursor()
    cursor.execute(query, {'realm_active_humans_end_time': COUNT_STATS['realm_active_humans::day'].last_successful_fill(), 'seven_day_actives_end_time': COUNT_STATS['7day_actives::day'].last_successful_fill(), 'one_day_actives_end_time': COUNT_STATS['1day_actives::day'].last_successful_fill(), 'active_users_audit_end_time': COUNT_STATS['active_users_audit:is_bot:day'].last_successful_fill()})
    rows = dictfetchall(cursor)
    cursor.close()
    realm_admins: Dict[(str, List[str])] = defaultdict(list)
    for up in UserProfile.objects.select_related('realm').filter(role=UserProfile.ROLE_REALM_ADMINISTRATOR, is_active=True):
        realm_admins[up.realm.string_id].append(up.delivery_email)
    for row in rows:
        row['date_created_day'] = row['date_created'].strftime('%Y-%m-%d')
        row['plan_type_string'] = get_plan_name(row['plan_type'])
        row['age_days'] = int(((now - row['date_created']).total_seconds() / 86400))
        row['is_new'] = (row['age_days'] < (12 * 7))
        row['realm_admin_email'] = ', '.join(realm_admins[row['string_id']])
    counts = get_realm_day_counts()
    for row in rows:
        try:
            row['history'] = counts[row['string_id']]['cnts']
        except Exception:
            row['history'] = ''
    total_amount = 0
    if settings.BILLING_ENABLED:
        from corporate.lib.stripe import estimate_annual_recurring_revenue_by_realm
        estimated_arrs = estimate_annual_recurring_revenue_by_realm()
        for row in rows:
            if (row['string_id'] in estimated_arrs):
                row['amount'] = estimated_arrs[row['string_id']]
        total_amount += sum(estimated_arrs.values())
    total_hours = 0.0
    for row in rows:
        string_id = row['string_id']
        minutes = realm_minutes.get(string_id, 0.0)
        hours = (minutes / 60.0)
        total_hours += hours
        row['hours'] = str(int(hours))
        try:
            row['hours_per_user'] = '{:.1f}'.format((hours / row['dau_count']))
        except Exception:
            pass
    for row in rows:
        row['stats_link'] = realm_stats_link(row['string_id'])
        row['string_id'] = realm_activity_link(row['string_id'])

    def meets_goal(row: Dict[(str, int)]) -> bool:
        return (row['dau_count'] >= 5)
    num_active_sites = len(list(filter(meets_goal, rows)))
    total_dau_count = 0
    total_user_profile_count = 0
    total_bot_count = 0
    total_wau_count = 0
    for row in rows:
        total_dau_count += int(row['dau_count'])
        total_user_profile_count += int(row['user_profile_count'])
        total_bot_count += int(row['bot_count'])
        total_wau_count += int(row['wau_count'])
    total_row = dict(string_id='Total', plan_type_string='', amount=total_amount, stats_link='', date_created_day='', realm_admin_email='', dau_count=total_dau_count, user_profile_count=total_user_profile_count, bot_count=total_bot_count, hours=int(total_hours), wau_count=total_wau_count)
    rows.insert(0, total_row)
    content = loader.render_to_string('analytics/realm_summary_table.html', dict(rows=rows, num_active_sites=num_active_sites, utctime=now.strftime('%Y-%m-%d %H:%MZ')))
    return content
def realm_summary_table(realm_minutes: Dict[(str, float)]) -> str:
    now = timezone_now()
    query = SQL("\n        SELECT\n            realm.string_id,\n            realm.date_created,\n            realm.plan_type,\n            coalesce(wau_table.value, 0) wau_count,\n            coalesce(dau_table.value, 0) dau_count,\n            coalesce(user_count_table.value, 0) user_profile_count,\n            coalesce(bot_count_table.value, 0) bot_count\n        FROM\n            zerver_realm as realm\n            LEFT OUTER JOIN (\n                SELECT\n                    value _14day_active_humans,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'realm_active_humans::day'\n                    AND end_time = %(realm_active_humans_end_time)s\n            ) as _14day_active_humans_table ON realm.id = _14day_active_humans_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = '7day_actives::day'\n                    AND end_time = %(seven_day_actives_end_time)s\n            ) as wau_table ON realm.id = wau_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = '1day_actives::day'\n                    AND end_time = %(one_day_actives_end_time)s\n            ) as dau_table ON realm.id = dau_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'active_users_audit:is_bot:day'\n                    AND subgroup = 'false'\n                    AND end_time = %(active_users_audit_end_time)s\n            ) as user_count_table ON realm.id = user_count_table.realm_id\n            LEFT OUTER JOIN (\n                SELECT\n                    value,\n                    realm_id\n                from\n                    analytics_realmcount\n                WHERE\n                    property = 'active_users_audit:is_bot:day'\n                    AND subgroup = 'true'\n                    AND end_time = %(active_users_audit_end_time)s\n            ) as bot_count_table ON realm.id = bot_count_table.realm_id\n        WHERE\n            _14day_active_humans IS NOT NULL\n            or realm.plan_type = 3\n        ORDER BY\n            dau_count DESC,\n            string_id ASC\n    ")
    cursor = connection.cursor()
    cursor.execute(query, {'realm_active_humans_end_time': COUNT_STATS['realm_active_humans::day'].last_successful_fill(), 'seven_day_actives_end_time': COUNT_STATS['7day_actives::day'].last_successful_fill(), 'one_day_actives_end_time': COUNT_STATS['1day_actives::day'].last_successful_fill(), 'active_users_audit_end_time': COUNT_STATS['active_users_audit:is_bot:day'].last_successful_fill()})
    rows = dictfetchall(cursor)
    cursor.close()
    realm_admins: Dict[(str, List[str])] = defaultdict(list)
    for up in UserProfile.objects.select_related('realm').filter(role=UserProfile.ROLE_REALM_ADMINISTRATOR, is_active=True):
        realm_admins[up.realm.string_id].append(up.delivery_email)
    for row in rows:
        row['date_created_day'] = row['date_created'].strftime('%Y-%m-%d')
        row['plan_type_string'] = get_plan_name(row['plan_type'])
        row['age_days'] = int(((now - row['date_created']).total_seconds() / 86400))
        row['is_new'] = (row['age_days'] < (12 * 7))
        row['realm_admin_email'] = ', '.join(realm_admins[row['string_id']])
    counts = get_realm_day_counts()
    for row in rows:
        try:
            row['history'] = counts[row['string_id']]['cnts']
        except Exception:
            row['history'] = 
    total_amount = 0
    if settings.BILLING_ENABLED:
        from corporate.lib.stripe import estimate_annual_recurring_revenue_by_realm
        estimated_arrs = estimate_annual_recurring_revenue_by_realm()
        for row in rows:
            if (row['string_id'] in estimated_arrs):
                row['amount'] = estimated_arrs[row['string_id']]
        total_amount += sum(estimated_arrs.values())
    total_hours = 0.0
    for row in rows:
        string_id = row['string_id']
        minutes = realm_minutes.get(string_id, 0.0)
        hours = (minutes / 60.0)
        total_hours += hours
        row['hours'] = str(int(hours))
        try:
            row['hours_per_user'] = '{:.1f}'.format((hours / row['dau_count']))
        except Exception:
            pass
    for row in rows:
        row['stats_link'] = realm_stats_link(row['string_id'])
        row['string_id'] = realm_activity_link(row['string_id'])

    def meets_goal(row: Dict[(str, int)]) -> bool:
        return (row['dau_count'] >= 5)
    num_active_sites = len(list(filter(meets_goal, rows)))
    total_dau_count = 0
    total_user_profile_count = 0
    total_bot_count = 0
    total_wau_count = 0
    for row in rows:
        total_dau_count += int(row['dau_count'])
        total_user_profile_count += int(row['user_profile_count'])
        total_bot_count += int(row['bot_count'])
        total_wau_count += int(row['wau_count'])
    total_row = dict(string_id='Total', plan_type_string=, amount=total_amount, stats_link=, date_created_day=, realm_admin_email=, dau_count=total_dau_count, user_profile_count=total_user_profile_count, bot_count=total_bot_count, hours=int(total_hours), wau_count=total_wau_count)
    rows.insert(0, total_row)
    content = loader.render_to_string('analytics/realm_summary_table.html', dict(rows=rows, num_active_sites=num_active_sites, utctime=now.strftime('%Y-%m-%d %H:%MZ')))
    return content