data_quality_feedback_template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "sfg_SbQWBCmW"
   },
   "source": [
    "## Setup\n",
    "\n",
    "*You must run the cells in this section each time you connect to a new runtime. For example, when you return to the notebook after an idle timeout, when the runtime crashes, or when you restart or factory reset the runtime.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2MRnhij4pmZO"
   },
   "source": [
    "Set name of spreadsheet to export results to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "drokzszlphmK"
   },
   "outputs": [],
   "source": [
    "spreadsheet_name = 'feedback_results'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "F8fwq_F6XMLs"
   },
   "source": [
    "Enter credentials:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "27yRx-oaXPtd"
   },
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "print('Enter your Kingfisher credentials')\n",
    "user = input('Username:')\n",
    "password = getpass.getpass('Password:')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DZASSenGpprv"
   },
   "source": [
    "Setup notebook environment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "--8vgOiP_58f"
   },
   "outputs": [],
   "source": [
    "# Install Kingfisher Colab and required packages\n",
    "%shell pip install --upgrade 'ocdskingfishercolab<0.4' psycopg2-binary > pip.log\n",
    "\n",
    "# Import libraries and functions\n",
    "from google.colab.data_table import DataTable\n",
    "from ocdskingfishercolab import (\n",
    "    list_source_ids,\n",
    "    list_collections,\n",
    "    render_json,\n",
    "    set_spreadsheet_name,\n",
    "    save_dataframe_to_sheet,\n",
    "    save_dataframe_to_spreadsheet,\n",
    "    set_search_path)\n",
    "import pandas as pd\n",
    "\n",
    "# Load https://pypi.org/project/ipython-sql/\n",
    "%load_ext sql \n",
    "\n",
    "# Load https://colab.research.google.com/notebooks/data_table.ipynb\n",
    "%load_ext google.colab.data_table\n",
    "DataTable.max_columns = 50 # Increase max columns so that dataframes with many columns are rendered as data tables\n",
    "DataTable.include_index = False # Remove the index from data tables for easier copy-pasting to Google Docs\n",
    "\n",
    "# Set config\n",
    "%config SqlMagic.autopandas = True  # Return Pandas DataFrames instead of regular result sets\n",
    "%config SqlMagic.displaycon = False  # Don't show connection string after execute\n",
    "%config SqlMagic.feedback = False  # Don't print number of rows affected by DML\n",
    "set_spreadsheet_name(spreadsheet_name) # Set name of spreadsheet for exporting results\n",
    "\n",
    "# Connect to database\n",
    "connection_string = 'postgresql://' + user + ':' + password + '@postgres-readonly.kingfisher.open-contracting.org/ocdskingfisherprocess?sslmode=require'\n",
    "%sql $connection_string\n",
    "  \n",
    "# Install and setup plotting library\n",
    "# Maybe this can also be moved to Kingfisher-Colab?\n",
    "!pip install seaborn\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as tkr\n",
    "\n",
    "colab_dark_style = {'figure.facecolor': '#383838',\n",
    "                       'axes.edgecolor': '#d5d5d5',\n",
    "                       'axes.facecolor': '#383838',\n",
    "                       'axes.labelcolor': '#d5d5d5',\n",
    "                       'text.color': '#d5d5d5',\n",
    "                       'xtick.color': '#d5d5d5',\n",
    "                       'ytick.color': '#d5d5d5'}\n",
    "\n",
    "sns.set_style('dark', colab_dark_style)\n",
    "\n",
    "# Define function to apply number formatting to axis labels\n",
    "# Maybe this can also be moved to Kingfisher-Colab?\n",
    "# Needs updating to support other locales\n",
    "def format_thousands(axis):\n",
    "  axis.set_major_formatter(tkr.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0h0nJSx6Mzm9"
   },
   "source": [
    "## Choose collections and schema\n",
    "\n",
    "*Use this section to choose the collections and schema that you want to query*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IKDe1itVq7dQ"
   },
   "source": [
    "### Set the collection(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-EVkYkVUsP9f"
   },
   "source": [
    "Update `collection_ids` with the `id`(s) of the [Kingfisher Process collection(s)](https://kingfisher-process.readthedocs.io/en/latest/data-model.html#collections):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IKn0TqQIsIGY"
   },
   "outputs": [],
   "source": [
    "collection_ids = (2358, 2359)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ewGMrRM9nNCl"
   },
   "source": [
    "If you don't know which collections you need, run the next cell and use the **Filter** button to filter the [collection table](https://kingfisher-process.readthedocs.io/en/latest/database-structure.html#collection-table) to find the collection(s). You can use the `source_id` column to filter on the `name` of the [Kingfisher Collect spider](https://kingfisher-collect.readthedocs.io/en/latest/spiders.html) used to collect the data. Use the value(s) from the `id` column to update the previous cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ITTvCttGsWj1"
   },
   "outputs": [],
   "source": [
    "list_collections()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aupr2bJKm4wr"
   },
   "source": [
    "### Set the schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "y3mo29DCm4wu"
   },
   "source": [
    "Update `schema_name` with the name of the [Kingfisher Summarize schema](https://kingfisher-summarize.readthedocs.io/en/latest/index.html#how-it-works)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RtNddeQWm4wx"
   },
   "outputs": [],
   "source": [
    "schema_name = 'view_data_collection_2358_2359'\n",
    "set_search_path(schema_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4Ns5ujbNWbID"
   },
   "source": [
    "If you don't know which schema you need, run the next cell and use the **Filter** button to filter the [selected collections table](https://kingfisher-summarize.readthedocs.io/en/latest/database.html#summaries-selected-collections) to find the schema. You can use the `collection_id` column to filter on the `id` of the collections that you identified in the previous step. Alternatively, you can filter on the `source_id` column. Use the value from the `schema` column to update the previous cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "aVg-KWfvzcfa"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    summaries.selected_collections.*,\n",
    "    source_id\n",
    "FROM\n",
    "    summaries.selected_collections\n",
    "    JOIN collection ON summaries.selected_collections.collection_id = collection.id\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LKjwIzCZoJYw"
   },
   "source": [
    "If you can't find a schema containing the collections that you want to query, you can create a schema using Kingfisher Summarize's [add command](https://kingfisher-summarize.readthedocs.io/en/latest/cli.html#add)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pP9KsYyT1Xnd"
   },
   "source": [
    "## Check for data collection and processing errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KGevotqJ_sN1"
   },
   "source": [
    "### Collection notes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0-ufvqTg_yP9"
   },
   "source": [
    "Generate a list of notes for each collection from the `collection_note` table. \n",
    "Users can add notes when starting a spider or via the cli. Transforms can also add notes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "U0x8mo8Yyj39"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    note\n",
    "FROM\n",
    "    collection_note\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Q-YliQK-_5PP"
   },
   "source": [
    "### Collection file errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fY0i9io4_90a"
   },
   "source": [
    "Generate a summary of errors and warnings from the `collection_file` table.\n",
    "Kingfisher Collect and the `local-load` command report errors when they cannot retrieve a file.\n",
    "Kingfisher Process reports warnings when it needs to modify the contents of a file in order to store it.\n",
    "Presently, the only warning is about the removal of control characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "E5CgVL562bbd"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_error_summary <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    warnings,\n",
    "    errors,\n",
    "    count(*),\n",
    "    (array_agg(filename ORDER BY random()))[1:3] AS example_filenames,\n",
    "    (array_agg(url ORDER BY random()))[1:3] AS example_urls\n",
    "FROM\n",
    "    collection_file\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND (errors IS NOT NULL\n",
    "        OR warnings IS NOT NULL)\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CY2izeiS_JGi"
   },
   "outputs": [],
   "source": [
    "collection_file_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2mXcFyssACKR"
   },
   "source": [
    "Generate a full list of errors and warnings from the `collection_file` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "pHsH4fqP0rnY"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_errors <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    filename,\n",
    "    warnings,\n",
    "    url,\n",
    "    errors\n",
    "FROM\n",
    "    collection_file\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND (errors IS NOT NULL\n",
    "        OR warnings IS NOT NULL);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_THCeewgwE8K"
   },
   "outputs": [],
   "source": [
    "collection_file_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MRDaacPxAIOh"
   },
   "source": [
    "### Collection file item errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lBboFieoAKyY"
   },
   "source": [
    "Generate a summary of errors and warnings from the `collection_file_item` table.\n",
    "Kingfisher Process reports errors when it cannot load a file item."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "vZ77JoaW3-bV"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_item_error_summary <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    cfi.warnings,\n",
    "    cfi.errors,\n",
    "    count(*)\n",
    "FROM\n",
    "    collection_file_item AS cfi\n",
    "    JOIN collection_file AS cf ON cfi.collection_file_id = cf.id\n",
    "WHERE\n",
    "    cf.collection_id IN :collection_ids\n",
    "    AND (cfi.errors IS NOT NULL\n",
    "        OR cfi.warnings IS NOT NULL)\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2N8uzloY_O8x"
   },
   "outputs": [],
   "source": [
    "collection_file_item_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "e6h9arTAAQDX"
   },
   "source": [
    "Generate a full list of errors and warnings from the `collection_file_item` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "TCD9v-mW08xL"
   },
   "outputs": [],
   "source": [
    "%%sql collection_file_item_errors <<\n",
    "\n",
    "SELECT\n",
    "    cfi.number,\n",
    "    cfi.warnings,\n",
    "    cfi.errors\n",
    "FROM\n",
    "    collection_file_item AS cfi\n",
    "    JOIN collection_file AS cf ON cfi.collection_file_id = cf.id\n",
    "WHERE\n",
    "    cf.collection_id IN :collection_ids\n",
    "    AND (cfi.errors IS NOT NULL\n",
    "        OR cfi.warnings IS NOT NULL);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "AwcWIslQ_Tmq"
   },
   "outputs": [],
   "source": [
    "collection_file_item_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jYavV9OEAThs"
   },
   "source": [
    "### Check errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZNTCykfZAVnr"
   },
   "source": [
    "A summary of errors from the `release_check_error` and `record_check_error` tables.\n",
    "CoVE reports errors when it cannot check a release or record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wXHs3ZIXZF4G"
   },
   "outputs": [],
   "source": [
    "%%sql check_error_summary <<\n",
    "\n",
    "WITH errors AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        'release' AS TYPE,\n",
    "        release.id AS release_id,\n",
    "        release_check_error.error\n",
    "    FROM\n",
    "        release_check_error\n",
    "        JOIN RELEASE ON release_check_error.release_id = release.id\n",
    "    WHERE\n",
    "        release.collection_id IN :collection_ids\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        'record' AS TYPE,\n",
    "        record.id AS record_id,\n",
    "        record_check_error.error\n",
    "    FROM\n",
    "        record_check_error\n",
    "        JOIN record ON record_check_error.record_id = record.id\n",
    "    WHERE\n",
    "        record.collection_id IN :collection_ids\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    TYPE,\n",
    "    error,\n",
    "    count(*),\n",
    "    (array_agg(release_id ORDER BY random()))[1:3] AS example_release_ids\n",
    "FROM\n",
    "    errors\n",
    "GROUP BY\n",
    "    1,\n",
    "    2,\n",
    "    3\n",
    "ORDER BY\n",
    "    4 DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "F3RrH_3t_egR"
   },
   "outputs": [],
   "source": [
    "check_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xY94PIGGAXpi"
   },
   "source": [
    "Generate a full list of errors from the `release_check_error` and `record_check_error` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7AtixVvm3Nx-"
   },
   "outputs": [],
   "source": [
    "%%sql check_errors <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS TYPE,\n",
    "    release.id AS release_id,\n",
    "    release_check_error.error\n",
    "FROM\n",
    "    release_check_error\n",
    "    JOIN RELEASE ON release_check_error.release_id = release.id\n",
    "WHERE\n",
    "    release.collection_id IN :collection_ids\n",
    "UNION\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'record' AS TYPE,\n",
    "    record.id AS record_id,\n",
    "    record_check_error.error\n",
    "FROM\n",
    "    record_check_error\n",
    "    JOIN record ON record_check_error.record_id = record.id\n",
    "WHERE\n",
    "    record.collection_id IN :collection_ids;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4UOCStcb_k5O"
   },
   "outputs": [],
   "source": [
    "check_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "73ktdYktAVoO"
   },
   "source": [
    "## Check scope"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "T4Gb2g11BXwt"
   },
   "source": [
    "\n",
    "\n",
    "Use this section to check:\n",
    "\n",
    "* how many releases, records and compiled releases your data contains\n",
    "* what stages of the contracting process your data covers\n",
    "* what date range your data covers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ycCwGfGkA6au"
   },
   "source": [
    "### Release and record counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "s-i5qCmvCsHA"
   },
   "source": [
    "Collections in Kingfisher Process contain either [releases](https://standard.open-contracting.org/latest/en/schema/reference/), [records](https://standard.open-contracting.org/latest/en/schema/records_reference/) or [compiled releases](https://standard.open-contracting.org/latest/en/schema/records_reference/#compiled-release). Kingfisher generates compiled release collections from release or record collections.\n",
    "\n",
    "Use this section to check that the data contains the expected number of releases, records and compiled releases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VXxxMiKcBOCf"
   },
   "source": [
    "Generate a count of releases, records and compiled releases for each collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MVJ6sIWeAZzD"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    id AS collection_id,\n",
    "    cached_releases_count AS releases_count,\n",
    "    cached_records_count AS records_count,\n",
    "    cached_compiled_releases_count AS compiled_releases_count\n",
    "FROM\n",
    "    collection\n",
    "WHERE\n",
    "    id IN :collection_ids\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HD9LE4SLCzPg"
   },
   "source": [
    "### Contracting process stages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "o5hZPAo5yHMm"
   },
   "source": [
    "Use this section to check that the data covers the expected stages of the contracting process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Tfq5haelyH3E"
   },
   "source": [
    "#### Release tags"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cTZiHSkvC3__"
   },
   "source": [
    "[Release tags](https://standard.open-contracting.org/latest/en/schema/codelists/#release-tag) indicate the stage of the contracting process an OCDS release relates to."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EqsaSZj6DkuS"
   },
   "source": [
    "Generate a summary of releases by `tag`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WwgeZMX5Ame4"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    tag,\n",
    "    count(*)\n",
    "FROM\n",
    "    release_summary\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    tag\n",
    "ORDER BY\n",
    "    collection_id;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vTzQeN_kyJ_E"
   },
   "source": [
    "#### Objects per stage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8EmYSnoDnOmT"
   },
   "source": [
    "In OCDS, data is organized into objects, for each stage of a contracting process. Each compiled release has: at most one `Planning` object, at most one `Tender` object, any number of `Award` objects, and any number of `Contract` objects. Each `Contract` object has at most one `Implementation` object. As such, the number of `Award` objects can exceed the number of unique OCIDs, but the number of `Tender` objects can't."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DtRec1tNnPz6"
   },
   "source": [
    "Generate and plot a count of objects per stage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Oe8W_HLCKUsO"
   },
   "outputs": [],
   "source": [
    "%%sql objects_per_stage << \n",
    "\n",
    "SELECT\n",
    "    CASE WHEN paths.path = 'contracts/implementation' THEN\n",
    "        'implementation'\n",
    "    ELSE\n",
    "        paths.path\n",
    "    END AS stage,\n",
    "    CASE WHEN paths.path IN ('planning',\n",
    "        'tender',\n",
    "        'contracts/implementation') THEN\n",
    "        GREATEST (object_property,\n",
    "            0)\n",
    "    ELSE\n",
    "        GREATEST (array_count,\n",
    "            0)\n",
    "    END AS object_count\n",
    "FROM (\n",
    "    SELECT\n",
    "        unnest(ARRAY['planning', 'tender', 'awards', 'contracts', 'contracts/implementation']) AS path) AS paths\n",
    "    LEFT JOIN (\n",
    "        SELECT\n",
    "            *\n",
    "        FROM\n",
    "            field_counts\n",
    "        WHERE\n",
    "            collection_id IN :collection_ids\n",
    "            AND release_type = 'compiled_release'\n",
    "            AND path IN ('planning', 'tender', 'awards', 'contracts', 'contracts/implementation')) AS field_counts USING (path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "mKo6Q4HimvQZ"
   },
   "outputs": [],
   "source": [
    "objects_per_stage_chart = sns.catplot(x=\"stage\", y=\"object_count\", kind=\"bar\", data=objects_per_stage).set_xticklabels(rotation=90)\n",
    "\n",
    "for ax in objects_per_stage_chart.axes.flat:\n",
    "  format_thousands(ax.yaxis)\n",
    "\n",
    "objects_per_stage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9Ui1BMMBFgGu"
   },
   "source": [
    "### Date ranges"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ybx8YrW7hRWC"
   },
   "source": [
    "\n",
    "Use this section to check that the data covers the expected date range."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "V5RSYoTSHRZE"
   },
   "source": [
    "Generate a summary of the earliest and latest `date`, `awards/date` and `contracts/dateSigned`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qy0VRQ3IDwsG"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    'release_date' AS date_type,\n",
    "    min(date) AS min,\n",
    "    max(date) AS max\n",
    "FROM\n",
    "    release_summary\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    date_type\n",
    "UNION ALL\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    'award_date' AS date_type,\n",
    "    min(first_award_date) AS min,\n",
    "    max(last_award_date) AS max\n",
    "FROM\n",
    "    release_summary\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    date_type\n",
    "UNION ALL\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    'contract_datesigned' AS date_type,\n",
    "    min(first_contract_datesigned) AS min,\n",
    "    max(last_contract_datesigned) AS max\n",
    "FROM\n",
    "    release_summary\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    date_type;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "daHiVoJkexWi"
   },
   "source": [
    "### Release date distribution\n",
    "\n",
    "Use this section to check that releases are distributed as expected."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DynLHB_12cZ3"
   },
   "source": [
    "Plot the count of releases per month:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0xTqCh28LOQj"
   },
   "outputs": [],
   "source": [
    "%%sql release_dates <<\n",
    "\n",
    "SELECT\n",
    "    collection_id::text,\n",
    "    release_type,\n",
    "    date,\n",
    "    count(*) AS release_count\n",
    "FROM\n",
    "    release_summary rs\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    date\n",
    "ORDER BY\n",
    "    date ASC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "OM2aiiJzAmzM"
   },
   "outputs": [],
   "source": [
    "# Resample by month\n",
    "release_dates = release_dates.set_index('date')\n",
    "release_dates = release_dates.groupby(['collection_id', 'release_type']).resample(\"M\").sum()\n",
    "\n",
    "fig, ax = plt.subplots(figsize = [15,5])\n",
    "sns.lineplot(data = release_dates, x='date', y='release_count', hue = 'collection_id', style = 'release_type')\n",
    "\n",
    "format_thousands(ax.yaxis)\n",
    "sns.despine()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ugoDOjsdkLXN"
   },
   "source": [
    "### Extensions "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9cxNPLXN8wwc"
   },
   "source": [
    "Use this section to check which extensions the data uses."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "U7iYgIdW8z1c"
   },
   "source": [
    "Generate a list of extensions declared in the package metadata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qFbWPY5Eq7fv"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    jsonb_array_elements(package_data -> 'extensions') AS EXTENSION,\n",
    "    count(*) AS count\n",
    "FROM\n",
    "    release_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND package_data IS NOT NULL\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    EXTENSION\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    count DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cE548sDyZyG5"
   },
   "source": [
    "## Check for structure and format errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gtzHL_Sul8i3"
   },
   "source": [
    "Kingfisher Process checks data against the OCDS schema using [CoVE](https://github.com/OpenDataServices/cove). For release collections, Kingfisher Process stores check results in the `release_check` table. For record collections, Kingfisher Process stores check results in the `record_check` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ydqNZpiWWJVT"
   },
   "source": [
    "### Confirm that checks are complete"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cyhI3LAhXZ44"
   },
   "source": [
    "By default, Kingfisher Process checks all data, therefore there is often a long queue of collections to be checked. Use the following query to confirm that checks are complete for your collection(s).\n",
    "\n",
    "If checks for your collection(s) have not started yet, you can use the [`check collection` command](https://kingfisher-process.readthedocs.io/en/latest/cli/check-collection.html) to start the checks manually.\n",
    "\n",
    "If checks are in progress, you should wait for the checks to finish before running the queries in this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "nKEuouhtWTiP"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS collection_type,\n",
    "    CASE WHEN count(release.id) = count(release_check.id) THEN\n",
    "        'complete'\n",
    "    WHEN count(release_check.id) = 0 THEN\n",
    "        'not_started'\n",
    "    ELSE\n",
    "        'in_progress'\n",
    "    END AS check_status,\n",
    "    count(release_check.id)::text || '/' || count(release.id)::text AS check_progress\n",
    "FROM\n",
    "    release_check\n",
    "    RIGHT JOIN release ON release_check.release_id = release.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "GROUP BY\n",
    "    collection_id\n",
    "UNION\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'record' AS collection_type,\n",
    "    CASE WHEN count(record.id) = count(record_check.id) THEN\n",
    "        'complete'\n",
    "    WHEN count(record_check.id) = 0 THEN\n",
    "        'not_started'\n",
    "    ELSE\n",
    "        'in_progress'\n",
    "    END AS check_status,\n",
    "    count(record_check.id)::text || '/' || count(record.id)::text AS check_progress\n",
    "FROM\n",
    "    record_check\n",
    "    RIGHT JOIN record ON record_check.record_id = record.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "GROUP BY\n",
    "    collection_id;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uTeVs0jnlgHu"
   },
   "source": [
    "### Error summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XGxn_SwCllq4"
   },
   "source": [
    "Generate a summary of errors from the `release_check` and `record_check` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ncEmto9mc_wI"
   },
   "outputs": [],
   "source": [
    "%%sql structure_and_format_error_summary <<\n",
    "\n",
    "WITH errors AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        errors ->> 'type' AS error_type,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value,\n",
    "    row_number() OVER (PARTITION BY collection_id,\n",
    "        errors ->> 'type',\n",
    "    LEFT (errors ->> 'description',\n",
    "    49000)) AS rownum\n",
    "FROM\n",
    "    release_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN RELEASE r ON rc.release_id = r.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "UNION ALL\n",
    "SELECT\n",
    "    collection_id,\n",
    "    errors ->> 'type' AS error_type,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value,\n",
    "    row_number() OVER (PARTITION BY collection_id,\n",
    "        errors ->> 'type',\n",
    "    LEFT (errors ->> 'description',\n",
    "    49000)) AS rownum\n",
    "FROM\n",
    "    record_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN record r ON rc.record_id = r.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "),\n",
    "examples AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        error_type,\n",
    "        error,\n",
    "        array_agg(ocid) AS example_ocids,\n",
    "        array_agg(value) AS example_values\n",
    "    FROM\n",
    "        errors\n",
    "    WHERE\n",
    "        rownum <= 3\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        error_type,\n",
    "        error\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    error_type,\n",
    "    error,\n",
    "    count(*) AS count,\n",
    "    example_ocids,\n",
    "    example_values\n",
    "FROM\n",
    "    errors\n",
    "    JOIN examples USING (collection_id, error_type, error)\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    error_type,\n",
    "    error,\n",
    "    example_ocids,\n",
    "    example_values;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zeOz07-jedqT"
   },
   "outputs": [],
   "source": [
    "structure_and_format_error_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZfdR-wgKpFmT"
   },
   "source": [
    "### Error details"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tCw4uaq3mwVT"
   },
   "source": [
    "Generate a full list of errors from the `release_check` and `record_check` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "QKRzlq22m0S-"
   },
   "outputs": [],
   "source": [
    "%%sql structure_and_format_errors <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'release' AS collection_type,\n",
    "    errors ->> 'type' AS error_type,\n",
    "    errors ->> 'field' AS field,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value\n",
    "FROM\n",
    "    release_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN RELEASE r ON rc.release_id = r.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "UNION ALL\n",
    "SELECT\n",
    "    collection_id,\n",
    "    'record' AS collection_type,\n",
    "    errors ->> 'type' AS error_type,\n",
    "    errors ->> 'field' AS field,\n",
    "    LEFT (errors ->> 'description',\n",
    "        49000) AS error,\n",
    "    ocid,\n",
    "    errors ->> 'value' AS value\n",
    "FROM\n",
    "    record_check rc\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
    "    JOIN record r ON rc.record_id = r.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "q3cKo_dnvdvQ"
   },
   "outputs": [],
   "source": [
    "structure_and_format_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7fpo23ErO_lL"
   },
   "source": [
    "## Check data quality"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dteWESl_lSb-"
   },
   "source": [
    "### Local extensions and additional fields and codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "B_nDBOyG2279"
   },
   "source": [
    "[Conforming publications](https://standard.open-contracting.org/latest/en/schema/conformance_and_extensions/#publication-conformance) must not use terms from outside the OCDS schema where the OCDS schema's terms would suffice.\n",
    "\n",
    "Use this section to identify fields in local extensions and additional fields and codes that should be mapped to fields and codes in the OCDS schema and extensions.\n",
    "\n",
    "You can use the following resources to find fields and codes with similar semantics:\n",
    "\n",
    "* [OCDS Schema and Codelist Reference](https://standard.open-contracting.org/latest/en/schema/), for fields in the core OCDS schema.\n",
    "* [OCDS Extensions Field and Code Search](https://open-contracting.github.io/editor-tools/), for fields and codes in OCDS extensions.\n",
    "* [Github Issue Tracker](https://github.com/open-contracting/standard/issues), for discussions about adding new fields and codes.\n",
    "\n",
    "Check that field and code names [conform to the style guide](https://ocds-standard-development-handbook.readthedocs.io/en/latest/meta/schema_style_guide.html#field-and-code-names) and report any issues to the publisher.\n",
    "\n",
    "If you cannot find a suitable mapping for an additional field or code, open a Github issue to describe the sematics of the field or code and to discuss how to model it. Report any issues to the publisher.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4oFJe2cw9HlA"
   },
   "source": [
    "#### Local extensions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "72PpTzAP9NSI"
   },
   "source": [
    "For each field and code in extensions authored by the publisher, in addition to the above checks, consider whether to [review the extension in detail](https://docs.google.com/document/d/1bRhVVkuTPXw6acE2opKD-Yj80y0lvAsfAqDjG632nNY/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LzOXXZob9gA2"
   },
   "source": [
    "Generate a list of extensions declared in the package metadata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Yul28_sU9gA2"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    jsonb_array_elements(package_data -> 'extensions') AS EXTENSION,\n",
    "    count(*) AS count\n",
    "FROM\n",
    "    release_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND package_data IS NOT NULL\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    EXTENSION\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    count DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CeabYppc20t4"
   },
   "source": [
    "#### Additional fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "52thQMhSsTe8"
   },
   "source": [
    "Note that the DRT also reports additional fields in the following scenarios.\n",
    "\n",
    "* Fields from undeclared extensions\n",
    "* Fields with language variations, e.g. `title_es`. You do not need to report language variations to the publisher, but you should check that the field [conforms to the rules for language variations](https://standard.open-contracting.org/latest/en/schema/reference/#language).\n",
    "* OCDS 1.0 data with fields from extensions. You should report the fields to the publisher and recommend that they upgrade to OCDS 1.1.\n",
    "* Data with no package, or where a version is not declared, which is checked against both the 1.0 and 1.1 schemas. You should filter the `schema_version` to get additional fields against the latest version of the schema and report them to the publisher.\n",
    "* Fields from extensions that patch multiple schemas ([issue](https://github.com/OpenDataServices/cove/issues/1132)). If additional fields are reported in the package schema, you should check whether any extensions have multiple schemas before reporting them to the publisher."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rOaRxbxgpzKw"
   },
   "source": [
    "Generate a list of additional fields reported by the DRT.\n",
    "\n",
    "By default, results are reported for a sample of 10% of releases. For small collections, you can set `sample_size` to `1` to return results for the full collection. For large collections, you can reduce the sample size."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KQlVv7YZjbED"
   },
   "outputs": [],
   "source": [
    "sample_size = 0.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RbJJ5k0SmOTB"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH check_results AS (\n",
    "    SELECT\n",
    "        *,\n",
    "        CASE WHEN (release_type IN ('record',\n",
    "                'embedded_release')) THEN\n",
    "            record_check\n",
    "        ELSE\n",
    "            release_check\n",
    "        END AS results\n",
    "    FROM\n",
    "        release_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type <> 'compiled_release'\n",
    "        AND random() < :sample_size\n",
    "),\n",
    "counts AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        additional_fields ->> 'path' AS path,\n",
    "        additional_fields ->> 'field_name' AS field,\n",
    "        sum(cast(additional_fields ->> 'count' AS int)) AS count,\n",
    "        results -> 'schema_url' AS schema_version\n",
    "    FROM\n",
    "        check_results\n",
    "    CROSS JOIN jsonb_array_elements(results -> 'all_additional_fields') AS additional_fields\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    schema_version,\n",
    "    field,\n",
    "    path\n",
    "ORDER BY\n",
    "    schema_version,\n",
    "    path,\n",
    "    count DESC\n",
    "),\n",
    "examples AS (\n",
    "    SELECT DISTINCT ON (collection_id,\n",
    "        release_type,\n",
    "        results -> 'schema_url',\n",
    "        additional_fields ->> 'path',\n",
    "        additional_fields ->> 'field_name')\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        results -> 'schema_url' AS schema_version,\n",
    "        additional_fields ->> 'path' AS path,\n",
    "        additional_fields ->> 'field_name' AS field,\n",
    "        additional_fields ->> 'examples' AS examples\n",
    "    FROM\n",
    "        check_results\n",
    "    CROSS JOIN jsonb_array_elements(results -> 'all_additional_fields') AS additional_fields\n",
    "WHERE\n",
    "    additional_fields ->> 'examples' <> '[]'\n",
    "    AND additional_fields ->> 'examples' IS NOT NULL\n",
    ")\n",
    "SELECT\n",
    "    counts.collection_id,\n",
    "    counts.release_type,\n",
    "    counts.schema_version,\n",
    "    counts.path path,\n",
    "    counts.field field,\n",
    "    count,\n",
    "    examples examples\n",
    "FROM\n",
    "    counts\n",
    "    LEFT JOIN examples USING (collection_id, release_type, schema_version, path, field)\n",
    "ORDER BY\n",
    "    counts.schema_version,\n",
    "    path,\n",
    "    field;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "v_6_-h72u3_Q"
   },
   "source": [
    "##### **Additional field examples**\n",
    "\n",
    "Use the query in the following cell to generate a release package containing an example release for each additional field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "oMBWTfjljvdc"
   },
   "outputs": [],
   "source": [
    "%%sql additional_field_examples <<\n",
    "\n",
    "WITH additional_field_releases AS (\n",
    "    SELECT\n",
    "        ocid AS ocid,\n",
    "        release.release_id AS release_id,\n",
    "        data_id AS data_id,\n",
    "        additional_fields ->> 'path' AS path,\n",
    "        additional_fields ->> 'field_name' AS field\n",
    "    FROM\n",
    "        release_check\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'all_additional_fields') AS additional_fields\n",
    "    JOIN release ON release_check.release_id = release.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND random() < :sample_size\n",
    "),\n",
    "additional_fields AS (\n",
    "    SELECT DISTINCT\n",
    "        path,\n",
    "        field\n",
    "    FROM\n",
    "        additional_field_releases\n",
    "),\n",
    "examples AS (\n",
    "    SELECT DISTINCT ON (additional_fields.path,\n",
    "        additional_fields.field)\n",
    "        additional_fields.path,\n",
    "        additional_fields.field,\n",
    "        ocid,\n",
    "        release_id,\n",
    "        data_id,\n",
    "        data\n",
    "    FROM\n",
    "        additional_fields\n",
    "        INNER JOIN additional_field_releases ON additional_fields.path = additional_field_releases.path\n",
    "            AND additional_fields.field = additional_field_releases.field\n",
    "        JOIN data ON data.id = data_id\n",
    "    ORDER BY\n",
    "        additional_fields.path,\n",
    "        additional_fields.field\n",
    ")\n",
    "SELECT\n",
    "    jsonb_build_object('releases', jsonb_agg(data)) release_package\n",
    "FROM\n",
    "    examples\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "YPHZX987mOG-"
   },
   "outputs": [],
   "source": [
    "render_json(additional_field_examples['release_package'][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tMVFNCDrmb0l"
   },
   "source": [
    "#### Additional codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HIiCZQni2kbA"
   },
   "source": [
    "List additional open codelist values reported by the DRT:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "V8Gx2WAOqERy"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH check_results AS (\n",
    "    SELECT\n",
    "        *,\n",
    "        CASE WHEN (release_type IN ('record',\n",
    "                'embedded_release')) THEN\n",
    "            record_check\n",
    "        ELSE\n",
    "            release_check\n",
    "        END AS results\n",
    "    FROM\n",
    "        release_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type <> 'compiled_release'\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    additional_open_codelist_values.value -> 'codelist' codelist,\n",
    "    codes.value code,\n",
    "    count(*) occurrences\n",
    "FROM\n",
    "    check_results\n",
    "    CROSS JOIN jsonb_each(results -> 'additional_open_codelist_values') additional_open_codelist_values\n",
    "    CROSS JOIN jsonb_array_elements(value -> 'values') codes\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    codelist,\n",
    "    code\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    codelist,\n",
    "    count(*) DESC\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0jMaeqCJPA5J"
   },
   "source": [
    "### Conformance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MiFFM6zOlGTp"
   },
   "source": [
    "#### Deprecated fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kzUGDNQqrlKq"
   },
   "source": [
    "Before a field or codelist is removed from the standard, it is first marked as [deprecated](https://standard.open-contracting.org/latest/en/governance/deprecation/#deprecation).\n",
    "\n",
    "Use this section to check for deprecated fields."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fZisRK9kr6MZ"
   },
   "source": [
    "Generate a list of deprecated fields:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Oc0ex2vNlIOU"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT DISTINCT ON (collection_id, path, deprecated_version, explanation)\n",
    "    collection_id,\n",
    "    regexp_replace(trim('\"' FROM paths::text), '\\/[0-9]+', '', 'g') || '/' || (deprecated_fields ->> 'field') AS path,\n",
    "    deprecated_fields -> 'explanation' -> 0 AS deprecated_version,\n",
    "    deprecated_fields -> 'explanation' -> 1 AS explanation,\n",
    "    ocid AS example_ocid\n",
    "FROM\n",
    "    release_check\n",
    "    CROSS JOIN jsonb_array_elements(cove_output -> 'deprecated_fields') AS deprecated_fields\n",
    "    CROSS JOIN jsonb_array_elements(deprecated_fields -> 'paths') AS paths\n",
    "    JOIN release ON release_check.release_id = release.id\n",
    "WHERE\n",
    "    collection_id IN :collection_ids;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lUxc-EZLkSPO"
   },
   "source": [
    "#### Metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OTUvn1oKPCnS"
   },
   "source": [
    "##### **Package metadata**\n",
    "\n",
    "OCDS data must be published within either a [release package](https://standard.open-contracting.org/latest/en/schema/reference/#package-metadata) or a [record package](https://standard.open-contracting.org/latest/en/schema/records_reference/#package-metadata).\n",
    "\n",
    "Use this section to check that the values in the package metadata conform to the descriptions in the schema.\n",
    "\n",
    "Look out for the following issues and report them to the publisher:\n",
    "\n",
    "* Placeholder values\n",
    "* Empty strings and objects\n",
    "* Discrepancies in the package metadata between different releases "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8TqV2NVDQ7t4"
   },
   "source": [
    "Generate a summary of the package metadata values used in each collection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "FpZvLuscPAf7"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    package_data -> 'version' AS ocds_version,\n",
    "    package_data -> 'publisher' -> 'name' AS publisher_name,\n",
    "    package_data -> 'publisher' -> 'name' -> 'scheme' AS publisher_scheme,\n",
    "    package_data -> 'publisher' -> 'name' -> 'uid' AS publisher_uid,\n",
    "    package_data -> 'publisher' -> 'name' -> 'uri' AS publisher_uri,\n",
    "    package_data -> 'license' AS license,\n",
    "    package_data -> 'publicationPolicy' AS publicationPolicy,\n",
    "    count(*)\n",
    "FROM\n",
    "    release_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND release_type != 'compiled_release'\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    publisher_name,\n",
    "    publisher_scheme,\n",
    "    publisher_uid,\n",
    "    publisher_uri,\n",
    "    license,\n",
    "    publicationPolicy,\n",
    "    ocds_version;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BqfowfAfT6Z_"
   },
   "source": [
    "##### **Release tags**\n",
    "\n",
    "> Releases must be tagged with one or more values from the [release tag codelist](https://standard.open-contracting.org/latest/en/schema/codelists/#release-tag).Tags may be used to filter releases and to understand the kind of information that a release might contain.\n",
    "\n",
    "Use this section to check that release tags reflect the data included in each release.\n",
    "\n",
    "Read the descriptions in the codelist to understand which sections can be provided for each tag.\n",
    "\n",
    "Remember that releases can repeat information from previous releases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bcyBJvRTUZX5"
   },
   "source": [
    "Generate a summary of the sections published for each release tag.\n",
    "\n",
    "Note that this check only counts whether the section exists, not whether it contains any fields or objects, so the results may include empty objects (e.g. `planning`) and arrays (e.g. `awards`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3f-D5_r5ThY_"
   },
   "outputs": [],
   "source": [
    "%%sql release_tag_section_summary <<\n",
    "\n",
    "WITH IMPLEMENTATION AS (\n",
    "    SELECT\n",
    "        cs.collection_id,\n",
    "        cs.release_type,\n",
    "        tag,\n",
    "        count(contract -> 'implementation') AS IMPLEMENTATION\n",
    "    FROM\n",
    "        contracts_summary cs\n",
    "    LEFT JOIN release_summary USING (id)\n",
    "GROUP BY\n",
    "    cs.collection_id, cs.release_type, tag\n",
    "),\n",
    "sections AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        tag,\n",
    "        count(*) AS release_count,\n",
    "        count(RELEASE -> 'planning') AS planning,\n",
    "        count(RELEASE -> 'tender') AS tender,\n",
    "        count(RELEASE -> 'awards') AS award,\n",
    "        count(RELEASE -> 'contracts') AS contract\n",
    "    FROM\n",
    "        release_summary\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        tag\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    sections.tag,\n",
    "    release_count,\n",
    "    planning,\n",
    "    tender,\n",
    "    award,\n",
    "    contract,\n",
    "    IMPLEMENTATION\n",
    "FROM\n",
    "    sections\n",
    "    LEFT JOIN IMPLEMENTATION USING (collection_id, release_type, tag);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "M-difjEFBSmB"
   },
   "outputs": [],
   "source": [
    "release_tag_section_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "abni9r6s3E_Z"
   },
   "source": [
    "##### **Release date**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HM9mez4h3Jy8"
   },
   "source": [
    "Use this section to check that all releases do not share the same date."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SzGpx01O41c3"
   },
   "source": [
    "For each collection and release type, generate a [frequency table](https://en.wikipedia.org/wiki/Frequency_distribution) for release dates and report the top 5 most frequent dates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "U-Sw5-X741c7"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH date_frequency AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        date,\n",
    "        count(*) AS release_count,\n",
    "        row_number() OVER (PARTITION BY collection_id,\n",
    "            release_type ORDER BY count(*) DESC) AS row_number\n",
    "    FROM\n",
    "        release_summary\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        date\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    date,\n",
    "    release_count\n",
    "FROM\n",
    "    date_frequency\n",
    "WHERE\n",
    "    row_number <= 5\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    release_count DESC;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ik2hkA8yghRT"
   },
   "source": [
    "##### **Language**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hE6aXnygjhuk"
   },
   "source": [
    "> The default language of the data using either two-letter [ISO639-1](https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes), or extended [BCP47 language tags](http://www.w3.org/International/articles/language-tags/). The use of lowercase two-letter codes from [ISO639-1](https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes) is recommended.\n",
    "\n",
    "Use this section to check that the code declared in `language` reflects the language used in free-text fields in the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6uieOUzgkFGF"
   },
   "source": [
    "Generate a list of language codes used in the data, with an example release for each language."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "HRweYx7nUvRE"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT DISTINCT ON (collection_id, release_type,\n",
    "    LANGUAGE)\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    LANGUAGE,\n",
    "    release AS example_release\n",
    "FROM\n",
    "    release_summary\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    LANGUAGE;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "74oWIWefkd7A"
   },
   "source": [
    "#### Change history"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Z41ym0kllAu6"
   },
   "source": [
    "OCDS supports the publication of a change history, using [releases and records](https://standard.open-contracting.org/latest/en/getting_started/releases_and_records/#releases-and-records).\n",
    "\n",
    "Fully implemented, releases and records can be used to publish the following for each contracting process:\n",
    "\n",
    "* Multiple OCDS releases, one for each change or update to the contracting process\n",
    "* A single OCDS record, containing:\n",
    "  * `releases` - an index of releases for the contracting process\n",
    "  * optionally, a `compiledRelease` - the latest version of the data about the contracting process\n",
    "  * optionally, a `versionedRelease` - a history of changes for each field\n",
    "\n",
    "However, many publishers use the ['easy releases'](https://standard.open-contracting.org/latest/en/guidance/build/easy_releases/) approach: publish a single release per contracting process with the latest version of the data about the contracting process.\n",
    "\n",
    "Use this section to understand the approach used by the publisher."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d1Igbq4gm01W"
   },
   "source": [
    "##### **Multiple releases per contracting process**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fK7PwwQPnjv6"
   },
   "source": [
    "Use this section to:\n",
    "\n",
    "* check if there are multiple releases per contracting process\n",
    "* check the distribution of releases per contracting process\n",
    "* examine examples of contracting processes with multiple releases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aAPM7PjIoKjo"
   },
   "source": [
    "Generate statistics on the minimum, maximum, average and standard deviation of releases per contracting process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lGQMqdTjmmXP"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH release_counts AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid,\n",
    "        count(*) AS release_count\n",
    "    FROM\n",
    "        release_summary rs\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    min(release_count) AS min_releases_per_ocid,\n",
    "    max(release_count) AS max_releases_per_ocid,\n",
    "    round(avg(release_count), 2) AS avg_releases_per_ocid,\n",
    "    round(stddev(release_count), 2) AS sd_releases_per_ocid\n",
    "FROM\n",
    "    release_counts\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uX8ddZoGnqoJ"
   },
   "source": [
    "Generate a summary of the release count per contracting process:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zojrnAxlke2p"
   },
   "outputs": [],
   "source": [
    "%%sql release_count_summary <<\n",
    "\n",
    "WITH release_counts AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid,\n",
    "        count(*) AS release_count\n",
    "    FROM\n",
    "        release_summary rs\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('release',\n",
    "            'embedded_release')\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    release_count,\n",
    "    count(*) AS contracting_processes\n",
    "FROM\n",
    "    release_counts\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    release_count;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8273QQnaecKT"
   },
   "outputs": [],
   "source": [
    "release_count_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vNrKZdsv21Sc"
   },
   "source": [
    "Plot the distribution of releases per contracting process:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "sJ40ZMqF7DnI"
   },
   "outputs": [],
   "source": [
    "%%sql release_counts <<\n",
    "\n",
    "WITH release_counts AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid,\n",
    "        count(*) AS release_count\n",
    "    FROM\n",
    "        release_summary rs\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('release',\n",
    "            'embedded_release')\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    release_count,\n",
    "    count(*) AS ocid_count\n",
    "FROM\n",
    "    release_counts\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    release_count;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yeBOaDsO6uDS"
   },
   "outputs": [],
   "source": [
    "release_count_chart = sns.catplot(x=\"release_count\", y=\"ocid_count\", kind=\"bar\", col=\"collection_id\", hue=\"release_type\", data=release_counts).set_xticklabels(rotation=90)\n",
    "\n",
    "for ax in release_count_chart.axes.flat:\n",
    "  format_thousands(ax.yaxis)\n",
    "\n",
    "plt.show(release_count_chart)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ww3sXJfXY9Lf"
   },
   "source": [
    "Generate a release package containing the top 5 contracting processes with the most releases. \n",
    "\n",
    "Specific things to check include:\n",
    "\n",
    "* Does the `date` field differ between releases?\n",
    "* Does the `tag` field differ between releases?\n",
    "\n",
    "Also check for differences in which fields are provided for each release and for differences in the values of fields.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RTJ8BwXBSrrw"
   },
   "outputs": [],
   "source": [
    "%%sql multiple_release_examples <<\n",
    "\n",
    "WITH ranked_ocids AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid,\n",
    "        count(*),\n",
    "        row_number() OVER (PARTITION BY collection_id,\n",
    "            release_type ORDER BY count(*) DESC) AS row_number\n",
    "    FROM\n",
    "        release_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('release',\n",
    "            'embedded_release')\n",
    "    GROUP BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid\n",
    ")\n",
    "SELECT\n",
    "    jsonb_build_object('releases', jsonb_agg(release)) AS release_package\n",
    "FROM\n",
    "    release_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND release_type IN ('release', 'embedded_release')\n",
    "    AND ocid IN (\n",
    "        SELECT\n",
    "            ocid\n",
    "        FROM\n",
    "            ranked_ocids\n",
    "        WHERE\n",
    "            row_number <= 5);\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "G7zsQ7Jvofge"
   },
   "outputs": [],
   "source": [
    "render_json(multiple_release_examples['release_package'][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "T_j8x4pnoemE"
   },
   "source": [
    "To ease review, uncomment the following cell to convert the release package to a Google Sheet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "YyL6ruuNRTA0"
   },
   "outputs": [],
   "source": [
    "# save_dataframe_to_spreadsheet(multiple_release_examples, f'{spreadsheet_name}_multiple_releases')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XcooMawPg41a"
   },
   "source": [
    "##### **Release ID**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HP0YAYtmg88t"
   },
   "source": [
    "The release identifer must be updated when the information about a contracting process changes.\n",
    "\n",
    "A common error is to set the release ID to the same value as the `ocid`, or to set it to a subset of the `ocid`, and to neglect to update it.\n",
    "\n",
    "Use this section to check that the release ID differs from the `ocid`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jI2eXvD-hqMg"
   },
   "source": [
    "Generate a list of releases where `id` and `ocid` share the same value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7IoW8sC2hkLc"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    ocid,\n",
    "    release_id\n",
    "FROM\n",
    "    release_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND (ocid = release_id\n",
    "        OR ocid ILIKE '%%' || release_id || '%%')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5xnOyYpj_njZ"
   },
   "source": [
    "#### Overfill\n",
    "\n",
    "In a whole dataset, we expect there to be some differences between the values, items and dates listed in the tender, award and contract sections of OCDS.\n",
    "\n",
    "In an effort to publish as many field as possible, publishers sometimes ignore semantics and map one field from their data source to several fields in OCDS, known as overfill.\n",
    "\n",
    "Use this section to identify instances of overfill."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "G2DY3A0G3ZDl"
   },
   "source": [
    "##### **Awards and contracts**\n",
    "\n",
    "Use this section to check if there are any differences the following fields in the award and contract sections:\n",
    "\n",
    "* `awards/date` and `contracts/dateSigned`\n",
    "* `awards/value` and `contracts/value`\n",
    "* `awards/items` and `contracts/items`\n",
    "* `awards/contractPeriod` and `contracts/period`\n",
    "* `award/documents` and `contracts/documents`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_QinRGvas2lN"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    contracts_summary.collection_id,\n",
    "    contracts_summary.release_type,\n",
    "    CASE WHEN awards_summary.date = contracts_summary.dateSigned THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS date_match,\n",
    "    CASE WHEN (awards_summary.value_amount = contracts_summary.value_amount)\n",
    "        AND (awards_summary.value_currency = contracts_summary.value_currency) THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS value_match,\n",
    "    CASE WHEN (awards_summary.contractperiod_startDate = contracts_summary.period_startDate)\n",
    "        AND (awards_summary.contractperiod_endDate = contracts_summary.period_startDate) THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS period_match,\n",
    "    CASE WHEN awards_summary.award ->> 'documents' = contracts_summary.contract ->> 'documents' THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS documents_match,\n",
    "    count(contracts_summary.id) AS contract_count\n",
    "FROM\n",
    "    contracts_summary\n",
    "    JOIN awards_summary ON awards_summary.id = contracts_summary.id\n",
    "        AND awards_summary.award_id = contracts_summary.awardid\n",
    "WHERE\n",
    "    contracts_summary.collection_id IN :collection_ids\n",
    "    AND contracts_summary.release_type IN ('record', 'compiled_release')\n",
    "GROUP BY\n",
    "    contracts_summary.collection_id,\n",
    "    contracts_summary.release_type,\n",
    "    date_match,\n",
    "    value_match,\n",
    "    period_match,\n",
    "    documents_match\n",
    "ORDER BY\n",
    "    contracts_summary.collection_id,\n",
    "    contracts_summary.release_type,\n",
    "    contract_count DESC\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YwWvrmnHdh5q"
   },
   "source": [
    "##### **Items**\n",
    "\n",
    "Items are attached to the tender, award and contract sections of an OCDS release, so that users can see if there were any changes to the items being procured during the contracting process.\n",
    "\n",
    "Use this section to check for differences between the items attached to the tender, award and contract sections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "nT9U0L1weBr4"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    tender_summary.collection_id,\n",
    "    tender_summary.release_type,\n",
    "    CASE WHEN contracts_summary.contract -> 'items' = awards_summary.award -> 'items' THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS award_contract_match,\n",
    "    count(contracts_summary.id) AS contracts_count,\n",
    "    CASE WHEN awards_summary.award -> 'items' = tender_summary.tender -> 'items' THEN\n",
    "        TRUE\n",
    "    ELSE\n",
    "        FALSE\n",
    "    END AS tender_award_match,\n",
    "    count(awards_summary.id) AS awards_count\n",
    "FROM\n",
    "    tender_summary\n",
    "    JOIN awards_summary USING (id)\n",
    "    LEFT JOIN contracts_summary ON awards_summary.id = contracts_summary.id\n",
    "        AND awards_summary.award_id = contracts_summary.awardid\n",
    "WHERE\n",
    "    tender_summary.collection_id IN :collection_ids\n",
    "    AND tender_summary.release_type IN ('record', 'compiled_release')\n",
    "GROUP BY\n",
    "    tender_summary.collection_id,\n",
    "    tender_summary.release_type,\n",
    "    award_contract_match,\n",
    "    tender_award_match;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pDhw9NXt1Qgy"
   },
   "source": [
    "#### Placeholder values\n",
    "\n",
    "Use this section to check for placeholder values.\n",
    "\n",
    "Manually review the example release to identify placeholder values, e.g. 'n/a', 'test', '1970-01-01T00:00:00Z' etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kxqdWzYw0tSo"
   },
   "source": [
    "Get an example release:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "jMjS-9MS1it_"
   },
   "outputs": [],
   "source": [
    "%%sql example_releases <<\n",
    "\n",
    "WITH examples AS (\n",
    "    SELECT DISTINCT ON (collection_id,\n",
    "        release_type)\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        release\n",
    "    FROM\n",
    "        release_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('release',\n",
    "            'embedded_release')\n",
    "    ORDER BY\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        random())\n",
    "SELECT\n",
    "    jsonb_build_object('releases', jsonb_agg(release)) release_package\n",
    "FROM\n",
    "    examples\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "i5cZhi-L5ZCu"
   },
   "outputs": [],
   "source": [
    "render_json(example_releases['release_package'][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PwP4LyhV2F5o"
   },
   "source": [
    "### Coherence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Cg37FRR_LvsO"
   },
   "source": [
    "#### Organization identifiers "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mvKnrvB_RS4T"
   },
   "source": [
    "Publishers should collect and publish [organization identifiers](https://standard.open-contracting.org/latest/en/schema/identifiers/#organization-ids).\n",
    "\n",
    "Use this section to check for invalid organization identifiers or incorrect organization identifiers.\n",
    "\n",
    "For each organization identifier:\n",
    "\n",
    "1. Look up the `scheme` in [org-id.guide](http://org-id.guide/) and follow the guidance to look up the organization identifiers in the register.\n",
    "1. Check that the identifier exists in the register."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0KDF0G43iprA"
   },
   "source": [
    "Get a list of all organization identifiers and select a random sample of 3 identifiers from each organization identifier scheme:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "XaF65iyq1YMm"
   },
   "outputs": [],
   "source": [
    "%%sql organization_identifiers <<\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    party ->> 'name' AS name,\n",
    "    party -> 'identifier' ->> 'legalName' AS legalName,\n",
    "    roles,\n",
    "    party -> 'identifier' ->> 'scheme' AS scheme,\n",
    "    party -> 'identifier' ->> 'id' AS id,\n",
    "    ocid\n",
    "FROM\n",
    "    parties_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "OGmecQ5d2d8e"
   },
   "outputs": [],
   "source": [
    "organization_identifiers.groupby(['collection_id', 'release_type', 'scheme']).sample(n=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8syUMlFlme6X"
   },
   "source": [
    "#### Foreign companies \n",
    "\n",
    "Publishers sometimes erroneously populate `.scheme` for foreign-registered companiues with the code for a domestic organization register.\n",
    "\n",
    "Use this section to check that the correct organization identifier scheme is provided for foreign-registered companies.\n",
    "\n",
    "Set the `country` variable to the name of the country for the publisher before running the query.\n",
    "\n",
    "For each organization identifier:\n",
    "\n",
    "1. Look up the `scheme` in [org-id.guide](http://org-id.guide/) and follow the guidance to look up the organization identifiers in the register.\n",
    "1. Check that the identifier exists in the register."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "rdfGKYmN3VbH"
   },
   "outputs": [],
   "source": [
    "country = 'Paraguay'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "iMPnnFMvmnNY"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    name,\n",
    "    scheme,\n",
    "    id,\n",
    "    legalName,\n",
    "    country,\n",
    "    roles\n",
    "FROM ( SELECT DISTINCT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        party ->> 'name' AS name,\n",
    "        party -> 'identifier' ->> 'scheme' scheme,\n",
    "        party -> 'identifier' ->> 'id' id,\n",
    "        party -> 'identifier' ->> 'legalName' legalName,\n",
    "        party -> 'address' ->> 'country' AS country,\n",
    "        roles,\n",
    "        rank() OVER (PARTITION BY collection_id, release_id, party -> 'identifier' ->> 'scheme' ORDER BY random())\n",
    "    FROM parties_summary\n",
    "WHERE collection_id IN :collection_ids\n",
    "    AND release_type IN ('record', 'compiled_release')\n",
    "AND party -> 'address' ->> 'country' NOT ILIKE :country) AS identifiers\n",
    "WHERE\n",
    "    rank <= 3\n",
    "ORDER BY\n",
    "    scheme;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jc9qxg2e7M82"
   },
   "source": [
    "#### Document metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uvvEaEz4mqAP"
   },
   "source": [
    "Use this section to check that document metadata is accurate.\n",
    "\n",
    "Retrieve the document from the `url` and check that each metadata field accurate reflects the actual document.\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ggMh-QWh4JLj"
   },
   "source": [
    "Get a random document:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8BnWpi2pmqAV"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH documents AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'planning' AS section,\n",
    "        ocid,\n",
    "        document\n",
    "    FROM\n",
    "        planning_documents_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('record',\n",
    "            'compiled_release')\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'tender' AS section,\n",
    "        ocid,\n",
    "        document\n",
    "    FROM\n",
    "        tender_documents_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('record',\n",
    "            'compiled_release')\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'awards' AS section,\n",
    "        ocid,\n",
    "        document\n",
    "    FROM\n",
    "        award_documents_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('record',\n",
    "            'compiled_release')\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'contracts' AS section,\n",
    "        ocid,\n",
    "        document\n",
    "    FROM\n",
    "        contract_documents_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('record',\n",
    "            'compiled_release')\n",
    "    UNION\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        ocid,\n",
    "        'implementation' AS section,\n",
    "        document\n",
    "    FROM\n",
    "        contract_implementation_documents_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type IN ('record',\n",
    "            'compiled_release'))\n",
    "SELECT DISTINCT ON (collection_id, release_type)\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    section,\n",
    "    document ->> 'id' AS id,\n",
    "    document ->> 'documentType' AS documentType,\n",
    "    document ->> 'title' AS title,\n",
    "    document ->> 'description' AS description,\n",
    "    document ->> 'url' AS url,\n",
    "    document ->> 'datePublished' AS datePublished,\n",
    "    document ->> 'dateModified' AS dateModified,\n",
    "    document ->> 'format' AS format,\n",
    "    document ->> 'language' AS\n",
    "    LANGUAGE\n",
    "FROM\n",
    "    documents\n",
    "ORDER BY\n",
    "    collection_id,\n",
    "    release_type;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WJSIS4xfA-ER"
   },
   "source": [
    "# Coverage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2jptF5qmIUHW"
   },
   "source": [
    "Use this section to check whether the data includes key fields."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7ZD6dnsAFr1E"
   },
   "source": [
    "## Organization identifiers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pCwl5SeoIZJo"
   },
   "source": [
    "Use this section to check whether the data includes organization identifiers for buyers and procuring entities, and for suppliers and tenderers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XSYeblK7Jr5f"
   },
   "source": [
    "Calculate the coverage of `parties/identifier/id` and `parties/identifier/scheme` grouped by `parties/role`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "u4SjRfkeA-32"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    CASE WHEN roles @> '[\"buyer\"]'::jsonb THEN\n",
    "        'buyer'\n",
    "    WHEN roles @> '[\"procuringEntity\"]'::jsonb THEN\n",
    "        'procuringEntity'\n",
    "    WHEN roles @> '[\"supplier\"]'::jsonb THEN\n",
    "        'supplier'\n",
    "    WHEN roles @> '[\"tenderer\"]'::jsonb THEN\n",
    "        'tenderer'\n",
    "    ELSE\n",
    "        'other'\n",
    "    END AS ROLE,\n",
    "    count(*) party_count,\n",
    "    round(sum(\n",
    "            CASE WHEN party -> 'identifier' ->> 'id' IS NOT NULL THEN\n",
    "                1\n",
    "            ELSE\n",
    "                0\n",
    "            END)::numeric / count(*), 2) id_coverage,\n",
    "    round(sum(\n",
    "            CASE WHEN party -> 'identifier' ->> 'scheme' IS NOT NULL THEN\n",
    "                1\n",
    "            ELSE\n",
    "                0\n",
    "            END)::numeric / count(*), 2) scheme_coverage\n",
    "FROM\n",
    "    parties_summary\n",
    "WHERE\n",
    "    collection_id IN :collection_ids\n",
    "    AND release_type = 'compiled_release'\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    ROLE;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BMIAhPpfKBw4"
   },
   "source": [
    "## Item classifications"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "olRNHvGYMEKf"
   },
   "source": [
    "Use this section to check whether the data includes item classifications."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "JNYU_ab8MKF9"
   },
   "source": [
    "Calculate the coverage of `items/classification/id` and `items/classification/scheme` grouped by `stage`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "JSd9MVtmKDt9"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "WITH items AS (\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'tender' AS stage,\n",
    "        item -> 'classification' ->> 'id' AS id,\n",
    "        item -> 'classification' ->> 'scheme' AS scheme\n",
    "    FROM\n",
    "        tender_items_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type = 'compiled_release'\n",
    "    UNION ALL\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'award' AS stage,\n",
    "        item -> 'classification' ->> 'id' AS id,\n",
    "        item -> 'classification' ->> 'scheme' AS scheme\n",
    "    FROM\n",
    "        award_items_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type = 'compiled_release'\n",
    "    UNION ALL\n",
    "    SELECT\n",
    "        collection_id,\n",
    "        release_type,\n",
    "        'contract' AS stage,\n",
    "        item -> 'classification' ->> 'id' AS id,\n",
    "        item -> 'classification' ->> 'scheme' AS scheme\n",
    "    FROM\n",
    "        contract_items_summary\n",
    "    WHERE\n",
    "        collection_id IN :collection_ids\n",
    "        AND release_type = 'compiled_release'\n",
    ")\n",
    "SELECT\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    stage,\n",
    "    count(*) item_count,\n",
    "    round(sum(\n",
    "            CASE WHEN id IS NOT NULL THEN\n",
    "                1\n",
    "            ELSE\n",
    "                0\n",
    "            END)::numeric / count(*), 2) id_coverage,\n",
    "    round(sum(\n",
    "            CASE WHEN scheme IS NOT NULL THEN\n",
    "                1\n",
    "            ELSE\n",
    "                0\n",
    "            END)::numeric / count(*), 2) scheme_coverage\n",
    "FROM\n",
    "    items\n",
    "GROUP BY\n",
    "    collection_id,\n",
    "    release_type,\n",
    "    stage;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Yo9Zv3iZ5YyO"
   },
   "source": [
    "## Documents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rRZ5V_FQPUqH"
   },
   "source": [
    "OCDS encourages the disclosure of both data and documents, to enable both systemic analysis of large numbers of contracting processes and detailed scrutiny of individual procurements.\n",
    "\n",
    "Use this section to check whether the data includes any documents."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1lHZWoUpPZIh"
   },
   "source": [
    "Get a count of documents from each `documents` array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "81GVpX2E5bDX"
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    count(*) AS contracting_processes,\n",
    "    sum(ps.total_documents) AS planning_documents,\n",
    "    sum(ts.total_documents) AS tender_documents,\n",
    "    sum(total_award_documents) AS award_documents,\n",
    "    sum(total_contract_documents) AS contract_documents,\n",
    "    sum(total_contract_implementation_documents) AS implementation_documents\n",
    "FROM\n",
    "    release_summary rs\n",
    "    LEFT JOIN planning_summary ps ON rs.id = ps.id\n",
    "    LEFT JOIN tender_summary ts ON rs.id = ts.id\n",
    "WHERE\n",
    "    rs.collection_id IN :collection_ids\n",
    "    AND rs.release_type = 'compiled_release'\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyPpPT0FsHcqUr/d/JNLgAmy",
   "name": "setup_notebook",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}