{"queries":[{"id":"farmers","compiledQueryString":"SELECT * FROM farmers;","inputQueryString":"SELECT * FROM farmers;","compiled":false,"inline":false},{"id":"visits","compiledQueryString":"SELECT * FROM visits;","inputQueryString":"SELECT * FROM visits;","compiled":false,"inline":false},{"id":"summary","compiledQueryString":"SELECT \n COUNT(DISTINCT farmer_name) as total_farmers,\n COUNT(DISTINCT village) as total_villages,\n COUNT(DISTINCT district) as total_districts,\n SUM(land_area) as total_area,\n ROUND(AVG(last_year_yield), 2) as avg_yield,\n ROUND(AVG(price_per_ton), 2) as avg_price,\n ROUND(SUM((last_year_yield / 1000) * price_per_ton), 2) as total_revenue\nFROM farmers;","inputQueryString":"SELECT \n COUNT(DISTINCT farmer_name) as total_farmers,\n COUNT(DISTINCT village) as total_villages,\n COUNT(DISTINCT district) as total_districts,\n SUM(land_area) as total_area,\n ROUND(AVG(last_year_yield), 2) as avg_yield,\n ROUND(AVG(price_per_ton), 2) as avg_price,\n ROUND(SUM((last_year_yield / 1000) * price_per_ton), 2) as total_revenue\nFROM farmers;","compiled":false,"inline":false},{"id":"ownership_distribution","compiledQueryString":"SELECT \n ownership_type as name,\n COUNT(*) as value,\nFROM farmers\nGROUP BY ownership_type;","inputQueryString":"SELECT \n ownership_type as name,\n COUNT(*) as value,\nFROM farmers\nGROUP BY ownership_type;","compiled":false,"inline":true},{"id":"land_area_distribution","compiledQueryString":"SELECT \n district,\n village,\n ROUND(SUM(land_area)/24, 2) as total_area_feddan\nFROM farmers\nGROUP BY district, village\nORDER BY district, village;","inputQueryString":"SELECT \n district,\n village,\n ROUND(SUM(land_area)/24, 2) as total_area_feddan\nFROM farmers\nGROUP BY district, village\nORDER BY district, village;","compiled":false,"inline":true},{"id":"land_ownership_by_age","compiledQueryString":"WITH age_ranges AS (\n SELECT \n CASE \n WHEN age < 21 THEN '< 21'\n WHEN age BETWEEN 21 AND 30 THEN '21-30'\n WHEN age BETWEEN 31 AND 40 THEN '31-40'\n WHEN age BETWEEN 41 AND 50 THEN '41-50'\n WHEN age BETWEEN 51 AND 60 THEN '51-60'\n ELSE '> 60'\n END as age_range,\n ownership_type,\n ROUND(SUM(land_area)/24, 2) as total_area_feddan,\n COUNT(*) as farmer_count\n FROM farmers\n GROUP BY 1, 2\n)\nSELECT \n age_range,\n ownership_type,\n total_area_feddan,\n farmer_count\nFROM age_ranges\nORDER BY \n CASE age_range\n WHEN '< 21' THEN 1\n WHEN '21-30' THEN 2\n WHEN '31-40' THEN 3\n WHEN '41-50' THEN 4\n WHEN '51-60' THEN 5\n WHEN '> 60' THEN 6\n END,\n ownership_type;","inputQueryString":"WITH age_ranges AS (\n SELECT \n CASE \n WHEN age < 21 THEN '< 21'\n WHEN age BETWEEN 21 AND 30 THEN '21-30'\n WHEN age BETWEEN 31 AND 40 THEN '31-40'\n WHEN age BETWEEN 41 AND 50 THEN '41-50'\n WHEN age BETWEEN 51 AND 60 THEN '51-60'\n ELSE '> 60'\n END as age_range,\n ownership_type,\n ROUND(SUM(land_area)/24, 2) as total_area_feddan,\n COUNT(*) as farmer_count\n FROM farmers\n GROUP BY 1, 2\n)\nSELECT \n age_range,\n ownership_type,\n total_area_feddan,\n farmer_count\nFROM age_ranges\nORDER BY \n CASE age_range\n WHEN '< 21' THEN 1\n WHEN '21-30' THEN 2\n WHEN '31-40' THEN 3\n WHEN '41-50' THEN 4\n WHEN '51-60' THEN 5\n WHEN '> 60' THEN 6\n END,\n ownership_type;","compiled":false,"inline":true},{"id":"detailed_yield_by_village","compiledQueryString":"WITH yield_ranges AS (\n SELECT \n village,\n CASE \n WHEN last_year_yield < 500 THEN '< 500'\n WHEN last_year_yield BETWEEN 500 AND 1000 THEN '500-1000'\n WHEN last_year_yield BETWEEN 1001 AND 1500 THEN '1001-1500'\n WHEN last_year_yield BETWEEN 1501 AND 2000 THEN '1501-2000'\n ELSE '> 2000'\n END as yield_range,\n COUNT(*) as farmer_count,\n ROUND(AVG(last_year_yield), 2) as avg_yield_kg\n FROM farmers\n GROUP BY \n village,\n CASE \n WHEN last_year_yield < 500 THEN '< 500'\n WHEN last_year_yield BETWEEN 500 AND 1000 THEN '500-1000'\n WHEN last_year_yield BETWEEN 1001 AND 1500 THEN '1001-1500'\n WHEN last_year_yield BETWEEN 1501 AND 2000 THEN '1501-2000'\n ELSE '> 2000'\n END\n)\nSELECT * FROM yield_ranges\nORDER BY \n village,\n CASE yield_range\n WHEN '< 500' THEN 1\n WHEN '500-1000' THEN 2\n WHEN '1001-1500' THEN 3\n WHEN '1501-2000' THEN 4\n ELSE 5\n END;","inputQueryString":"WITH yield_ranges AS (\n SELECT \n village,\n CASE \n WHEN last_year_yield < 500 THEN '< 500'\n WHEN last_year_yield BETWEEN 500 AND 1000 THEN '500-1000'\n WHEN last_year_yield BETWEEN 1001 AND 1500 THEN '1001-1500'\n WHEN last_year_yield BETWEEN 1501 AND 2000 THEN '1501-2000'\n ELSE '> 2000'\n END as yield_range,\n COUNT(*) as farmer_count,\n ROUND(AVG(last_year_yield), 2) as avg_yield_kg\n FROM farmers\n GROUP BY \n village,\n CASE \n WHEN last_year_yield < 500 THEN '< 500'\n WHEN last_year_yield BETWEEN 500 AND 1000 THEN '500-1000'\n WHEN last_year_yield BETWEEN 1001 AND 1500 THEN '1001-1500'\n WHEN last_year_yield BETWEEN 1501 AND 2000 THEN '1501-2000'\n ELSE '> 2000'\n END\n)\nSELECT * FROM yield_ranges\nORDER BY \n village,\n CASE yield_range\n WHEN '< 500' THEN 1\n WHEN '500-1000' THEN 2\n WHEN '1001-1500' THEN 3\n WHEN '1501-2000' THEN 4\n ELSE 5\n END;","compiled":false,"inline":true},{"id":"gender_distribution","compiledQueryString":"SELECT \n gender as name,\n COUNT(*) as value,\nFROM farmers\nGROUP BY gender;","inputQueryString":"SELECT \n gender as name,\n COUNT(*) as value,\nFROM farmers\nGROUP BY gender;","compiled":false,"inline":true},{"id":"gender_range_distribution","compiledQueryString":"SELECT\n CASE \n WHEN age < 21 THEN '< 21'\n WHEN age BETWEEN 21 AND 30 THEN '21-30'\n WHEN age BETWEEN 31 AND 40 THEN '31-40'\n WHEN age BETWEEN 41 AND 50 THEN '41-50'\n WHEN age BETWEEN 51 AND 60 THEN '51-60'\n ELSE '> 60'\n END as gender_range,\n COUNT(*) as value,\nFROM farmers\nGROUP BY gender_range\nORDER BY gender_range desc;","inputQueryString":"SELECT\n CASE \n WHEN age < 21 THEN '< 21'\n WHEN age BETWEEN 21 AND 30 THEN '21-30'\n WHEN age BETWEEN 31 AND 40 THEN '31-40'\n WHEN age BETWEEN 41 AND 50 THEN '41-50'\n WHEN age BETWEEN 51 AND 60 THEN '51-60'\n ELSE '> 60'\n END as gender_range,\n COUNT(*) as value,\nFROM farmers\nGROUP BY gender_range\nORDER BY gender_range desc;","compiled":false,"inline":true},{"id":"district_village_yield","compiledQueryString":"WITH yield_ranks AS (\n SELECT \n district,\n village,\n ROUND(SUM(last_year_yield) / 1000, 3) as total_yield_tons,\n ROW_NUMBER() OVER (PARTITION BY district ORDER BY SUM(last_year_yield) DESC) as rank_in_district\n FROM farmers\n GROUP BY district, village\n)\nSELECT \n district,\n village,\n total_yield_tons\nFROM yield_ranks\nORDER BY district, total_yield_tons DESC;","inputQueryString":"WITH yield_ranks AS (\n SELECT \n district,\n village,\n ROUND(SUM(last_year_yield) / 1000, 3) as total_yield_tons,\n ROW_NUMBER() OVER (PARTITION BY district ORDER BY SUM(last_year_yield) DESC) as rank_in_district\n FROM farmers\n GROUP BY district, village\n)\nSELECT \n district,\n village,\n total_yield_tons\nFROM yield_ranks\nORDER BY district, total_yield_tons DESC;","compiled":false,"inline":true},{"id":"district_village_revenue","compiledQueryString":"SELECT \n district,\n village,\n -- Convert yield to tons (divide by 1000) then multiply by price per ton\n ROUND(SUM(last_year_yield * price_per_ton / 1000) / 1000000, 3) as revenue_million_egp,\n COUNT(*) as farmer_count\nFROM farmers\nGROUP BY district, village\nORDER BY district, revenue_million_egp DESC;","inputQueryString":"SELECT \n district,\n village,\n -- Convert yield to tons (divide by 1000) then multiply by price per ton\n ROUND(SUM(last_year_yield * price_per_ton / 1000) / 1000000, 3) as revenue_million_egp,\n COUNT(*) as farmer_count\nFROM farmers\nGROUP BY district, village\nORDER BY district, revenue_million_egp DESC;","compiled":false,"inline":true},{"id":"rent_hierarchy","compiledQueryString":"SELECT \n district,\n village,\n ROUND(AVG(annual_rent / (land_area / 24)), 2) as avg_rent_per_feddan_egp,\n COUNT(*) as farmer_count,\n SUM(annual_rent) as total_rent_egp\nFROM farmers\nWHERE annual_rent > 0 -- Exclude non-renting farmers\nGROUP BY district, village\nORDER BY district, village;","inputQueryString":"SELECT \n district,\n village,\n ROUND(AVG(annual_rent / (land_area / 24)), 2) as avg_rent_per_feddan_egp,\n COUNT(*) as farmer_count,\n SUM(annual_rent) as total_rent_egp\nFROM farmers\nWHERE annual_rent > 0 -- Exclude non-renting farmers\nGROUP BY district, village\nORDER BY district, village;","compiled":false,"inline":true},{"id":"farmers_list","compiledQueryString":"SELECT DISTINCT(farmer_name) FROM visits;","inputQueryString":"SELECT DISTINCT(farmer_name) FROM visits;","compiled":false,"inline":true},{"id":"visits_for_farmer","compiledQueryString":"WITH visit_columns AS (\n SELECT unnest(array[1, 2, 3, 4, 5, 6, 7, 8]) AS visit_number\n),\nvisit_data AS (\n SELECT \n f.farmer_name,\n f.village,\n f.crop,\n f.planting_date,\n f.planted_area,\n f.preparation,\n vc.visit_number,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_date\n WHEN 2 THEN second_visit_date\n WHEN 3 THEN third_visit_date\n WHEN 4 THEN fourth_visit_date\n WHEN 5 THEN fifth_visit_date\n WHEN 6 THEN sixth_visit_date\n WHEN 7 THEN seventh_visit_date\n WHEN 8 THEN eighth_visit_date\n END as current_visit_date,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_observations\n WHEN 2 THEN second_visit_observations\n WHEN 3 THEN third_visit_observations\n WHEN 4 THEN fourth_visit_observations\n WHEN 5 THEN fifth_visit_observations\n WHEN 6 THEN sixth_visit_observations\n WHEN 7 THEN seventh_visit_observations\n WHEN 8 THEN eighth_visit_observations\n END as observations,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_recommendations\n WHEN 2 THEN second_visit_recommendations\n WHEN 3 THEN third_visit_recommendations\n WHEN 4 THEN fourth_visit_recommendations\n WHEN 5 THEN fifth_visit_recommendations\n WHEN 6 THEN sixth_visit_recommendations\n WHEN 7 THEN seventh_visit_recommendations\n WHEN 8 THEN eighth_visit_recommendations\n END as recommendations,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_overall_plant_health\n WHEN 2 THEN second_visit_overall_plant_health\n WHEN 3 THEN third_visit_overall_plant_health\n WHEN 4 THEN fourth_visit_overall_plant_health\n WHEN 5 THEN fifth_visit_overall_plant_health\n WHEN 6 THEN sixth_visit_overall_plant_health\n WHEN 7 THEN seventh_visit_overall_plant_health\n WHEN 8 THEN eighth_visit_overall_plant_health\n END as plant_health\n FROM visits f\n CROSS JOIN visit_columns vc\n)\n\nSELECT \n village,\n crop,\n 'Visit ' || visit_number as visit_number,\n current_visit_date as visit_date,\n observations,\n recommendations,\n plant_health,\n planting_date,\n planted_area,\n preparation,\n -- Calculate days since planting, handling different date formats\n CASE \n WHEN current_visit_date != '' AND current_visit_date IS NOT NULL THEN \n CAST(DATEDIFF('day', \n CAST(STRPTIME(REPLACE(planting_date, '-', '/'), '%Y/%m/%d') AS DATE),\n CAST(current_visit_date AS DATE)\n ) AS INTEGER)\n END as days_since_planting,\n -- Calculate days since last visit using window function\n CASE \n WHEN current_visit_date != '' AND current_visit_date IS NOT NULL THEN\n COALESCE(\n CAST(DATEDIFF('day', \n LAG(CAST(current_visit_date AS DATE)) OVER (PARTITION BY farmer_name ORDER BY current_visit_date),\n CAST(current_visit_date AS DATE)\n ) AS INTEGER),\n 0\n )\n END as days_since_last_visit\nFROM visit_data\nWHERE observations != '' \n AND farmer_name = '${inputs.farmer_name.value}'\nORDER BY current_visit_date;","inputQueryString":"WITH visit_columns AS (\n SELECT unnest(array[1, 2, 3, 4, 5, 6, 7, 8]) AS visit_number\n),\nvisit_data AS (\n SELECT \n f.farmer_name,\n f.village,\n f.crop,\n f.planting_date,\n f.planted_area,\n f.preparation,\n vc.visit_number,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_date\n WHEN 2 THEN second_visit_date\n WHEN 3 THEN third_visit_date\n WHEN 4 THEN fourth_visit_date\n WHEN 5 THEN fifth_visit_date\n WHEN 6 THEN sixth_visit_date\n WHEN 7 THEN seventh_visit_date\n WHEN 8 THEN eighth_visit_date\n END as current_visit_date,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_observations\n WHEN 2 THEN second_visit_observations\n WHEN 3 THEN third_visit_observations\n WHEN 4 THEN fourth_visit_observations\n WHEN 5 THEN fifth_visit_observations\n WHEN 6 THEN sixth_visit_observations\n WHEN 7 THEN seventh_visit_observations\n WHEN 8 THEN eighth_visit_observations\n END as observations,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_recommendations\n WHEN 2 THEN second_visit_recommendations\n WHEN 3 THEN third_visit_recommendations\n WHEN 4 THEN fourth_visit_recommendations\n WHEN 5 THEN fifth_visit_recommendations\n WHEN 6 THEN sixth_visit_recommendations\n WHEN 7 THEN seventh_visit_recommendations\n WHEN 8 THEN eighth_visit_recommendations\n END as recommendations,\n CASE vc.visit_number\n WHEN 1 THEN first_visit_overall_plant_health\n WHEN 2 THEN second_visit_overall_plant_health\n WHEN 3 THEN third_visit_overall_plant_health\n WHEN 4 THEN fourth_visit_overall_plant_health\n WHEN 5 THEN fifth_visit_overall_plant_health\n WHEN 6 THEN sixth_visit_overall_plant_health\n WHEN 7 THEN seventh_visit_overall_plant_health\n WHEN 8 THEN eighth_visit_overall_plant_health\n END as plant_health\n FROM visits f\n CROSS JOIN visit_columns vc\n)\n\nSELECT \n village,\n crop,\n 'Visit ' || visit_number as visit_number,\n current_visit_date as visit_date,\n observations,\n recommendations,\n plant_health,\n planting_date,\n planted_area,\n preparation,\n -- Calculate days since planting, handling different date formats\n CASE \n WHEN current_visit_date != '' AND current_visit_date IS NOT NULL THEN \n CAST(DATEDIFF('day', \n CAST(STRPTIME(REPLACE(planting_date, '-', '/'), '%Y/%m/%d') AS DATE),\n CAST(current_visit_date AS DATE)\n ) AS INTEGER)\n END as days_since_planting,\n -- Calculate days since last visit using window function\n CASE \n WHEN current_visit_date != '' AND current_visit_date IS NOT NULL THEN\n COALESCE(\n CAST(DATEDIFF('day', \n LAG(CAST(current_visit_date AS DATE)) OVER (PARTITION BY farmer_name ORDER BY current_visit_date),\n CAST(current_visit_date AS DATE)\n ) AS INTEGER),\n 0\n )\n END as days_since_last_visit\nFROM visit_data\nWHERE observations != '' \n AND farmer_name = '${inputs.farmer_name.value}'\nORDER BY current_visit_date;","compiled":false,"inline":true}]}

Soybean - 2023 Season - Menia, Egypt

Project Information