# 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 largest fields * 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.