prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  Improve performance of _get_creator_counts util function
  `kitsune.community.utils._get_creator_counts` until function is DB heavy and takes a lot of time to execute. Evaluate its usefulness and provide a way to optimize the query and/or cache the results.  
  
  This issue is related to the degraded performance SUMO experienced on Fri March 30th ([NR Error](https://rpm.newrelic.com/accounts/1299394/applications/45097089/downtime/34422892))
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `kitsune/community/utils.py`
  Content:
  ```
  1 import hashlib
  2 
  3 from datetime import datetime, date, timedelta
  4 from django.conf import settings
  5 from django.core.cache import cache
  6 from django.db.models import Count, F
  7 
  8 from kitsune.products.models import Product
  9 from kitsune.questions.models import Answer
  10 from kitsune.users.models import User, UserMappingType
  11 from kitsune.wiki.models import Revision
  12 
  13 
  14 def top_contributors_questions(start=None, end=None, locale=None, product=None,
  15                                count=10, page=1, use_cache=True):
  16     """Get the top Support Forum contributors."""
  17     if use_cache:
  18         cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)
  19         cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()
  20         cache_key = 'top_contributors_questions_{}'.format(cache_key)
  21         cached = cache.get(cache_key, None)
  22         if cached:
  23             return cached
  24 
  25     answers = (Answer.objects
  26                .exclude(is_spam=True)
  27                .exclude(question__is_spam=True)
  28                # Adding answer to your own question, isn't a contribution.
  29                .exclude(creator_id=F('question__creator_id')))
  30 
  31     if start is None:
  32         # By default we go back 90 days.
  33         start = date.today() - timedelta(days=90)
  34         answers = answers.filter(created__gte=start)
  35     if end:
  36         # If no end is specified, we don't need to filter by it.
  37         answers = answers.filter(created__lt=end)
  38     if locale:
  39         answers = answers.filter(question__locale=locale)
  40     if product:
  41         if isinstance(product, Product):
  42             product = product.slug
  43         answers = answers.filter(question__product__slug=product)
  44 
  45     users = (User.objects
  46              .filter(answers__in=answers)
  47              .annotate(query_count=Count('answers'))
  48              .order_by('-query_count'))
  49     counts = _get_creator_counts(users, count, page)
  50 
  51     if use_cache:
  52         cache.set(cache_key, counts, 60*15)  # 15 minutes
  53     return counts
  54 
  55 
  56 def top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):
  57     """Get the top KB editors (locale='en-US')."""
  58     return top_contributors_l10n(
  59         start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)
  60 
  61 
  62 def top_contributors_l10n(start=None, end=None, locale=None, product=None,
  63                           count=10, page=1, use_cache=True):
  64     """Get the top l10n contributors for the KB."""
  65     if use_cache:
  66         cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)
  67         cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()
  68         cache_key = u'top_contributors_l10n_{}'.format(cache_key)
  69         cached = cache.get(cache_key, None)
  70         if cached:
  71             return cached
  72 
  73     # Get the user ids and contribution count of the top contributors.
  74     revisions = Revision.objects.all()
  75     if locale is None:
  76         # If there is no locale specified, exclude en-US only. The rest are
  77         # l10n.
  78         revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)
  79     if start is None:
  80         # By default we go back 90 days.
  81         start = date.today() - timedelta(days=90)
  82         revisions = revisions.filter(created__gte=start)
  83     if end:
  84         # If no end is specified, we don't need to filter by it.
  85         revisions = revisions.filter(created__lt=end)
  86     if locale:
  87         revisions = revisions.filter(document__locale=locale)
  88     if product:
  89         if isinstance(product, Product):
  90             product = product.slug
  91         revisions = revisions.filter(document__products__slug=product)
  92 
  93     users = (User.objects
  94              .filter(created_revisions__in=revisions)
  95              .annotate(query_count=Count('created_revisions'))
  96              .order_by('-query_count'))
  97     counts = _get_creator_counts(users, count, page)
  98 
  99     if use_cache:
  100         cache.set(cache_key, counts, 60*15)  # 15 minutes
  101     return counts
  102 
  103 
  104 def top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):
  105     """Get the top Army of Awesome contributors."""
  106     # AoA is deprecated, return 0 until we remove all related code.
  107     return ([], 0)
  108 
  109 
  110 def _get_creator_counts(query, count, page):
  111     total = query.count()
  112 
  113     start = (page - 1) * count
  114     end = page * count
  115     query_data = query.values('id', 'query_count')[start:end]
  116 
  117     query_data = {obj['id']: obj['query_count'] for obj in query_data}
  118 
  119     users_data = (UserMappingType.search().filter(id__in=query_data.keys())
  120                                  .values_dict('id', 'username', 'display_name',
  121                                               'avatar', 'twitter_usernames',
  122                                               'last_contribution_date')[:count])
  123 
  124     users_data = UserMappingType.reshape(users_data)
  125 
  126     results = []
  127     now = datetime.now()
  128 
  129     for u_data in users_data:
  130         user_id = u_data.get('id')
  131         last_contribution_date = u_data.get('last_contribution_date', None)
  132 
  133         u_data['days_since_last_activity'] = ((now - last_contribution_date).days
  134                                               if last_contribution_date else None)
  135 
  136         data = {
  137             'count': query_data.get(user_id),
  138             'term': user_id,
  139             'user': u_data
  140         }
  141 
  142         results.append(data)
  143 
  144     return results, total
  145
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/kitsune/community/utils.py b/kitsune/community/utils.py
  --- a/kitsune/community/utils.py
  +++ b/kitsune/community/utils.py
  @@ -1,6 +1,8 @@
   import hashlib
   
   from datetime import datetime, date, timedelta
  +from operator import itemgetter
  +
   from django.conf import settings
   from django.core.cache import cache
   from django.db.models import Count, F
  @@ -141,4 +143,8 @@
   
           results.append(data)
   
  +    # Descending Order the list according to count.
  +    # As the top number of contributor should be at first
  +    results = sorted(results, key=itemgetter('count'), reverse=True)
  +
       return results, total
  
verification_info: {"golden_diff": "diff --git a/kitsune/community/utils.py b/kitsune/community/utils.py\n--- a/kitsune/community/utils.py\n+++ b/kitsune/community/utils.py\n@@ -1,6 +1,8 @@\n import hashlib\n \n from datetime import datetime, date, timedelta\n+from operator import itemgetter\n+\n from django.conf import settings\n from django.core.cache import cache\n from django.db.models import Count, F\n@@ -141,4 +143,8 @@\n \n         results.append(data)\n \n+    # Descending Order the list according to count.\n+    # As the top number of contributor should be at first\n+    results = sorted(results, key=itemgetter('count'), reverse=True)\n+\n     return results, total\n", "issue": "Improve performance of _get_creator_counts util function\n`kitsune.community.utils._get_creator_counts` until function is DB heavy and takes a lot of time to execute. Evaluate its usefulness and provide a way to optimize the query and/or cache the results.  \r\n\r\nThis issue is related to the degraded performance SUMO experienced on Fri March 30th ([NR Error](https://rpm.newrelic.com/accounts/1299394/applications/45097089/downtime/34422892))\n", "before_files": [{"content": "import hashlib\n\nfrom datetime import datetime, date, timedelta\nfrom django.conf import settings\nfrom django.core.cache import cache\nfrom django.db.models import Count, F\n\nfrom kitsune.products.models import Product\nfrom kitsune.questions.models import Answer\nfrom kitsune.users.models import User, UserMappingType\nfrom kitsune.wiki.models import Revision\n\n\ndef top_contributors_questions(start=None, end=None, locale=None, product=None,\n                               count=10, page=1, use_cache=True):\n    \"\"\"Get the top Support Forum contributors.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = 'top_contributors_questions_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    answers = (Answer.objects\n               .exclude(is_spam=True)\n               .exclude(question__is_spam=True)\n               # Adding answer to your own question, isn't a contribution.\n               .exclude(creator_id=F('question__creator_id')))\n\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        answers = answers.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        answers = answers.filter(created__lt=end)\n    if locale:\n        answers = answers.filter(question__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        answers = answers.filter(question__product__slug=product)\n\n    users = (User.objects\n             .filter(answers__in=answers)\n             .annotate(query_count=Count('answers'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top KB editors (locale='en-US').\"\"\"\n    return top_contributors_l10n(\n        start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)\n\n\ndef top_contributors_l10n(start=None, end=None, locale=None, product=None,\n                          count=10, page=1, use_cache=True):\n    \"\"\"Get the top l10n contributors for the KB.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = u'top_contributors_l10n_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    # Get the user ids and contribution count of the top contributors.\n    revisions = Revision.objects.all()\n    if locale is None:\n        # If there is no locale specified, exclude en-US only. The rest are\n        # l10n.\n        revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        revisions = revisions.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        revisions = revisions.filter(created__lt=end)\n    if locale:\n        revisions = revisions.filter(document__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        revisions = revisions.filter(document__products__slug=product)\n\n    users = (User.objects\n             .filter(created_revisions__in=revisions)\n             .annotate(query_count=Count('created_revisions'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top Army of Awesome contributors.\"\"\"\n    # AoA is deprecated, return 0 until we remove all related code.\n    return ([], 0)\n\n\ndef _get_creator_counts(query, count, page):\n    total = query.count()\n\n    start = (page - 1) * count\n    end = page * count\n    query_data = query.values('id', 'query_count')[start:end]\n\n    query_data = {obj['id']: obj['query_count'] for obj in query_data}\n\n    users_data = (UserMappingType.search().filter(id__in=query_data.keys())\n                                 .values_dict('id', 'username', 'display_name',\n                                              'avatar', 'twitter_usernames',\n                                              'last_contribution_date')[:count])\n\n    users_data = UserMappingType.reshape(users_data)\n\n    results = []\n    now = datetime.now()\n\n    for u_data in users_data:\n        user_id = u_data.get('id')\n        last_contribution_date = u_data.get('last_contribution_date', None)\n\n        u_data['days_since_last_activity'] = ((now - last_contribution_date).days\n                                              if last_contribution_date else None)\n\n        data = {\n            'count': query_data.get(user_id),\n            'term': user_id,\n            'user': u_data\n        }\n\n        results.append(data)\n\n    return results, total\n", "path": "kitsune/community/utils.py"}], "after_files": [{"content": "import hashlib\n\nfrom datetime import datetime, date, timedelta\nfrom operator import itemgetter\n\nfrom django.conf import settings\nfrom django.core.cache import cache\nfrom django.db.models import Count, F\n\nfrom kitsune.products.models import Product\nfrom kitsune.questions.models import Answer\nfrom kitsune.users.models import User, UserMappingType\nfrom kitsune.wiki.models import Revision\n\n\ndef top_contributors_questions(start=None, end=None, locale=None, product=None,\n                               count=10, page=1, use_cache=True):\n    \"\"\"Get the top Support Forum contributors.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = 'top_contributors_questions_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    answers = (Answer.objects\n               .exclude(is_spam=True)\n               .exclude(question__is_spam=True)\n               # Adding answer to your own question, isn't a contribution.\n               .exclude(creator_id=F('question__creator_id')))\n\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        answers = answers.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        answers = answers.filter(created__lt=end)\n    if locale:\n        answers = answers.filter(question__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        answers = answers.filter(question__product__slug=product)\n\n    users = (User.objects\n             .filter(answers__in=answers)\n             .annotate(query_count=Count('answers'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top KB editors (locale='en-US').\"\"\"\n    return top_contributors_l10n(\n        start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)\n\n\ndef top_contributors_l10n(start=None, end=None, locale=None, product=None,\n                          count=10, page=1, use_cache=True):\n    \"\"\"Get the top l10n contributors for the KB.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = u'top_contributors_l10n_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    # Get the user ids and contribution count of the top contributors.\n    revisions = Revision.objects.all()\n    if locale is None:\n        # If there is no locale specified, exclude en-US only. The rest are\n        # l10n.\n        revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        revisions = revisions.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        revisions = revisions.filter(created__lt=end)\n    if locale:\n        revisions = revisions.filter(document__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        revisions = revisions.filter(document__products__slug=product)\n\n    users = (User.objects\n             .filter(created_revisions__in=revisions)\n             .annotate(query_count=Count('created_revisions'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top Army of Awesome contributors.\"\"\"\n    # AoA is deprecated, return 0 until we remove all related code.\n    return ([], 0)\n\n\ndef _get_creator_counts(query, count, page):\n    total = query.count()\n\n    start = (page - 1) * count\n    end = page * count\n    query_data = query.values('id', 'query_count')[start:end]\n\n    query_data = {obj['id']: obj['query_count'] for obj in query_data}\n\n    users_data = (UserMappingType.search().filter(id__in=query_data.keys())\n                                 .values_dict('id', 'username', 'display_name',\n                                              'avatar', 'twitter_usernames',\n                                              'last_contribution_date')[:count])\n\n    users_data = UserMappingType.reshape(users_data)\n\n    results = []\n    now = datetime.now()\n\n    for u_data in users_data:\n        user_id = u_data.get('id')\n        last_contribution_date = u_data.get('last_contribution_date', None)\n\n        u_data['days_since_last_activity'] = ((now - last_contribution_date).days\n                                              if last_contribution_date else None)\n\n        data = {\n            'count': query_data.get(user_id),\n            'term': user_id,\n            'user': u_data\n        }\n\n        results.append(data)\n\n    # Descending Order the list according to count.\n    # As the top number of contributor should be at first\n    results = sorted(results, key=itemgetter('count'), reverse=True)\n\n    return results, total\n", "path": "kitsune/community/utils.py"}]}