Public procurement register · 2023 192,036 contracts · 13,446 modifications PostgreSQL

Correct SQL, wrong answer

Four questions about Portuguese public procurement, the queries that answer them, and the reason every answer is wrong. The worst is out by a factor of sixty.

The four questions → Read the SQL Repository ↗
Summary
grain fan-out entity resolution sentinels public data

Portugal publishes every public contract it signs. In 2023 that was 192,036 contracts worth 15.5 billion euros, plus a separate register of every subsequent modification to them. Both are open data, and both invite the obvious question: who gets the money, and how much does it grow after the ink dries.

This project asks four such questions, writes the query anyone would write, and then shows what the answer is actually measuring. None of the failing queries is a beginner's mistake. Each one is syntactically correct, each uses the right join, and each returns a figure a person would put in a slide without a second thought.

The errors are semantic. Wrong grain, wrong meaning of a column, wrong key for an entity. They are invisible in the output, which is precisely what makes them dangerous: a query that fails loudly gets fixed, and a query that returns 22 billion euros instead of 373 million gets published.

What the four questions have in common is that the correction never required cleverness — no fuzzy matching, no machine learning, no clever rewriting. It required reading the column definitions and asking what one row means.

§ 01

What the gaps come to

four questions, four wrong answers
Growth after signature 59.5× The obvious sum reports 22.2 bn € of contract growth. The real net figure is 373 m €.
Spellings of one company 79 One supplier, 764 contracts, 79 versions of its name. The most common covers 26% of them.
Suppliers with >1 name 22.9% 6,487 of 28,322. Any analysis grouped by supplier name is wrong — no text cleaning fixes it.
Value invented by one join 457m € A correct join, an incorrect SUM. +2.95% on the national total, from contracts with more than one supplier.
§ 02

The four questions

ascending order of damage
{{ q.qn }}

{{ q.title }}

✕ The obvious answer 
{{ q.wrongCode }}
{{ q.wrongFig }}
{{ q.wrongUnit }}

{{ q.wrongNote }}

✓ The defensible answer 
{{ q.rightCode }}
{{ q.rightFig }}
{{ q.rightUnit }}

{{ q.rightNote }}

{{ q.why }}
§ 03

Reproduce

PostgreSQL 18 · 325 MB source
STEP 1

Get the data

dados.gov.pt → IMPIC

Two files: contracts and modifications for 2023. Raw files are not in the repo — they exceed GitHub's limit and are republished weekly. SHA-256 hashes pin the exact snapshot.

STEP 2

Load

python python/02_load.py …

Streams the 325 MB JSON one object at a time, keeps the twelve columns the questions need, and writes CSVs for bulk load. Peak memory stays flat.

STEP 3

Ask

psql -f sql/q4_contract_growth.sql

Each question is one file, containing the obvious query, the reason it fails, and the defensible one. Run them in any order.

The full query set, per-table row explorers and downloadable CSVs live in the repository. Code and load scripts on GitHub ↗

Limitations
coverage censoring assumptions

9.86% of contracts are excluded from supplier analysis. 18,930 contracts naming 1.11 bn € of work list a supplier whose identifier is not a nine-digit Portuguese tax number. They concentrate in direct awards — a structural cause, not a random one — and are left out rather than guessed at.

The 8.26% growth figure is a lower bound. Modifications published after the extract are absent, and 820 contracts are reported closed with no effective price — either cancelled before any spending or never filled in. The register does not distinguish the two.

Splitting a contract's value equally between co-suppliers is an assumption. It is stated as one in the query and reported alongside the reading that assumes nothing: contracts won, counted rather than summed.

Only 2023 is loaded. A contract needs time to be modified, so a recent year shows almost no growth. The modification register covers contracts signed in 2023 only.