OCDS Analytics

How to build queries for "Top 10"

date 04-06-2019

In this post, we will explain how to build queries for such popular ratings as top 10 contracts, top 10 tenders, top 10 procuring entities, and top 10 suppliers.

Before starting, see our documentation and check out previous blog posts. Note that our API GUI contains docs and schema that help in writing the correct queries.

Schema and Docs for OCDS Analytics API

Sorting order

For these queries, we will need to sort the results in DESC descending or ASC ascending order.

For instance, if you want to order Contracts by value.amount, simply specify the field and sorting direction:

{
  Contracts(
    page: { limit: 5 }
    order: { field: "value.amount", direction: "DESC" }
  ) {
    values {
      contractID
    }
  }
}

If you want to order Organizations based on the sum of their contracts, specify the aggregation first:

{
  Organizations(
    page: { limit: 5 }
    order: {
      aggregation: { sum: { contracts: { field: "value.amount" } } }
      direction: "DESC"
    }
  ) {
    values {
      name      
    }
  }
}

"This week's top 10 contracts" query

Let's start with one of the easiest queries.

Firstly, we should decide which criteria matter for this case:

  • results should be limit-ed to 10 since we need just the first 10 biggest contracts;
  • results should be sorted in the DESC order based on their value.amount.

The query will look as follows:

{
  Contracts(
    page: { limit: 10 }
    order: { field: "value.amount", direction: "DESC" }
  ) {
    values {
      contractID
      status
      value {
        amount
      }
    }
  }
}

Results:

{
  "data": {
    "Contracts": {
      "values": [
        {
          "contractID": "UA-2019-04-26-000414-a-a1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-04-12-000752-a-a1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-04-12-000125-c-b1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-02-11-000850-a-c1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-04-03-001074-a-b1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-02-19-001591-a-b1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2018-11-29-001948-b-c4",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-01-25-000927-a-a1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-02-01-002755-b-c1",
          "status": "active",
          "value": {
            "amount": null
          }
        },
        {
          "contractID": "UA-2019-02-18-001043-a-c1",
          "status": "active",
          "value": {
            "amount": null
          }
        }
      ]
    }
  }
}

As you can see in the response, some amount values are null. We need to exclude such results.

Additionally, we need to pay attention to the contract's status. Definitions can be found in the OCDS Contract status codelist.

One more thing, let's take into account not all contracts, but just those signed within the last week.

So the criteria for this case should be extended as follows:

  • results should be limit-ed to 10 since we need just the first 10 biggest contracts;
  • results should be sorted in the DESC order based on their value.amount;
  • contract's amount value should greater than 0;
  • results should be filtered based on the contract's status (active status means that the contract has been signed by all the parties, and is now legally in force);
  • contracts should be signed within the last week (May 27 - June 02).

The updated query will look as follows:

{
  Contracts(
    page: { limit: 10 }
    filters: [
      { eq: { field: "status", value: "active" } }
      { gt: { field: "value.amount", value: "0" } }
      { gte: { field: "dateSigned", value: "2019-05-27" } }
      { lte: { field: "dateSigned", value: "2019-06-02" } }
    ]
    order: { field: "value.amount", direction: "DESC" }
  ) {
    values {
      contractID
      title
      status
      value {
        amount
      }
    }
  }
}

Results:

{
  "data": {
    "Contracts": {
      "values": [
        {
          "contractID": "UA-2019-05-29-002216-c-c1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 47334000
          }
        },
        {
          "contractID": "UA-2019-05-14-002745-a-b1",
          "title": "«Оновлення природного середовища курорту Моршин. Реконструкція благоустрою рекреаційних територій» Коригування",
          "status": "active",
          "value": {
            "amount": 44303948.5
          }
        },
        {
          "contractID": "UA-2019-05-28-001371-c-c1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 21580000
          }
        },
        {
          "contractID": "UA-2019-05-20-001051-b-b1",
          "title": "Електрична енергія",
          "status": "active",
          "value": {
            "amount": 18976560.48
          }
        },
        {
          "contractID": "UA-2019-05-15-000644-a-a1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 15000000
          }
        },
        {
          "contractID": "UA-2019-05-29-000552-c-b1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 14930829.01
          }
        },
        {
          "contractID": "UA-2019-04-24-000609-b-a1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 13250000
          }
        },
        {
          "contractID": "UA-2019-05-28-001571-c-c1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 12494889.6
          }
        },
        {
          "contractID": "UA-2019-04-24-001396-b-a2",
          "title": null,
          "status": "active",
          "value": {
            "amount": 10952605.17
          }
        },
        {
          "contractID": "UA-2019-04-24-001396-b-a1",
          "title": null,
          "status": "active",
          "value": {
            "amount": 8441803.83
          }
        }
      ]
    }
  }
}

"This week's top 10 tenders" query

Similarly, we can build a query to get 10 tenders with the biggest estimated value.

Criteria for this case will be as follows:

  • results should be limit-ed to 10 since we need just the first 10 biggest tenders;
  • results should be sorted in the DESC order based on their value.amount;
  • tender's amount value should greater than 0;
  • results should be filtered based on tender's status (active status means that a tender process is currently taking place);
  • tender's enquiry period should start within the last week (May 27 - June 02).

The query will look as follows:

{
  Tenders(
    page: { limit: 10 }
    filters: [
      { eq: { field: "status", value: "active" } }
      { gt: { field: "value.amount", value: "0" } }
      { gte: { field: "enquiryPeriod.startDate", value: "2019-05-27" } }
      { lte: { field: "enquiryPeriod.startDate", value: "2019-06-02" } }
    ]
    order: { field: "value.amount", direction: "DESC" }
  ) {
    values {
      tenderID
      title
      status
      value {
        amount
      }
    }
  }
}

Results:

{
  "data": {
    "Tenders": {
      "values": [
        {
          "tenderID": "UA-2019-05-31-000582-c",
          "title": "Будівництво автомобільної дороги від автомобільної дороги державного значення Н-25 Городище – Рівне – Старокостянтинів км 146+630 до автомобільної дороги державного значення Т-18-32 /Н-22/ – Ходоси – Кустин – /Н-25/ км 8+426",
          "status": "active",
          "value": {
            "amount": 822467000
          }
        },
        {
          "tenderID": "UA-2019-05-30-000808-b",
          "title": "Нафта і дистиляти (09130000-9) (бензин)",
          "status": "active",
          "value": {
            "amount": 593581800
          }
        },
        {
          "tenderID": "UA-2019-05-30-002027-b",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 420282900
          }
        },
        {
          "tenderID": "UA-2019-05-30-001431-c",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 420282900
          }
        },
        {
          "tenderID": "UA-2019-05-30-001430-c",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 420282900
          }
        },
        {
          "tenderID": "UA-2019-05-30-000845-a",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 420282900
          }
        },
        {
          "tenderID": "UA-2019-05-30-002025-b",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 280188600
          }
        },
        {
          "tenderID": "UA-2019-05-30-002024-b",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 280188600
          }
        },
        {
          "tenderID": "UA-2019-05-30-001429-c",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 280188600
          }
        },
        {
          "tenderID": "UA-2019-05-30-000844-a",
          "title": "Дизельне паливо",
          "status": "active",
          "value": {
            "amount": 280188600
          }
        }
      ]
    }
  }
}

"This week's top 10 suppliers" query

Query to get 10 biggest suppliers will depend on other criteria:

  • we should use Organizations data collection since suppliers' information is stored there;
  • results should be limit-ed to 10 since we need just the first 10 biggest suppliers;
  • results should be sorted in the DESC order based on value.amount of their contracts;
  • we should use related field contractsAsSupplier to access relations between Organizations and Contracts data collections;
  • results should be filtered based on the contract's status (active status means that the contract has been signed by all the parties, and is now legally in force);
  • associated contract's should be signed within the last week (May 27 - June 02).

The query will look as follows:

{
  Organizations(
    page: { limit: 10 }
    order: {
      aggregation: { sum: { contractsAsSupplier: { field: "value.amount" } } }
      direction: "DESC"
    }
    filters: [
      {
        eq: {
          relatedField: { contractsAsSupplier: { field: "status" } }
          value: "active"
        }
      }
      {
        gte: {
          relatedField: { contractsAsSupplier: { field: "dateSigned" } }
          value: "2019-05-27"
        }
      }
            {
        lte: {
          relatedField: { contractsAsSupplier: { field: "dateSigned" } }
          value: "2019-06-02"
        }
      }
    ]
  ) {
    values {
      id
      name
      contracts {
        aggregation {
          sum(params: { field: "value.amount" })
          count
        }
      }
    }
  }
}

The example of a response:

{
  "data": {
    "Organizations": {
      "values": [
        {
          "id": "UA-EDR-30597936",
          "name": "ТОВАРИСТВО З ОБМЕЖЕНОЮ ВІДПОВІДАЛЬНІСТЮ \"МЕЖИРІЧЕНСЬКИЙ ГІРНИЧО-ЗБАГАЧУВАЛЬНИЙ КОМБІНАТ\"",
          "contracts": {
            "aggregation": {
              "sum": 47334000,
              "count": 1
            }
          }
        },
        {
          "id": "UA-EDR-02576260",
          "name": "ДП \"Прикарпатське БМУ\" ЗАТ \"Карпаткурортбуд\"",
          "contracts": {
            "aggregation": {
              "sum": 44503796.5,
              "count": 2
            }
          }
        },
        ...
      ]
    }
  }
}

"This week's top 10 procuring entities" query

Query to get 10 biggest procuring entities will depend on similar criteria:

  • we should use Organizations data collection, since procuring entities' information is stored there;
  • results should be limit-ed to 10, since we need just the first 10 biggest procuring entities;
  • results should be sorted in the DESC order based on value.amount of their contracts;
  • we should use related field contractsAsProcuringEntity to access relations between Organizations and Contracts data collections;
  • results should be filtered based on the contract's status (active status means that the contract has been signed by all the parties, and is now legally in force);
  • associated contract's should be signed within the last week (May 27 - June 02).

The query will look as follows:

{
  Organizations(
    page: { limit: 5 }
    order: {
      aggregation: { sum: { contractsAsProcuringEntity: { field: "value.amount" } } }
      direction: "DESC"
    }
    filters: [
      {
        eq: {
          relatedField: { contractsAsProcuringEntity: { field: "status" } }
          value: "active"
        }
      }
      {
        gte: {
          relatedField: { contractsAsProcuringEntity: { field: "dateSigned" } }
          value: "2019-05-27"
        }
      }
            {
        lte: {
          relatedField: { contractsAsProcuringEntity: { field: "dateSigned" } }
          value: "2019-06-02"
        }
      }
    ]
  ) {
    values {
      id
      name
      contracts {
        aggregation {
          sum(params: { field: "value.amount" })
          count
        }
      }
    }
  }
}

The example of a response:

{
  "data": {
    "Organizations": {
      "values": [
        {
          "id": "UA-EDR-39391950",
          "name": "АТ \"АКЦІОНЕРНЕ ТОВАРИСТВО «ОБ’ЄДНАНА ГІРНИЧО-ХІМІЧНА КОМПАНІЯ» від імені якого виступає ФІЛІЯ «ІРШАНСЬКИЙ ГІРНИЧО-ЗБАГАЧУВАЛЬНИЙ КОМБІНАТ» АКЦІОНЕРНОГО ТОВАРИСТВА «ОБ’ЄДНАНА ГІРНИЧО-ХІМІЧНА КОМПАНІЯ»\"",
          "contracts": {
            "aggregation": {
              "sum": 47358978.66,
              "count": 4
            }
          }
        },
        {
          "id": "UA-EDR-04372448",
          "name": "Моршинська міська рада Львівської області",
          "contracts": {
            "aggregation": {
              "sum": 44303948.5,
              "count": 1
            }
          }
        },
        ...
      ]
    }
  }
}

If you find this post interesting, subscribe to our newsletter to get news and updates.

← Georgian procurement data is available now! →