Every Fix Moved the League Table

Some questions should take one line of SQL. Which country exports the most farm produce, and who comes second, is one of them.

The table is ag_exports, from AgriSphere, an invented set of farm production and market files that LucidPlexus uses as practice data. It holds 103 rows of exports by country, commodity and year, 2015 to 2022, with faults planted on purpose so that learners have something to find. The countries are real. Every number is made up, so nothing below is a fact about real trade. That makes it a fair test, though: the faults are the ordinary kind, the ones that arrive in any spreadsheet that has passed through a few inboxes.

So, the one-line answer:

SELECT Country, SUM(Export_tons) AS tons
FROM ag_exports
GROUP BY Country
ORDER BY tons DESC;

Further down the full result come names the master list has never heard of: the USA has entered under more than one name, a tactic that would get you thrown out of most pub quizzes, and so has China. On the face of it, Brazil is second.

Nobody would publish that. But the instinct, to tidy the names and move on, skips the step that makes an audit worth doing. Before fixing anything, count each fault and see which answer it moves. Some faults are cosmetic. Others decide the result.

Count before you fix

Start with exact copies, rows identical in every column. The test is a difference of two counts:

SELECT COUNT(*) - COUNT(DISTINCT (Country, Commodity, Year, Export_tons))
         AS exact_copies
FROM ag_exports;

It returns 3: one copy each of a Brazilian soybean row, a Brazilian rice row and an Indian corn row, about 226,000 tonnes counted twice, about 170,000 of them Brazil’s. Then the names, checked against the table that says which countries exist, country_master. An anti-join, a LEFT JOIN that keeps only the rows with no match, finds every spelling the master list doesn’t know:

SELECT e.Country, SUM(e.Export_tons) AS tons
FROM ag_exports AS e
LEFT JOIN country_master AS m ON m.country_name = e.Country
WHERE m.country_name IS NULL
GROUP BY e.Country
ORDER BY tons DESC;

Here’s the number that shows why the anti-join comes first. An ordinary inner join to the master list, the obvious way to “validate” the countries, would have dropped every one of those rows, silently: about 344,000 tonnes gone, no error, and Brazil still second. Last, the values that can’t be true:

SELECT Country, Commodity, Year, Export_tons
FROM ag_exports
WHERE Export_tons <= 0 OR Year IS NULL
ORDER BY Export_tons;

That’s a negative export of 4,500 tonnes, two exports of exactly nothing, and two Brazilian rows with no year. The other tables have their own collection, and FILTER lets one pass count several checks at once. On the prices:

SELECT COUNT(*) - COUNT(DISTINCT (Commodity, Date, Price_per_ton)) AS exact_copies,
       COUNT(*) FILTER (WHERE Date IS NULL)                         AS no_date,
       COUNT(*) FILTER (WHERE Price_per_ton > 1000)                 AS over_1000,
       COUNT(DISTINCT Commodity)                                    AS spellings
FROM ag_prices;

It finds one copied row, two prices ten times higher than the rest, some prices with no date, and soybean spelt three ways. (How many have no date is one of the counts the guided project asks you for, so we’ll leave that number there.) Here is every fault in the four tables, found the same way, with the answer each one moves:

Of those, the technology row deserves a second look. Three farms typed their efficiency gain as a percentage (27, 8 and 19) in a column of fractions (0.27, 0.08, 0.19). Those three cells lift the average gain from 17.1% to 70.6%, and they make irrigation systems look like the second-best technology at 84.2%. Fixed, irrigation is the worst of the four, at 14.5%. One farm’s decimal point was carrying a whole product category.

Fix one thing at a time, and watch

From here, the fixes for ag_exports go in a view, so the raw table stays as it arrived. A small mapping table gives every spelling its standard name, and a LEFT JOIN to it keeps any spelling the mapping has never seen, where a test can catch it:

CREATE TABLE blog_aliases AS
SELECT * FROM (VALUES ('USA', 'USA'), ('U.S.A.', 'USA'),
                      ('United States', 'USA'), ('China', 'China'),
                      ('china', 'China'), ('Brazil', 'Brazil'),
                      ('India', 'India')) AS t(alias, country);

CREATE VIEW blog_clean AS
SELECT DISTINCT
       COALESCE(a.country, e.Country) AS country,
       e.Commodity                    AS commodity,
       e.Year                         AS year,
       e.Export_tons                  AS tons
FROM ag_exports AS e
LEFT JOIN blog_aliases AS a ON a.alias = e.Country
WHERE e.Year IS NOT NULL
  AND e.Export_tons > 0;

That’s four decisions in one statement. Applied one at a time, each moves the league table:

Step by step: standardising the names adds the USA’s spellings together into about 1.37 million tonnes and puts it second. Removing the copies costs Brazil about 170,000 tonnes but not its third place. Removing the impossible values raises the total, because one of them was negative. And the two rows with no year, about 174,000 tonnes, both Brazilian, decide on their own whether Brazil or India is third. Dropping a row because its year is missing is a judgement, not a fact: the tonnes were presumably shipped, just not in any year anyone wrote down. That judgement comes back later.

After the view, the total is just under 5.0 million tonnes, down from 5,394,894 as delivered, and every fault planted in the table is gone. (The exact cleaned total is the answer to the first task of the Can I Trust This Data? guided project, so we’ll leave it for you to find.) It’s tempting to call the table clean.

The fault nobody planted

One check is left, and it’s the one that matters most: the key. The dataset’s own description says one row is one country’s exports of one commodity in one year. If that’s true, no country, commodity and year turns up on more than one row:

SELECT COUNT(*) AS contested_keys, SUM(n) AS rows_in_them, MAX(n) AS most_rows
FROM (SELECT country, commodity, year, COUNT(*) AS n
      FROM blog_clean GROUP BY ALL HAVING COUNT(*) > 1);

Twenty-five of them do, with between two and four rows each, 59 rows in all. DISTINCT can’t touch them, because they aren’t copies; they disagree. The dataset’s notes on LucidPlexus say these shared keys were in the rows as supplied, before a single fault was planted. Someone built the table this way. To see how far apart the rows are, fold each contested key into a list. Here is the USA’s 2016:

SELECT commodity, list(tons ORDER BY tons) AS competing_rows
FROM blog_clean
WHERE country = 'USA' AND year = 2016
GROUP BY commodity
HAVING COUNT(*) > 1
ORDER BY commodity;

So what is the USA’s rice export for 2016: 4,207, 4,541, 28,664 or 84,151 tonnes? If the rows are separate shipments, add them up. If they’re competing versions of one figure (a revision, a second source, an estimate keyed twice), one of them is right and nobody has said which. Each reading is a rule, and each rule is a query:

WITH per_key AS (
  SELECT country, commodity, year,
         SUM(tons) AS every_row,
         MAX(tons) AS largest,
         MIN(tons) AS smallest
  FROM blog_clean
  GROUP BY ALL
)
SELECT country,
       SUM(every_row) AS every_row,
       SUM(largest)   AS largest_wins,
       SUM(smallest)  AS smallest_wins
FROM per_key
GROUP BY country
ORDER BY every_row DESC;

Read along the USA’s row (tonnes rounded to the nearest thousand). Count every row and it’s second, with about 1.37 million tonnes. Let the largest row of each key win and it’s still second. Let the smallest win and it falls to about 635,000 tonnes, behind India and Brazil.

The USA is the second-biggest exporter in this table.
Or the smallest.

There’s a fourth defensible rule, too: take the mean of a key’s competing rows, on the view that each is a noisy measure of the same thing. Add the other open decision, whether the two rows with no year count, and there are eight ways to finish the cleaning: two choices about the missing years, four about the contested keys (every row, the largest, the smallest, the mean). Each produces a league table.

In every one of them, China is first. Below it, the USA, India and Brazil each take second, third and fourth place somewhere. The planted faults moved the answer, but each had one right fix. This one doesn’t: the whole lower half of the league is a choice the data can’t make.

Who decides what a row is?

Looking back, the spellings, the copies, the minus sign and the decimal point were the easy part. Each had one correct fix, and a query could find it. The shared keys are different in kind. Only someone who knows how the table was made can say whether two rows for US rice in 2016 are two ships or two guesses, and until they do, every chart of this table has quietly chosen a league table on their behalf.

Which is fine, as long as someone can still be asked. But suppose the person who built the table has moved on, and nobody kept a note. Which of the eight league tables do you publish?