template_data_quality_feedback.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": "sowWi_Ve_Lm3"
      },
      "source": [
        "Install requirements (*Note: ocdskingfishercolab installs google-colab, which expects specific versions of pandas and numpy*):\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X4nmyvOa_Ls7"
      },
      "outputs": [],
      "source": [
        "! pip install --upgrade pip > pip.log\n",
        "! pip install --upgrade 'ocdskingfishercolab<0.4' ipywidgets psycopg2-binary >> pip.log"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tezDNj4uAOHq"
      },
      "outputs": [],
      "source": [
        "# @title Import packages and load extensions { display-mode: \"form\" }\n",
        "\n",
        "import gzip\n",
        "import json\n",
        "import os\n",
        "import shutil\n",
        "import tempfile\n",
        "from collections import Counter\n",
        "from datetime import datetime, timezone\n",
        "from pathlib import Path\n",
        "\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from dateutil.relativedelta import relativedelta\n",
        "from google.colab.data_table import DataTable\n",
        "from google.colab.files import download\n",
        "from ipywidgets import widgets\n",
        "from ocdskingfishercolab import (\n",
        "    authenticate_gspread,\n",
        "    calculate_coverage,\n",
        "    download_dataframe_as_csv,\n",
        "    format_thousands,\n",
        "    render_json,\n",
        "    save_dataframe_to_sheet,\n",
        "    save_dataframe_to_spreadsheet,\n",
        "    set_dark_mode,\n",
        "    set_light_mode,\n",
        "    set_spreadsheet_name,\n",
        ")\n",
        "\n",
        "# Load https://pypi.org/project/ipython-sql/\n",
        "%load_ext sql\n",
        "# Load https://colab.research.google.com/notebooks/data_table.ipynb\n",
        "%load_ext google.colab.data_table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "--8vgOiP_58f"
      },
      "outputs": [],
      "source": [
        "# @title Configure the notebook environment { display-mode: \"form\" }\n",
        "\n",
        "# Increase max columns so that Pandas DataFrames with many columns are rendered as data tables.\n",
        "DataTable.max_columns = 50\n",
        "# Remove the index from data tables for easier copy-pasting to Google Docs.\n",
        "DataTable.include_index = False\n",
        "\n",
        "# Return Pandas DataFrames instead of regular result sets.\n",
        "%config SqlMagic.autopandas = True\n",
        "# Don't print number of rows affected.\n",
        "%config SqlMagic.feedback = False\n",
        "\n",
        "# If you set Tools > Settings > Site > Theme to dark, uncomment this line.\n",
        "# set_dark_mode()\n",
        "# If you are creating plots to copy-paste into reports, uncomment this line.\n",
        "# set_light_mode()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IhnbdjqU1e6p"
      },
      "source": [
        "## Charts Setup\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": "code",
      "execution_count": null,
      "metadata": {
        "id": "elpUvMf61Ym6"
      },
      "outputs": [],
      "source": [
        "! pip install altair pyarrow==14.0.0 >> pip.log"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P1aenztz1zK3"
      },
      "source": [
        "Import chart packages and define chart functions. The currently available chart functions are:\n",
        "\n",
        "* Release count\n",
        "* Objects per stage\n",
        "* Releases by month\n",
        "* Objects per year\n",
        "* Top buyers\n",
        "* Usability indicators"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Bip37aP917XY"
      },
      "outputs": [],
      "source": [
        "# @title Chart functions { display-mode: \"form\" }\n",
        "import altair as alt\n",
        "\n",
        "\n",
        "class MissingColumnsError(Exception):\n",
        "    def __init__(self, columns):\n",
        "        super().__init__(f\"The source data is missing one or more of these columns: {columns}\")\n",
        "\n",
        "\n",
        "chart_properties = {\n",
        "    \"width\": 600,\n",
        "    \"height\": 350,\n",
        "    \"padding\": 50,\n",
        "    \"title\": alt.TitleParams(text=\"\", subtitle=[\"\"], fontSize=18),\n",
        "}\n",
        "chart_axis = {\n",
        "    \"titleFontSize\": 14,\n",
        "    \"labelFontSize\": 14,\n",
        "    \"labelPadding\": 5,\n",
        "    \"ticks\": False,\n",
        "    \"domain\": False,\n",
        "}\n",
        "\n",
        "\n",
        "def check_columns(columns, data):\n",
        "    # check if input contains the right columns\n",
        "    if not columns.issubset(data.columns):\n",
        "        raise MissingColumnsError(columns)\n",
        "\n",
        "\n",
        "def plot_release_count(release_counts):\n",
        "    check_columns({\"collection_id\", \"release_type\", \"release_count\", \"ocid_count\"}, release_counts)\n",
        "    return (\n",
        "        alt.Chart(release_counts)\n",
        "        .mark_bar()\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"release_count\",\n",
        "                type=\"ordinal\",\n",
        "                axis=alt.Axis(title=\"release count\", labelAngle=0),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"ocid_count\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"ocid count\", format=\"~s\", tickCount=5),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"release_type\",\n",
        "                type=\"nominal\",\n",
        "                title=\"release type\",\n",
        "                scale=alt.Scale(range=[\"#D6E100\", \"#FB6045\", \"#23B2A7\", \"#6C75E1\"]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"release_count\", title=\"release count\"),\n",
        "                alt.Tooltip(\"ocid_count\", title=\"ocid count\", format=\"~s\"),\n",
        "                alt.Tooltip(\"release_type\", title=\"release type\"),\n",
        "                alt.Tooltip(\"collection_id\", title=\"collection id\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_objects_per_stage(objects_per_stage):\n",
        "    check_columns({\"stage\", \"object_count\"}, objects_per_stage)\n",
        "    stages = [\"planning\", \"tender\", \"awards\", \"contracts\", \"implementation\"]\n",
        "    return (\n",
        "        alt.Chart(objects_per_stage)\n",
        "        .mark_bar(fill=\"#d6e100\")\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"stage\",\n",
        "                type=\"ordinal\",\n",
        "                scale=alt.Scale(domain=stages),\n",
        "                sort=stages,\n",
        "                axis=alt.Axis(title=\"stage\", labelAngle=0),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"object_count\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of objects\", format=\"~s\", tickCount=len(stages)),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"stage\", title=\"stage\"),\n",
        "                alt.Tooltip(\"object_count\", title=\"number of objects\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_releases_by_month(release_dates):\n",
        "    check_columns({\"date\", \"collection_id\", \"release_type\", \"release_count\"}, release_dates)\n",
        "    max_rows = 5000\n",
        "    # check if number of rows is more than 5000\n",
        "    if release_dates.shape[0] > max_rows:\n",
        "        alt.data_transformers.disable_max_rows()\n",
        "\n",
        "    # draw chart\n",
        "    return (\n",
        "        alt.Chart(release_dates)\n",
        "        .mark_line(strokeWidth=3)\n",
        "        .encode(\n",
        "            x=alt.X(\"date\", timeUnit=\"yearmonth\", axis=alt.Axis(title=\"year and month\")),\n",
        "            y=alt.Y(\n",
        "                \"release_count\",\n",
        "                type=\"quantitative\",\n",
        "                aggregate=\"sum\",\n",
        "                axis=alt.Axis(title=\"number of releases\", format=\"~s\", tickCount=5),\n",
        "                scale=alt.Scale(zero=False),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"release_type\",\n",
        "                type=\"nominal\",\n",
        "                scale=alt.Scale(range=[\"#D6E100\", \"#FB6045\", \"#23B2A7\", \"#6C75E1\"]),\n",
        "                legend=alt.Legend(title=\"release type\"),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"date\", timeUnit=\"yearmonth\", title=\"date\"),\n",
        "                alt.Tooltip(\"release_count\", aggregate=\"sum\", title=\"number of releases\"),\n",
        "                alt.Tooltip(\"release_type\", title=\"release type\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_objects_per_year(objects_per_year):\n",
        "    check_columns({\"year\", \"tenders\", \"awards\"}, objects_per_year)\n",
        "    stages = [\"tenders\", \"awards\"]\n",
        "    return (\n",
        "        alt.Chart(objects_per_year)\n",
        "        .transform_fold(stages)\n",
        "        .mark_line(strokeWidth=3)\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"year\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"year\", format=\".0f\", tickCount=objects_per_year.shape[0]),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"value\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of objects\", format=\"~s\", tickCount=5),\n",
        "                scale=alt.Scale(zero=False),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                \"key\",\n",
        "                type=\"nominal\",\n",
        "                title=\"object type\",\n",
        "                scale=alt.Scale(domain=stages, range=[\"#D6E100\", \"#FB6045\"]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"year\", title=\"year\", type=\"quantitative\"),\n",
        "                alt.Tooltip(\"value\", title=\"number of objects\", type=\"quantitative\"),\n",
        "                alt.Tooltip(\"key\", title=\"object type\", type=\"nominal\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_top_buyers(buyers):\n",
        "    check_columns({\"name\", \"total_tenders\"}, buyers)\n",
        "    return (\n",
        "        alt.Chart(buyers)\n",
        "        .mark_bar(fill=\"#d6e100\")\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"total_tenders\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=\"number of tenders\", format=\"~s\", tickCount=5),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                \"name\",\n",
        "                type=\"ordinal\",\n",
        "                axis=alt.Axis(title=\"buyer\", labelAngle=0),\n",
        "                sort=alt.SortField(\"total_tenders\", order=\"descending\"),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(\"name\", title=\"buyer\", type=\"nominal\"),\n",
        "                alt.Tooltip(\"total_tenders\", title=\"number of tenders\", type=\"quantitative\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )\n",
        "\n",
        "\n",
        "def plot_usability_indicators(data, lang=\"English\"):\n",
        "    labels = {\n",
        "        \"English\": {\n",
        "            \"nrow\": \"row_number(indicator)\",\n",
        "            \"sort\": \"calculation\",\n",
        "            \"y_sort\": \"indicator\",\n",
        "            \"groupby\": \"Use case\",\n",
        "            \"title\": \"number of indicators\",\n",
        "            \"tooltip_missing\": \"Missing Fields\",\n",
        "        },\n",
        "        \"Spanish\": {\n",
        "            \"nrow\": \"row_number(Indicador)\",\n",
        "            \"sort\": \"¿Se puede calcular?\",\n",
        "            \"y_sort\": \"Indicador\",\n",
        "            \"groupby\": \"Caso de Uso\",\n",
        "            \"title\": \"Número de indicadores\",\n",
        "            \"tooltip_missing\": \"Campos faltantes\",\n",
        "        },\n",
        "    }\n",
        "    return (\n",
        "        alt.Chart(data)\n",
        "        .transform_window(\n",
        "            nrow=labels[lang][\"nrow\"],\n",
        "            frame=[None, None],\n",
        "            sort=[{\"field\": labels[lang][\"sort\"]}],\n",
        "            groupby=[labels[lang][\"groupby\"]],\n",
        "        )\n",
        "        .mark_circle(size=250, opacity=1)\n",
        "        .encode(\n",
        "            x=alt.X(\n",
        "                \"nrow\",\n",
        "                type=\"quantitative\",\n",
        "                axis=alt.Axis(title=[labels[lang][\"title\"], \"\"], orient=\"top\", tickCount=5),\n",
        "            ),\n",
        "            y=alt.Y(\n",
        "                labels[lang][\"groupby\"],\n",
        "                type=\"nominal\",\n",
        "                sort=alt.Sort(field=labels[lang][\"y_sort\"], op=\"count\", order=\"descending\"),\n",
        "            ),\n",
        "            color=alt.Color(\n",
        "                labels[lang][\"sort\"],\n",
        "                type=\"nominal\",\n",
        "                scale=alt.Scale(range=[\"#fb6045\", \"#d6e100\"]),\n",
        "                legend=alt.Legend(title=[labels[lang][\"sort\"]]),\n",
        "            ),\n",
        "            tooltip=[\n",
        "                alt.Tooltip(labels[lang][\"y_sort\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"groupby\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"sort\"], type=\"nominal\"),\n",
        "                alt.Tooltip(labels[lang][\"tooltip_missing\"], type=\"nominal\"),\n",
        "            ],\n",
        "        )\n",
        "        .properties(**chart_properties)\n",
        "        .configure_axis(**chart_axis)\n",
        "        .configure_view(strokeWidth=0)\n",
        "    )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EEE8NyL9UXi7"
      },
      "source": [
        "## Setup Kingfisher Process"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hVRJEe8Ry04F"
      },
      "source": [
        "### Connect to the database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sX1Mk084L8gN"
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "\n",
        "from ocdskingfishercolab import (\n",
        "    list_collections,\n",
        "    list_source_ids,\n",
        "    set_search_path,\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F8fwq_F6XMLs"
      },
      "source": [
        "Enter your PostgreSQL credentials and connect to the Kingfisher Process database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "outputs": [],
      "source": [
        "user = input(\"Username:\")\n",
        "password = getpass.getpass(\"Password:\")\n",
        "\n",
        "# Don't show connection string after execute.\n",
        "%config SqlMagic.displaycon = False\n",
        "\n",
        "connection_string = (\n",
        "    \"postgresql://\"\n",
        "    + user\n",
        "    + \":\"\n",
        "    + password\n",
        "    + \"@postgres.kingfisher.open-contracting.org/kingfisher_process?sslmode=require\"\n",
        ")\n",
        "%sql $connection_string"
      ]
    },
    {
      "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",
        "SELECT\n",
        "    summaries.selected_collections.*,\n",
        "    source_id\n",
        "FROM\n",
        "    summaries.selected_collections\n",
        "INNER JOIN\n",
        "    collection\n",
        "    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](https://ocdsdeploy.readthedocs.io/en/latest/use/kingfisher-summarize.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pP9KsYyT1Xnd"
      },
      "source": [
        "## Check for data collection and processing errors\n",
        "\n",
        "Kingfisher Collect and Kingfisher Process log messages that might indicate OCDS implementation errors or API stability issues.\n",
        "\n",
        "Confirm any errors or warnings by manually checking the OCDS publication."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i_rbrQG_3_-x"
      },
      "source": [
        "### Kingfisher Collect\n",
        "\n",
        "See [how to review the Kingfisher Collect crawl’s log file](https://kingfisher-collect.readthedocs.io/en/latest/logs.html).\n",
        "\n",
        "**Note:** There is an open [pull request](https://github.com/open-contracting/notebooks-ocds/pull/44) to automate this. If interested, please comment on the pull request to prioritize it."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1U1_PR0J4DQA"
      },
      "source": [
        "### Kingfisher Process\n",
        "\n",
        "Users add a note when starting a crawl and when running the `load` command.\n",
        "\n",
        "Kingfisher Process adds notes (the `note` column) at different levels (the `code` column):\n",
        "\n",
        "- `INFO`\n",
        "  - *load*: The Kingfisher Collect crawl's reason for closing and statistics (the `data` column).\n",
        "- `WARNING`\n",
        "  - *compile*: When a record has undated releases, linked releases or no releases, but a compiled release can be calculated (by merging remaining dated releases, using the `compiledRelease` field, or using an undated release with a 'compiled' tag).\n",
        "- `ERROR`\n",
        "  - *load*: When the input data is invalid JSON.\n",
        "  - *load*: When Kingfisher Collect yielded a `FileError` item.\n",
        "  - *compile*: When no compiled release can be calculated for a record.\n",
        "\n",
        "📗 **You can check the full list of note types and what they mean in the Kingfisher Process's [documentation](https://kingfisher-process.readthedocs.io/en/latest/database.html#collection-note-table)**📗\n",
        "\n",
        "**Note:** `FileError` items in Kingfisher Process are [scheduled for removal](https://github.com/open-contracting/kingfisher-process/issues/366), as they also appear in the crawl's log file.\n",
        "\n",
        "List the notes for each of your collections:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "U0x8mo8Yyj39"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    code,\n",
        "    note,\n",
        "    data\n",
        "FROM\n",
        "    collection_note\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7fpo23ErO_lL"
      },
      "source": [
        "## Perform manual conformance checks"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B_nDBOyG2279"
      },
      "source": [
        "[Conforming publications](https://standard.open-contracting.org/latest/en/schema/conformance_and_extensions/#publication-conformance):\n",
        "\n",
        "* Use a [registered OCID prefix](https://standard.open-contracting.org/latest/en/schema/identifiers/#contracting-process-identifier-ocid).\n",
        "* The [OCDS Data Review Tool](https://standard.open-contracting.org/review/) can report results on the data.\n",
        "* Must not use terms from outside the OCDS schema where its terms would suffice.\n",
        "\n",
        "Use this section to:\n",
        "\n",
        "* Check if the proper OCID prefix is in use.\n",
        "* Check if the OCDS Data Review Tool is able to report results on data.\n",
        "* 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",
        "* Identify deprecated fields, in order to recommend a change to the publisher's OCDS implementation.\n",
        "\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](https://github.com/open-contracting/standard/issues) to describe the semantics of the field or code and to discuss how to model it. Report any issues to the publisher.\n",
        "\n",
        "**Note:** This section depends on the `check` step of Kingfisher Process having completed. See *Check for structure and format errors >Confirm that checks are complete*. If the checks are not complete or the OCDS Data Review Tool is not able to report results on data, **the publisher don't pass that conformance criterion**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### OCID prefix\n",
        "\n",
        "Check that the data uses the OCID prefix that was issued to this specific publisher.\n",
        "\n",
        "You only need to run either the *Release prefixes* or *Record prefixes* section, depending on the publication's data format.\n",
        "\n",
        "Update the ocid prefix in the appropriate cell. Prefixes can be found at [the list of registered prefixes](https://docs.google.com/spreadsheets/d/1E5ZVhc8VhGOakCq4GegvkyFYT974QQb-sSjvOfaxH7s/pubhtml?gid=506986894&single=true&widget=true)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### **Release prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "# Do not remove the final % character\n",
        "ocid_prefix = \"%\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_release_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "ocid_prefix_release_check"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "#### **Record prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_record_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    record_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "ocid_prefix_record_check"
      ]
    },
    {
      "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/1CS_TMubqoYaucT8JXPTgLS-mF4eMIifX-6mD0xpWg9M/edit)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LzOXXZob9gA2"
      },
      "source": [
        "List the extensions declared in the package metadata.\n",
        "\n",
        "**Note:** This query should be kept in sync with the query in *Check scope > Extensions*.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Yul28_sU9gA2"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    jsonb_array_elements(package_data -> 'extensions') AS ocds_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",
        "    ocds_extension\n",
        "ORDER BY\n",
        "    collection_id ASC,\n",
        "    release_type ASC,\n",
        "    count DESC;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CeabYppc20t4"
      },
      "source": [
        "### Additional fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "52thQMhSsTe8"
      },
      "source": [
        "[lib-cove-ocds](https://github.com/open-contracting/lib-cove-ocds) 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 using extension fields. You should report the fields to the publisher and recommend that they upgrade to OCDS 1.1.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rOaRxbxgpzKw"
      },
      "source": [
        "List additional fields.\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",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                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",
        "\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((additional_fields ->> 'count')::int) AS count\n",
        "    FROM\n",
        "        check_results\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        field,\n",
        "        path\n",
        "    ORDER BY\n",
        "        path ASC,\n",
        "        count DESC\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path',\n",
        "        additional_fields ->> 'field_name')\n",
        "        collection_id,\n",
        "        release_type,\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\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    WHERE\n",
        "        additional_fields ->> 'examples' <> '[]'\n",
        "        AND additional_fields ->> 'examples' IS NOT NULL\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    counts.collection_id,\n",
        "    counts.release_type,\n",
        "    counts.path,\n",
        "    counts.field,\n",
        "    count,\n",
        "    examples\n",
        "FROM\n",
        "    counts\n",
        "LEFT JOIN examples\n",
        "    USING (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        path,\n",
        "        field\n",
        "    )\n",
        "ORDER BY\n",
        "    path,\n",
        "    field;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v_6_-h72u3_Q"
      },
      "source": [
        "#### Additional field examples\n",
        "\n",
        "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",
        "WITH additional_field_releases AS (\n",
        "    SELECT\n",
        "        ocid,\n",
        "        release.release_id,\n",
        "        data_id,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field\n",
        "    FROM\n",
        "        release_check\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            cove_output -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    INNER JOIN release ON release_check.release_id = release.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND random() < :sample_size\n",
        "),\n",
        "\n",
        "additional_fields AS (\n",
        "    SELECT DISTINCT\n",
        "        path,\n",
        "        field\n",
        "    FROM\n",
        "        additional_field_releases\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        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\n",
        "        ON\n",
        "            additional_fields.path = additional_field_releases.path\n",
        "            AND additional_fields.field = additional_field_releases.field\n",
        "    INNER JOIN data ON data.id = data_id\n",
        "    ORDER BY\n",
        "        additional_fields.path,\n",
        "        additional_fields.field\n",
        ")\n",
        "\n",
        "SELECT jsonb_build_object('releases', jsonb_agg(data)) AS 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 codes in the context of an open codelist.\n",
        "\n",
        "Using additional codes in the context of a closed codelist is an error, and is reported in the *Check for structure and format errors* section."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V8Gx2WAOqERy"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                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",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    additional_open_codelist_values.value -> 'codelist' AS codelist,\n",
        "    codes.value AS code,\n",
        "    count(*) AS occurrences\n",
        "FROM\n",
        "    check_results\n",
        "CROSS JOIN\n",
        "    jsonb_each(\n",
        "        results -> 'additional_open_codelist_values'\n",
        "    ) AS additional_open_codelist_values\n",
        "CROSS JOIN jsonb_array_elements(value -> 'values') AS 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": "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": [
        "List deprecated fields:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Oc0ex2vNlIOU"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT DISTINCT ON (collection_id, path, deprecated_version, explanation)\n",
        "    collection_id,\n",
        "    regexp_replace(trim('\"' FROM paths::text), '\\/[0-9]+', '', 'g')\n",
        "    || '/'\n",
        "    || (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\n",
        "    jsonb_array_elements(\n",
        "        cove_output -> 'deprecated_fields'\n",
        "    ) AS deprecated_fields\n",
        "CROSS JOIN jsonb_array_elements(deprecated_fields -> 'paths') AS paths\n",
        "INNER JOIN release ON release_check.release_id = release.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids;\n"
      ]
    },
    {
      "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\n",
        "\n",
        "If you are preparing an [Ad-hoc structure and format feedback](https://docs.google.com/document/d/1_k7eA2rI-k5EH8VESkVAB73wa_qrpplL-7dKgMLTGZc/edit#heading=h.i7tpu8c49dcv), you might skip this section."
      ]
    },
    {
      "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 Process creates 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. Where possible, you should check these numbers against the total number of results available in the frontend of the data source.\n",
        "\n",
        "Count the number of releases, records and compiled releases, for each collection.\n",
        "\n",
        "**Note:** These columns are not yet populated in version 2 of Kingfisher Process. Comment on [this issue](https://github.com/open-contracting/kingfisher-process/issues/370) to prioritize it."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MVJ6sIWeAZzD"
      },
      "outputs": [],
      "source": [
        "%%sql\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 a contracting process to which a release is related."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EqsaSZj6DkuS"
      },
      "source": [
        "Count the number of releases, for each release tag:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WwgeZMX5Ame4"
      },
      "outputs": [],
      "source": [
        "%%sql\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"
      ]
    },
    {
      "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": [
        "Plot a count of objects per stage:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Oe8W_HLCKUsO"
      },
      "outputs": [],
      "source": [
        "%%sql objects_per_stage <<\n",
        "SELECT\n",
        "    CASE\n",
        "        WHEN paths.path = 'contracts/implementation'\n",
        "            THEN\n",
        "                'implementation'\n",
        "        ELSE\n",
        "            paths.path\n",
        "    END AS stage,\n",
        "    CASE\n",
        "        WHEN\n",
        "            paths.path IN ('planning', 'tender', 'contracts/implementation')\n",
        "            THEN\n",
        "                greatest(field_counts.object_property, 0)\n",
        "        ELSE\n",
        "            greatest(field_counts.array_count, 0)\n",
        "    END AS object_count\n",
        "FROM (\n",
        "    SELECT\n",
        "        unnest(\n",
        "            ARRAY[\n",
        "                'planning',\n",
        "                'tender',\n",
        "                'awards',\n",
        "                'contracts',\n",
        "                'contracts/implementation'\n",
        "            ]\n",
        "        ) AS path\n",
        ") AS paths\n",
        "LEFT JOIN (\n",
        "    SELECT *\n",
        "    FROM\n",
        "        field_counts\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "        AND path IN (\n",
        "            'planning',\n",
        "            'tender',\n",
        "            'awards',\n",
        "            'contracts',\n",
        "            'contracts/implementation'\n",
        "        )\n",
        ") AS field_counts USING (path)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mKo6Q4HimvQZ"
      },
      "outputs": [],
      "source": [
        "plot_objects_per_stage(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": [
        "Calculate the earliest and latest `date`, `awards/date` and `contracts/dateSigned`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qy0VRQ3IDwsG"
      },
      "outputs": [],
      "source": [
        "%%sql\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"
      ]
    },
    {
      "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",
        "SELECT\n",
        "    collection_id::text,\n",
        "    release_type,\n",
        "    date,\n",
        "    count(*) AS release_count\n",
        "FROM\n",
        "    release_summary AS 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"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OM2aiiJzAmzM"
      },
      "outputs": [],
      "source": [
        "# Resample by month\n",
        "release_dates[\"date\"] = release_dates[\"date\"].dt.strftime(\"%Y-%m\")\n",
        "release_dates = (\n",
        "    release_dates.groupby([\"collection_id\", \"release_type\", \"date\"]).agg({\"release_count\": \"sum\"}).reset_index()\n",
        ")\n",
        "\n",
        "plot_releases_by_month(release_dates)"
      ]
    },
    {
      "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": [
        "List the extensions declared in the package metadata:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qFbWPY5Eq7fv"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    jsonb_array_elements(package_data -> 'extensions') AS ocds_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",
        "    ocds_extension\n",
        "ORDER BY\n",
        "    collection_id ASC,\n",
        "    release_type ASC,\n",
        "    count DESC;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cE548sDyZyG5"
      },
      "source": [
        "## Check for structure and format errors\n",
        "\n",
        "Kingfisher Collect reformats data sources as release packages or record packages. Check the `data_type` class attribute of the [spider](https://github.com/open-contracting/kingfisher-collect/tree/main/kingfisher_scrapy/spiders). If it is not 'release_package' or 'record_package', recommend to the publisher to package their data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gtzHL_Sul8i3"
      },
      "source": [
        "Kingfisher Process checks data against the OCDS schema using [lib-cove-ocds](https://github.com/open-contracting/lib-cove-ocds), same as the [OCDS Data Review Tool](https://review.standard.open-contracting.org). 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": [
        "If a crawl is scheduled using Kingfisher Collect, by default, Kingfisher Process performs structural checks. Checking data is the slowest step. For large collections, it is recommended to skip the `check` step or to collect only a sample. Otherwise, there can be a backlog of data to check.\n",
        "\n",
        "Use this query to determine whether checks are complete for your collection(s).\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",
        "SELECT\n",
        "    collection_id,\n",
        "    'release' AS collection_type,\n",
        "    CASE\n",
        "        WHEN count(release.id) = count(release_check.id)\n",
        "            THEN\n",
        "                'complete'\n",
        "        WHEN count(release_check.id) = 0\n",
        "            THEN\n",
        "                'not_started'\n",
        "        ELSE\n",
        "            'in_progress'\n",
        "    END AS check_status,\n",
        "    count(release_check.id)::text\n",
        "    || '/'\n",
        "    || 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\n",
        "        WHEN count(record.id) = count(record_check.id)\n",
        "            THEN\n",
        "                'complete'\n",
        "        WHEN count(record_check.id) = 0\n",
        "            THEN\n",
        "                'not_started'\n",
        "        ELSE\n",
        "            'in_progress'\n",
        "    END AS check_status,\n",
        "    count(record_check.id)::text\n",
        "    || '/'\n",
        "    || 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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uTeVs0jnlgHu"
      },
      "source": [
        "### Error summary"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XGxn_SwCllq4"
      },
      "source": [
        "Summarize the 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",
        "WITH errors AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        errors ->> 'type' AS error_type,\n",
        "        left(\n",
        "            errors ->> 'description',\n",
        "            49000\n",
        "        ) AS error,\n",
        "        ocid,\n",
        "        errors ->> 'field' AS field,\n",
        "        errors ->> 'value' AS value,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                errors ->> 'type',\n",
        "                left(\n",
        "                    errors ->> 'description',\n",
        "                    49000\n",
        "                )\n",
        "        ) AS rownum\n",
        "    FROM\n",
        "        release_check AS rc\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "    INNER JOIN release AS 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(\n",
        "            errors ->> 'description',\n",
        "            49000\n",
        "        ) AS error,\n",
        "        ocid,\n",
        "        errors ->> 'field' AS field,\n",
        "        errors ->> 'value' AS value,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                errors ->> 'type',\n",
        "                left(\n",
        "                    errors ->> 'description',\n",
        "                    49000\n",
        "                )\n",
        "        ) AS rownum\n",
        "    FROM\n",
        "        record_check AS rc\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "    INNER JOIN record AS r ON rc.record_id = r.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        error_type,\n",
        "        field,\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",
        "        field,\n",
        "        error\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    error_type,\n",
        "    errors.field,\n",
        "    error,\n",
        "    count(*) AS count,\n",
        "    example_ocids,\n",
        "    example_values\n",
        "FROM\n",
        "    errors\n",
        "INNER JOIN examples USING (collection_id, error_type, error)\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    error_type,\n",
        "    errors.field,\n",
        "    error,\n",
        "    example_ocids,\n",
        "    example_values;\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": [
        "List all 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",
        "SELECT\n",
        "    collection_id,\n",
        "    'release' AS collection_type,\n",
        "    errors ->> 'type' AS error_type,\n",
        "    errors ->> 'field' AS field,\n",
        "    left(\n",
        "        errors ->> 'description',\n",
        "        49000\n",
        "    ) AS error,\n",
        "    ocid,\n",
        "    errors ->> 'value' AS value\n",
        "FROM\n",
        "    release_check AS rc\n",
        "CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "INNER JOIN release AS 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(\n",
        "        errors ->> 'description',\n",
        "        49000\n",
        "    ) AS error,\n",
        "    ocid,\n",
        "    errors ->> 'value' AS value\n",
        "FROM\n",
        "    record_check AS rc\n",
        "CROSS JOIN jsonb_array_elements(cove_output -> 'validation_errors') AS errors\n",
        "INNER JOIN record AS 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": "0jMaeqCJPA5J"
      },
      "source": [
        "## Perform manual data quality checks\n",
        "\n",
        "[Pelican](https://ocdsdeploy.readthedocs.io/en/latest/use/pelican.html) performs dozens of data quality checks that can be exported to Google Docs. It performs checks on compiled releases, not on individual releases, records, or package metadata.\n",
        "\n",
        "Use this section to perform quality checks that require manual review, including:\n",
        "\n",
        "* Package metadata\n",
        "* Release metadata\n",
        "* Free-text language (*can be added to Pelican if needed*)\n",
        "* Change history\n",
        "* Overfill\n",
        "* Placeholder values\n",
        "* Ground truth\n",
        "* Identifier scheme preference\n",
        "* Segmented field coverage"
      ]
    },
    {
      "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 the values in the package metadata.\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": [
        "Display the package metadata for each collection:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FpZvLuscPAf7"
      },
      "outputs": [],
      "source": [
        "%%sql\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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BqfowfAfT6Z_"
      },
      "source": [
        "#### Release tags\n",
        "\n",
        "> A release must be tagged to indicate whether it is about a planning process or a contracting process and, if it is about the latter, to indicate the stage of the contracting process to which it relates. (OCDS 1.2)\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](https://standard.open-contracting.org/latest/en/schema/codelists/#release-tagf) 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": [
        "Count the number of times a section is 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",
        "WITH contract_implementation AS (\n",
        "    SELECT\n",
        "        cs.collection_id,\n",
        "        cs.release_type,\n",
        "        tag,\n",
        "        count(contract -> 'implementation') AS contract_implementation\n",
        "    FROM\n",
        "        contracts_summary AS cs\n",
        "    LEFT JOIN release_summary USING (id)\n",
        "    GROUP BY\n",
        "        cs.collection_id, cs.release_type, tag\n",
        "),\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",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    sections.tag,\n",
        "    release_count,\n",
        "    planning,\n",
        "    tender,\n",
        "    award,\n",
        "    contract,\n",
        "    contract_implementation\n",
        "FROM\n",
        "    sections\n",
        "LEFT JOIN contract_implementation USING (collection_id, release_type, tag);\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",
        "WITH date_frequency AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        date,\n",
        "        count(*) AS release_count,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                release_type\n",
        "            ORDER BY count(*) DESC\n",
        "        ) AS row_number\n",
        "    FROM\n",
        "        release_summary\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        date\n",
        ")\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 ASC,\n",
        "    release_type ASC,\n",
        "    release_count DESC;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ik2hkA8yghRT"
      },
      "source": [
        "#### Language"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hE6aXnygjhuk"
      },
      "source": [
        "> The default language of the data, from the open language codelist. A BCP47 language tag is allowed, if there is a user need for the additional information.\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": [
        "List the language codes used, with an example release for each language."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HRweYx7nUvRE"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT DISTINCT ON (\n",
        "    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"
      ]
    },
    {
      "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/primer/releases_and_records/).\n",
        "\n",
        "Fully implemented, releases and records can be used to publish the following for each contracting process:\n",
        "\n",
        "* Multiple releases, one for each change or update to the contracting process\n",
        "* A single 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 change history 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": [
        "Calculate 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",
        "WITH release_counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid,\n",
        "        count(*) AS release_count\n",
        "    FROM\n",
        "        release_summary AS rs\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid\n",
        ")\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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uX8ddZoGnqoJ"
      },
      "source": [
        "Count the number of contracting processes, for each observed number of releases:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zojrnAxlke2p"
      },
      "outputs": [],
      "source": [
        "%%sql release_count_summary <<\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 AS rs\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type IN ('release', 'embedded_release')\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid\n",
        ")\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"
      ]
    },
    {
      "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",
        "WITH release_counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid,\n",
        "        count(*) AS release_count\n",
        "    FROM\n",
        "        release_summary AS rs\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type IN ('release', 'embedded_release')\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid\n",
        ")\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"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yeBOaDsO6uDS"
      },
      "outputs": [],
      "source": [
        "plot_release_count(release_counts)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ww3sXJfXY9Lf"
      },
      "source": [
        "Create 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",
        "WITH ranked_ocids AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid,\n",
        "        count(*),\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                release_type\n",
        "            ORDER BY count(*) DESC\n",
        "        ) AS row_number\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type IN ('release', 'embedded_release')\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        ocid\n",
        ")\n",
        "\n",
        "SELECT 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 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": [
        "# spreadsheet_name = ''\n",
        "# save_dataframe_to_spreadsheet(multiple_release_examples, f'{spreadsheet_name}_multiple_releases')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XcooMawPg41a"
      },
      "source": [
        "#### Static release ID"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HP0YAYtmg88t"
      },
      "source": [
        "The release identifier 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`, 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": [
        "List the releases where `id` and `ocid` have the same value:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7IoW8sC2hkLc"
      },
      "outputs": [],
      "source": [
        "%%sql\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 (\n",
        "        ocid = release_id\n",
        "        OR ocid ILIKE '%%' || release_id || '%%'\n",
        "    )\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L5an1c9kXgO7"
      },
      "source": [
        "#### Duplicate release ID"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iqTIUf3s7p85"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    ocid,\n",
        "    release_id,\n",
        "    count(*) AS release_count\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "GROUP BY\n",
        "    ocid,\n",
        "    release_id\n",
        "HAVING\n",
        "    count(*) > 1\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SpZpEQ1zrw16"
      },
      "source": [
        "Export to Google Sheets the 5 release IDs with the most duplicates, and review them to determine whether the full release is duplicated or only the release ID."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BYhoit7Nn53n"
      },
      "outputs": [],
      "source": [
        "%%sql duplicate_release_ids <<\n",
        "WITH release_ids AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        ocid,\n",
        "        release_type,\n",
        "        release_id,\n",
        "        count(*) AS release_count,\n",
        "        row_number() OVER (\n",
        "            PARTITION BY\n",
        "                collection_id,\n",
        "                release_type\n",
        "            ORDER BY count(*) DESC\n",
        "        ) AS row_number\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        ocid,\n",
        "        release_type,\n",
        "        release_id\n",
        "    HAVING\n",
        "        count(*) > 1\n",
        ")\n",
        "\n",
        "SELECT 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 ocid\n",
        "        FROM\n",
        "            release_ids\n",
        "        WHERE\n",
        "            row_number <= 5\n",
        "    );\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GmX3uLJ9Ui6s"
      },
      "outputs": [],
      "source": [
        "render_json(duplicate_release_ids[\"release_package\"][0])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "q16QKC2WqAz2"
      },
      "outputs": [],
      "source": [
        "# save_dataframe_to_spreadsheet(duplicate_release_ids, f'{spreadsheet_name}_duplicate_release_ids')"
      ]
    },
    {
      "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 between 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",
        "SELECT\n",
        "    contracts_summary.collection_id,\n",
        "    contracts_summary.release_type,\n",
        "    coalesce(\n",
        "        awards_summary.date = contracts_summary.datesigned,\n",
        "        FALSE\n",
        "    ) AS date_match,\n",
        "    coalesce(\n",
        "        (awards_summary.value_amount = contracts_summary.value_amount)\n",
        "        AND (awards_summary.value_currency = contracts_summary.value_currency),\n",
        "        FALSE\n",
        "    ) AS value_match,\n",
        "    coalesce((\n",
        "        awards_summary.contractperiod_startdate\n",
        "        = contracts_summary.period_startdate\n",
        "    )\n",
        "    AND (\n",
        "        awards_summary.contractperiod_enddate\n",
        "        = contracts_summary.period_startdate\n",
        "    ), FALSE) AS period_match,\n",
        "    coalesce(\n",
        "        awards_summary.award ->> 'documents'\n",
        "        = contracts_summary.contract ->> 'documents',\n",
        "        FALSE\n",
        "    ) AS documents_match,\n",
        "    count(contracts_summary.id) AS contract_count\n",
        "FROM\n",
        "    contracts_summary\n",
        "INNER JOIN awards_summary\n",
        "    ON\n",
        "        contracts_summary.id = awards_summary.id\n",
        "        AND contracts_summary.awardid = awards_summary.award_id\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 ASC,\n",
        "    contracts_summary.release_type ASC,\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 a 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",
        "SELECT\n",
        "    tender_summary.collection_id,\n",
        "    tender_summary.release_type,\n",
        "    coalesce(\n",
        "        contracts_summary.contract -> 'items'\n",
        "        = awards_summary.award -> 'items',\n",
        "        FALSE\n",
        "    ) AS award_contract_match,\n",
        "    count(contracts_summary.id) AS contracts_count,\n",
        "    coalesce(\n",
        "        awards_summary.award -> 'items'\n",
        "        = tender_summary.tender -> 'items',\n",
        "        FALSE\n",
        "    ) AS tender_award_match,\n",
        "    count(awards_summary.id) AS awards_count\n",
        "FROM\n",
        "    tender_summary\n",
        "INNER JOIN awards_summary USING (id)\n",
        "LEFT JOIN contracts_summary\n",
        "    ON\n",
        "        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"
      ]
    },
    {
      "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",
        "WITH examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        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', 'embedded_release')\n",
        "    ORDER BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        random()\n",
        ")\n",
        "\n",
        "SELECT jsonb_build_object('releases', jsonb_agg(release)) AS 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": [
        "### Ground truth"
      ]
    },
    {
      "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 or incorrect organization identifiers. (Pelican checks schemes, not 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": [
        "Select a random sample of 3 identifiers for each organization identifier scheme:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XaF65iyq1YMm"
      },
      "outputs": [],
      "source": [
        "%%sql organization_identifiers <<\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"
      ]
    },
    {
      "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": "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",
        "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', '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', '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', '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', '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', 'compiled_release')\n",
        ")\n",
        "\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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8syUMlFlme6X"
      },
      "source": [
        "### Identifier scheme preference\n",
        "\n",
        "Publishers sometimes use a domestic register as the `.scheme` for foreign-registered companies. It is preferred to use an international register.\n",
        "\n",
        "Use this section to check whether an international 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",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    name,\n",
        "    scheme,\n",
        "    id,\n",
        "    legalname,\n",
        "    country,\n",
        "    roles\n",
        "FROM (\n",
        "    SELECT DISTINCT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        party ->> 'name' AS name,\n",
        "        party -> 'identifier' ->> 'scheme' AS scheme,\n",
        "        party -> 'identifier' ->> 'id' AS id,\n",
        "        party -> 'identifier' ->> 'legalName' AS legalname,\n",
        "        party -> 'address' ->> 'country' AS country,\n",
        "        roles,\n",
        "        rank()\n",
        "            OVER (\n",
        "                PARTITION BY\n",
        "                    collection_id,\n",
        "                    release_id,\n",
        "                    party -> 'identifier' ->> 'scheme'\n",
        "                ORDER BY random()\n",
        "            )\n",
        "    FROM parties_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type IN ('record', 'compiled_release')\n",
        "        AND party -> 'address' ->> 'country' NOT ILIKE :country\n",
        ") AS identifiers\n",
        "WHERE\n",
        "    rank <= 3\n",
        "ORDER BY\n",
        "    scheme;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WJSIS4xfA-ER"
      },
      "source": [
        "### Coverage\n",
        "\n",
        "Coverage is covered by Pelican. This section segments field coverage for priority fields (Pelican does not segment by, e.g., party role).\n"
      ]
    },
    {
      "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, procuring entities, 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",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    CASE\n",
        "        WHEN roles @> '[\"buyer\"]'::jsonb\n",
        "            THEN\n",
        "                'buyer'\n",
        "        WHEN roles @> '[\"procuringEntity\"]'::jsonb\n",
        "            THEN\n",
        "                'procuringEntity'\n",
        "        WHEN roles @> '[\"supplier\"]'::jsonb\n",
        "            THEN\n",
        "                'supplier'\n",
        "        WHEN roles @> '[\"tenderer\"]'::jsonb\n",
        "            THEN\n",
        "                'tenderer'\n",
        "        ELSE\n",
        "            'other'\n",
        "    END AS role,\n",
        "    count(*) AS party_count,\n",
        "    round(sum(\n",
        "        CASE\n",
        "            WHEN party -> 'identifier' ->> 'id' IS NOT NULL\n",
        "                THEN\n",
        "                    1\n",
        "            ELSE\n",
        "                0\n",
        "        END\n",
        "    )::numeric / count(*), 2) AS id_coverage,\n",
        "    round(sum(\n",
        "        CASE\n",
        "            WHEN party -> 'identifier' ->> 'scheme' IS NOT NULL\n",
        "                THEN\n",
        "                    1\n",
        "            ELSE\n",
        "                0\n",
        "        END\n",
        "    )::numeric / count(*), 2) AS 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"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8aERQ1ZbsR99"
      },
      "source": [
        "### OCID modeling"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DiQ4pEkLvDnX"
      },
      "source": [
        "Use this section to check if the OCID is being modeled as expected"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SHmE4lEdu713"
      },
      "source": [
        "#### Number of tenders per ocid\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F4-Dj3GusTzI"
      },
      "source": [
        "Use this section to check there is always only one tender per ocid.\n",
        "\n",
        "If the data contains planning data and multiple tenders per ocid, it might indicate that the ocid is being assigned in the planning stage, and the planning can have more than one tender, for example, due to unsuccessful tenders."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fyUd67h9tr8g"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    ocid,\n",
        "    count(DISTINCT data -> 'tender' ->> 'id') AS cnt\n",
        "FROM data\n",
        "INNER JOIN release ON data.id = data_id\n",
        "WHERE collection_id IN :collection_ids\n",
        "GROUP BY ocid\n",
        "ORDER BY cnt DESC;\n"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "template_data_quality_feedback",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}