prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  Trending Projects are not updated
  I think in the past month that I've been looking at pypi.org (and even before that), the "Trending Projects" haven't changed.
  
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `warehouse/packaging/tasks.py`
  Content:
  ```
  1 # Licensed under the Apache License, Version 2.0 (the "License");
  2 # you may not use this file except in compliance with the License.
  3 # You may obtain a copy of the License at
  4 #
  5 # http://www.apache.org/licenses/LICENSE-2.0
  6 #
  7 # Unless required by applicable law or agreed to in writing, software
  8 # distributed under the License is distributed on an "AS IS" BASIS,
  9 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  10 # See the License for the specific language governing permissions and
  11 # limitations under the License.
  12 
  13 from warehouse import tasks
  14 from warehouse.cache.origin import IOriginCache
  15 from warehouse.packaging.models import Project
  16 
  17 
  18 @tasks.task(ignore_result=True, acks_late=True)
  19 def compute_trending(request):
  20     bq = request.find_service(name="gcloud.bigquery")
  21     query = bq.run_sync_query(
  22         """ SELECT project,
  23                    IF(
  24                         STDDEV(downloads) > 0,
  25                         (todays_downloads - AVG(downloads))/STDDEV(downloads),
  26                         NULL
  27                     ) as zscore
  28             FROM (
  29                 SELECT project,
  30                        date,
  31                        downloads,
  32                        FIRST_VALUE(downloads) OVER (
  33                             PARTITION BY project
  34                             ORDER BY DATE DESC
  35                             ROWS BETWEEN UNBOUNDED PRECEDING
  36                                 AND UNBOUNDED FOLLOWING
  37                         ) as todays_downloads
  38                 FROM (
  39                     SELECT file.project as project,
  40                            DATE(timestamp) AS date,
  41                            COUNT(*) as downloads
  42                     FROM `{table}`
  43                     WHERE _TABLE_SUFFIX BETWEEN
  44                         FORMAT_DATE(
  45                             "%Y%m%d",
  46                             DATE_ADD(CURRENT_DATE(), INTERVAL -31 day))
  47                         AND
  48                         FORMAT_DATE(
  49                             "%Y%m%d",
  50                             DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))
  51                     GROUP BY file.project, date
  52                 )
  53             )
  54             GROUP BY project, todays_downloads
  55             HAVING SUM(downloads) >= 5000
  56             ORDER BY zscore DESC
  57         """.format(table=request.registry.settings["warehouse.trending_table"])
  58     )
  59     query.use_legacy_sql = False
  60     query.run()
  61 
  62     zscores = {}
  63     page_token = None
  64     while True:
  65         rows, total_rows, page_token = query.fetch_data(
  66             max_results=1000,
  67             page_token=page_token,
  68         )
  69 
  70         zscores.update(dict(rows))
  71 
  72         if not page_token:
  73             break
  74 
  75     # We're going to "reset" all of our zscores to a steady state where they
  76     # are all equal to ``None``. The next query will then set any that have a
  77     # value back to the expected value.
  78     (request.db.query(Project)
  79                .filter(Project.zscore != None)  # noqa
  80                .update({Project.zscore: None}))
  81 
  82     # We need to convert the normalized name that we get out of BigQuery and
  83     # turn it into the primary key of the Project object and construct a list
  84     # of primary key: new zscore, including a default of None if the item isn't
  85     # in the result set.
  86     query = request.db.query(Project.name, Project.normalized_name).all()
  87     to_update = [
  88         {"name": name, "zscore": zscores[normalized_name]}
  89         for name, normalized_name in query
  90         if normalized_name in zscores
  91     ]
  92 
  93     # Reflect out updated ZScores into the database.
  94     request.db.bulk_update_mappings(Project, to_update)
  95 
  96     # Trigger a purge of the trending surrogate key.
  97     try:
  98         cacher = request.find_service(IOriginCache)
  99     except ValueError:
  100         pass
  101     else:
  102         cacher.purge(["trending"])
  103
  ```
  --- 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/warehouse/packaging/tasks.py b/warehouse/packaging/tasks.py
  --- a/warehouse/packaging/tasks.py
  +++ b/warehouse/packaging/tasks.py
  @@ -18,7 +18,7 @@
   @tasks.task(ignore_result=True, acks_late=True)
   def compute_trending(request):
       bq = request.find_service(name="gcloud.bigquery")
  -    query = bq.run_sync_query(
  +    query = bq.query(
           """ SELECT project,
                      IF(
                           STDDEV(downloads) > 0,
  @@ -56,21 +56,11 @@
               ORDER BY zscore DESC
           """.format(table=request.registry.settings["warehouse.trending_table"])
       )
  -    query.use_legacy_sql = False
  -    query.run()
   
       zscores = {}
  -    page_token = None
  -    while True:
  -        rows, total_rows, page_token = query.fetch_data(
  -            max_results=1000,
  -            page_token=page_token,
  -        )
  -
  -        zscores.update(dict(rows))
  -
  -        if not page_token:
  -            break
  +    for row in query.result():
  +        row = dict(row)
  +        zscores[row["project"]] = row["zscore"]
   
       # We're going to "reset" all of our zscores to a steady state where they
       # are all equal to ``None``. The next query will then set any that have a
  
verification_info: {"golden_diff": "diff --git a/warehouse/packaging/tasks.py b/warehouse/packaging/tasks.py\n--- a/warehouse/packaging/tasks.py\n+++ b/warehouse/packaging/tasks.py\n@@ -18,7 +18,7 @@\n @tasks.task(ignore_result=True, acks_late=True)\n def compute_trending(request):\n     bq = request.find_service(name=\"gcloud.bigquery\")\n-    query = bq.run_sync_query(\n+    query = bq.query(\n         \"\"\" SELECT project,\n                    IF(\n                         STDDEV(downloads) > 0,\n@@ -56,21 +56,11 @@\n             ORDER BY zscore DESC\n         \"\"\".format(table=request.registry.settings[\"warehouse.trending_table\"])\n     )\n-    query.use_legacy_sql = False\n-    query.run()\n \n     zscores = {}\n-    page_token = None\n-    while True:\n-        rows, total_rows, page_token = query.fetch_data(\n-            max_results=1000,\n-            page_token=page_token,\n-        )\n-\n-        zscores.update(dict(rows))\n-\n-        if not page_token:\n-            break\n+    for row in query.result():\n+        row = dict(row)\n+        zscores[row[\"project\"]] = row[\"zscore\"]\n \n     # We're going to \"reset\" all of our zscores to a steady state where they\n     # are all equal to ``None``. The next query will then set any that have a\n", "issue": "Trending Projects are not updated\nI think in the past month that I've been looking at pypi.org (and even before that), the \"Trending Projects\" haven't changed.\r\n\n", "before_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nfrom warehouse import tasks\nfrom warehouse.cache.origin import IOriginCache\nfrom warehouse.packaging.models import Project\n\n\n@tasks.task(ignore_result=True, acks_late=True)\ndef compute_trending(request):\n    bq = request.find_service(name=\"gcloud.bigquery\")\n    query = bq.run_sync_query(\n        \"\"\" SELECT project,\n                   IF(\n                        STDDEV(downloads) > 0,\n                        (todays_downloads - AVG(downloads))/STDDEV(downloads),\n                        NULL\n                    ) as zscore\n            FROM (\n                SELECT project,\n                       date,\n                       downloads,\n                       FIRST_VALUE(downloads) OVER (\n                            PARTITION BY project\n                            ORDER BY DATE DESC\n                            ROWS BETWEEN UNBOUNDED PRECEDING\n                                AND UNBOUNDED FOLLOWING\n                        ) as todays_downloads\n                FROM (\n                    SELECT file.project as project,\n                           DATE(timestamp) AS date,\n                           COUNT(*) as downloads\n                    FROM `{table}`\n                    WHERE _TABLE_SUFFIX BETWEEN\n                        FORMAT_DATE(\n                            \"%Y%m%d\",\n                            DATE_ADD(CURRENT_DATE(), INTERVAL -31 day))\n                        AND\n                        FORMAT_DATE(\n                            \"%Y%m%d\",\n                            DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))\n                    GROUP BY file.project, date\n                )\n            )\n            GROUP BY project, todays_downloads\n            HAVING SUM(downloads) >= 5000\n            ORDER BY zscore DESC\n        \"\"\".format(table=request.registry.settings[\"warehouse.trending_table\"])\n    )\n    query.use_legacy_sql = False\n    query.run()\n\n    zscores = {}\n    page_token = None\n    while True:\n        rows, total_rows, page_token = query.fetch_data(\n            max_results=1000,\n            page_token=page_token,\n        )\n\n        zscores.update(dict(rows))\n\n        if not page_token:\n            break\n\n    # We're going to \"reset\" all of our zscores to a steady state where they\n    # are all equal to ``None``. The next query will then set any that have a\n    # value back to the expected value.\n    (request.db.query(Project)\n               .filter(Project.zscore != None)  # noqa\n               .update({Project.zscore: None}))\n\n    # We need to convert the normalized name that we get out of BigQuery and\n    # turn it into the primary key of the Project object and construct a list\n    # of primary key: new zscore, including a default of None if the item isn't\n    # in the result set.\n    query = request.db.query(Project.name, Project.normalized_name).all()\n    to_update = [\n        {\"name\": name, \"zscore\": zscores[normalized_name]}\n        for name, normalized_name in query\n        if normalized_name in zscores\n    ]\n\n    # Reflect out updated ZScores into the database.\n    request.db.bulk_update_mappings(Project, to_update)\n\n    # Trigger a purge of the trending surrogate key.\n    try:\n        cacher = request.find_service(IOriginCache)\n    except ValueError:\n        pass\n    else:\n        cacher.purge([\"trending\"])\n", "path": "warehouse/packaging/tasks.py"}], "after_files": [{"content": "# Licensed under the Apache License, Version 2.0 (the \"License\");\n# you may not use this file except in compliance with the License.\n# You may obtain a copy of the License at\n#\n# http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an \"AS IS\" BASIS,\n# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n# See the License for the specific language governing permissions and\n# limitations under the License.\n\nfrom warehouse import tasks\nfrom warehouse.cache.origin import IOriginCache\nfrom warehouse.packaging.models import Project\n\n\n@tasks.task(ignore_result=True, acks_late=True)\ndef compute_trending(request):\n    bq = request.find_service(name=\"gcloud.bigquery\")\n    query = bq.query(\n        \"\"\" SELECT project,\n                   IF(\n                        STDDEV(downloads) > 0,\n                        (todays_downloads - AVG(downloads))/STDDEV(downloads),\n                        NULL\n                    ) as zscore\n            FROM (\n                SELECT project,\n                       date,\n                       downloads,\n                       FIRST_VALUE(downloads) OVER (\n                            PARTITION BY project\n                            ORDER BY DATE DESC\n                            ROWS BETWEEN UNBOUNDED PRECEDING\n                                AND UNBOUNDED FOLLOWING\n                        ) as todays_downloads\n                FROM (\n                    SELECT file.project as project,\n                           DATE(timestamp) AS date,\n                           COUNT(*) as downloads\n                    FROM `{table}`\n                    WHERE _TABLE_SUFFIX BETWEEN\n                        FORMAT_DATE(\n                            \"%Y%m%d\",\n                            DATE_ADD(CURRENT_DATE(), INTERVAL -31 day))\n                        AND\n                        FORMAT_DATE(\n                            \"%Y%m%d\",\n                            DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))\n                    GROUP BY file.project, date\n                )\n            )\n            GROUP BY project, todays_downloads\n            HAVING SUM(downloads) >= 5000\n            ORDER BY zscore DESC\n        \"\"\".format(table=request.registry.settings[\"warehouse.trending_table\"])\n    )\n\n    zscores = {}\n    for row in query.result():\n        row = dict(row)\n        zscores[row[\"project\"]] = row[\"zscore\"]\n\n    # We're going to \"reset\" all of our zscores to a steady state where they\n    # are all equal to ``None``. The next query will then set any that have a\n    # value back to the expected value.\n    (request.db.query(Project)\n               .filter(Project.zscore != None)  # noqa\n               .update({Project.zscore: None}))\n\n    # We need to convert the normalized name that we get out of BigQuery and\n    # turn it into the primary key of the Project object and construct a list\n    # of primary key: new zscore, including a default of None if the item isn't\n    # in the result set.\n    query = request.db.query(Project.name, Project.normalized_name).all()\n    to_update = [\n        {\"name\": name, \"zscore\": zscores[normalized_name]}\n        for name, normalized_name in query\n        if normalized_name in zscores\n    ]\n\n    # Reflect out updated ZScores into the database.\n    request.db.bulk_update_mappings(Project, to_update)\n\n    # Trigger a purge of the trending surrogate key.\n    try:\n        cacher = request.find_service(IOriginCache)\n    except ValueError:\n        pass\n    else:\n        cacher.purge([\"trending\"])\n", "path": "warehouse/packaging/tasks.py"}]}