questions.md

# 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.