Core questions (no hcat)

What are the average field sizes of each country in the baltics?
How many fields are there in Latvia?
What's the total area of devoted to agriculture in Latvia?
Is the average field size larger in lithuania or latvia?
How many fields are there that are under 1 hectare?
Show a map with the 10 largestfields
What percent of fields are under 2 hectares?
Show a map with the largest field in Estonia
Show a map with the ten largest fields
Which country has the most area covered by fields?
what is the average field size of the largest 10 percent of fields?
what percent of fields are over 20 hectares?
how big on average are the largest 20% of fields?
can you print me a table that calculates deciles of field area?
can you print me a table that shows the average field area by decile?

More coding / prompting needed

Maps with more

Show a map with the largest fields seems to have a limit of 100, but nothing shows up

Percent of total area

What precent of Latvia is used for agriculture? (just need to put in total area of latvia, etc. somewhere)

Quantiles / deciles

Can you make a table with quantiles / deciles of the field sizes?

SELECT NTILE(10) OVER (ORDER BY area) AS decile, MIN(area) AS min_size, MAX(area) AS max_size, AVG(area) AS avg_size FROM crops GROUP BY decile ORDER BY decile;

GROUP BY clause cannot contain window functions!

need to teach the right duckdb calculation for this.

Graphs / charts

Show a chart of field size by decile/quantile
Show a chart of field size by decile, with the most common crop for that decile (hcat)

Admin 2 level questions

Need to pre-process admin 2 names for each row.

How many fields are there in each county of Estonia?
What state/county has the highest percent of its land as agriculture?

hcat / crop questions

Show a map with the ten largest sugar beet fields
What are the top ten crops by area for Lithuania?
What are the top ten crops by number of fields for Lithuania?
What are the top ten crops that have a field size over 10 hectares in the baltics?

sometimes gets this TODO: teach manual sum of rows for 'number of fields' and field count', 'what are the most common crops'

What is the percent of wheat in the baltics?
what percent of latvia agricultural area is corn?
what is the average field size of corn in latvia?
what crop has the smallest average field size in latvia?
what are the ten crops with the smallest average field sizes in latvia?
what are the ten crops with the smallest average field sizes (with at least 20 fields) in latvia?
What percent of latvia is strawberries?
what are the ten crops with the largest average field sizes (with at least 20 fields) in latvia?
how many fields plant vetches in Latvia?
how many fields of corn are there in each of the baltic states?
what's the total area of corn in each of the baltic states?
what percent of lithuania is corn?
what is the average field size for wheat in the baltics?
What is the most common crop on fields over 5 hectares?
what is the most common crop on fields over 10 hectares in estonia?
What percent of sugar beet fields are over 10 hectares?
- 45.74898785425101
what are the ten most common crops by number of fields?
What are the top 5 flowers in the baltics?
what are the top 5 legumes by field count in the baltics?
What are the top 5 legumes in the baltics?
what are the average field sizes of peas in the baltics?
what are the average field sizes of beans in the baltics?
what percent of estonia is not fallow or pasture?
What percent of latvia is fallow?
what are the sizes of strawberry fields by quantile in latvia?
what are the sizes of wheat fields by quantile in latvia?

More coding / prompting needed

this is using the percent of the country, not the percent of the fields

what is the percent of wheat in each country?

SELECT collection, SUM(area) / 4346727 * 100 AS percent_wheat FROM crops WHERE crop_type IN ('common_soft_wheat', 'durum_hard_wheat') GROUP BY collection; - should use percent of the country, not the total area. Also this doesn't return any results. * What are the average field sizes of the top ten crops by area?
Tried to teach it about not using windowed functions like row_number(), but seems like we need to explicitly train it for this type of query like we did for quantiles.

Which country has the largest area of arable crops (crop code starts with 3301)?
Which country has the largest area of grassland (crop code starts with 3302)?
Which country has the largest area of Permanent perenniel crops (crop code starts with 3303)? What is the most unique crop type for each country?

returned the values with the crop_type for each one.

What percent of sugar in the baltics is sugar beet?

Ideas needing more coding

show a chart or a graph
Natural language processing of responses, particularly when there's only one result.
- decide on what to use of map, table, graph, answer
- give more common names in output - ie clean up some of the weird quirks of eurocrops.- multi-step analysis, like get complex results from each country and then compare / analyze- pre-process country level stats
  - total area, total perimeter
  - by crop stats - total area, total percent of fields, total percent of overall land
- add admin 2 (state) level attribute to each field
- update the table when starting up? Save the new table?
- do admin 2 level stats like country level ones- geospatial queries (should wait for duckdb 1.1 support)
- like bounding box / polygon- joins with environmental data, etc.