component_check_quality.ipynb

{
  "cells": [
    {
      "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": {
        "collapsed": false,
        "vscode": {
          "languageId": "plaintext"
        }
      },
      "outputs": [],
      "source": [
        "organization_identifiers.groupby([\"collection_id\", \"release_type\", \"scheme\"]).sample(n=3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "### Item classifications"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "Use this section to check whether the data includes item classifications."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "WITH items AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'tender' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        tender_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'award' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        award_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'contract' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        contract_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    stage,\n",
        "    count(*) AS item_count,\n",
        "    scheme\n",
        "FROM\n",
        "    items\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    scheme,\n",
        "    stage\n",
        "ORDER BY stage;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "collapsed": false
      },
      "source": [
        "Select a random sample of 3 identifiers for each item identifier scheme:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "%%sql item_identifiers <<\n",
        "WITH items AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'tender' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        tender_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'award' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        award_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        "    UNION ALL\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        'contract' AS stage,\n",
        "        item -> 'classification' ->> 'id' AS id,\n",
        "        item -> 'classification' ->> 'scheme' AS scheme\n",
        "    FROM\n",
        "        contract_items_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type = 'compiled_release'\n",
        ")\n",
        "\n",
        "SELECT *\n",
        "FROM\n",
        "    items\n",
        "WHERE id IS NOT NULL;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": false
      },
      "outputs": [],
      "source": [
        "item_identifiers.groupby([\"collection_id\", \"release_type\", \"stage\", \"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": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}