<commit_before>{
  "topics": "SELECT indicators.id AS indicator_id, topics.id AS topic_id, topicTranslations.name AS topic_name, indicators.preferable_tendency, indicators.last_update, indicators.starred, indicatorTranslations.name AS indicator_name, indicatorTranslations.description AS indicator_description FROM indicators INNER JOIN topics ON indicators.topic_id = topics.id INNER JOIN topicTranslations ON topics.id = topicTranslations.topic_id AND topicTranslations.lang_code = '{0}' INNER JOIN indicatorTranslations ON indicators.id = indicatorTranslations.indicator_id AND indicatorTranslations.lang_code = '{0}'",
  "countries": "SELECT c.id AS country_id, c.faoURI AS faoURI, c.iso3, c.iso2, r1.name AS country_name, regions.id AS region_id, r2.name as region_name, regions.un_code, (SELECT count(1) FROM observations INNER JOIN countries ON observations.region_id = countries.id  WHERE countries.id = c.id) AS data FROM countries c INNER JOIN regionTranslations r1 ON c.id = r1.region_id AND r1.lang_code = '{0}' INNER JOIN regions ON c.is_part_of_id = regions.id INNER JOIN regionTranslations r2 ON regions.id = r2.region_id AND r2.lang_code = '{0}' ORDER BY region_name, country_name",
  "observations_by_region": "SELECT DISTINCT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value, ( SELECT vals2.value FROM observations obs1 INNER JOIN landportal.values as vals2 ON vals2.id = obs1.value_id INNER JOIN intervals inter2 ON inter2.id = obs1.ref_time_id WHERE inter2.value < ref_time_value AND obs1.indicator_id = ind_id AND vals2.value IS NOT NULL AND obs1.region_id = obs.region_id ORDER BY inter2.value DESC LIMIT 1 ) as previous_value , organizations.id AS organization_id, organizations.name as organization_name, r.un_code AS continent, r2.name AS continent_name FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN regions r ON r.id = c1.is_part_of_id INNER JOIN regionTranslations r2 ON r.id = r2.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN dataset_indicator ON ind.id = dataset_indicator.indicator_id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id INNER JOIN organizations ON organizations.id = datasources.organization_id WHERE {1} ind.id = '{2}' AND r1.lang_code = '{0}' AND r2.lang_code = '{0}' AND i1.lang_code ='{0}' AND vals.value IS NOT NULL",
  "observations_by_region_avg": "SELECT AVG(vals.value) AS value, intervals.value AS ref_time FROM observations AS obs INNER JOIN landportal.values AS vals ON obs.value_id = vals.id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN countries ON obs.region_id = countries.id INNER JOIN regions ON regions.id = countries.is_part_of_id WHERE obs.indicator_id = '{1}' {0} GROUP BY ref_time",
  "observations_by_country": "SELECT DISTINCT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value, orgs.id AS organization_id, orgs.name AS organization_name FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN dataset_indicator ON dataset_indicator.indicator_id = ind.id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id INNER JOIN organizations as orgs ON orgs.id = datasources.organization_id WHERE c1.iso3 = '{1}' AND ind.id = '{2}' AND r1.lang_code = '{0}' AND i1.lang_code = '{0}' AND vals.value IS NOT NULL",
  "observations_by_country_starred": "SELECT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id WHERE c1.iso3 = '{1}' AND ind.starred = true AND vals.value IS NOT NULL AND i1.lang_code = '{0}' AND r1.lang_code = '{0}'",
  "continents": "SELECT reg.id AS continent_id, reg.un_code AS continent_un_code, t1.name AS continent_name FROM regions reg INNER JOIN regionTranslations t1 ON t1.region_id = reg.id WHERE reg.is_part_of_id = 1 AND t1.lang_code = '{0}'",
  "datasources": "SELECT distinct(ind.id) as ind_id, dat.id as dat_id, dat.name as dat_name, dat.organization_id, ind.starred, ind.preferable_tendency, ind.last_update, t1.name as ind_name, t1.description as ind_description FROM datasources AS dat INNER JOIN datasets ON datasets.datasource_id = dat.id INNER JOIN dataset_indicator ON dataset_indicator.dataset_id = datasets.id INNER JOIN indicators ind ON ind.id = dataset_indicator.indicator_id INNER JOIN indicatorTranslations t1 ON t1.indicator_id = ind.id WHERE t1.lang_code = '{0}'",
  "datasources_by_country": "SELECT distinct(ind.id) as ind_id, dat.id as dat_id, dat.name as dat_name, dat.organization_id, ind.starred, ind.preferable_tendency, ind.last_update, t1.name as ind_name, t1.description as ind_description, (SELECT count(1) FROM observations INNER JOIN countries ON observations.region_id = countries.id INNER JOIN landportal.values ON observations.value_id = values.id WHERE countries.iso3 = 'AFG' AND landportal.values.value IS NOT NULL AND observations.indicator_id = ind.id) AS data FROM datasources AS dat INNER JOIN datasets ON datasets.datasource_id = dat.id INNER JOIN dataset_indicator ON dataset_indicator.dataset_id = datasets.id INNER JOIN indicators ind ON ind.id = dataset_indicator.indicator_id INNER JOIN indicatorTranslations t1 ON t1.indicator_id = ind.id WHERE t1.lang_code = '{0}'",
  "country": "SELECT countries.id, countries.taxonomy_id, countries.iso2, countries.iso3, countries.faoURI, t1.name as country_name, t2.name as region_name, regions.id as region_id, regions.un_code FROM countries INNER JOIN regions ON regions.id = countries.is_part_of_id INNER JOIN regionTranslations t1 ON t1.region_id = countries.id INNER JOIN regionTranslations t2 ON t2.region_id = countries.is_part_of_id WHERE t1.lang_code = '{0}' AND t2.lang_code = '{0}' AND countries.iso3 ='{1}'",
  "countries_without_region": "SELECT countries.id, countries.iso2, countries.iso3, countries.faoURI, t1.name AS country_name, (SELECT count(1) FROM observations WHERE observations.region_id = countries.id) AS data, countries.is_part_of_id FROM countries INNER JOIN regionTranslations t1 ON t1.region_id = countries.id WHERE t1.lang_code =  '{0}' ORDER BY country_name",
  "country_chart_indicators": "SELECT obs.id, inter1.value AS ref_time_value, countries.iso3, countries.iso2, countries.faoURI, regionTranslations.name AS country_name, ind.id AS ind_id, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, ind.starred AS ind_starred, indicatorTranslations.name AS ind_name, indicatorTranslations.description AS ind_description, vals1.value AS value FROM observations obs INNER JOIN intervals inter1 ON inter1.id = obs.ref_time_id INNER JOIN countries ON countries.id = obs.region_id INNER JOIN regionTranslations ON regionTranslations.region_id = countries.id INNER JOIN landportal.values vals1 ON vals1.id = obs.value_id INNER JOIN indicators ind ON ind.id = obs.indicator_id INNER JOIN indicatorTranslations ON indicatorTranslations.indicator_id = ind.id INNER JOIN (SELECT obs2.indicator_id, MAX( inter2.value) AS max_val FROM observations obs2 INNER JOIN intervals inter2 ON obs2.ref_time_id = inter2.id INNER JOIN landportal.values vals2 ON obs2.value_id = vals2.id WHERE vals2.value IS NOT NULL GROUP BY obs2.indicator_id ) sub ON (obs.indicator_id = sub.indicator_id AND inter1.value = max_val) WHERE countries.iso3 = '{1}' AND obs.indicator_id IN ({2}) AND vals1.value IS NOT NULL AND regionTranslations.lang_code = '{0}' AND indicatorTranslations.lang_code = '{0}'",
  "starred_indicators": "SELECT ind.preferable_tendency, ind.last_update, ind.topic_id, ind.starred, trans.description, trans.name, ind.id, trans2.name as topic_name FROM indicators ind INNER JOIN indicatorTranslations trans ON trans.indicator_id = ind.id INNER JOIN topicTranslations trans2 ON trans2.topic_id = ind.topic_id WHERE trans.lang_code = '{0}' AND trans2.lang_code = '{0}' AND ind.starred = TRUE",
  "organizations": "SELECT id, name, url FROM organizations",
  "organization": "SELECT id, name, url, description FROM organizations INNER JOIN organizationTranslations ON organizations.id = organizationTranslations.organization_id WHERE id =  '{0}' AND organizationTranslations.lang_code =  '{1}'",
  "indicators_by_organization": "SELECT DISTINCT(ind.id), ind.last_update, trans.name, trans.description, munit.name AS measurement_unit FROM indicators ind INNER JOIN indicatorTranslations trans ON trans.indicator_id = ind.id INNER JOIN measurementUnits munit ON munit.id = ind.measurement_unit_id INNER JOIN dataset_indicator di ON di.indicator_id = ind.id INNER JOIN datasets ON datasets.id = di.dataset_id INNER JOIN datasources ON datasets.datasource_id = datasources.id WHERE trans.lang_code = '{0}' AND datasources.organization_id = '{1}'",
  "topics_and_indicators_by_region": "SELECT DISTINCT(ind.id) AS ind_id, obs.id, inter1.value AS ref_time_value, ind.last_update AS ind_last_update, indicatorTranslations.name AS ind_name, vals1.value AS value, topicTranslations.name AS topic_name, topicTranslations.topic_id AS topic_id, organizations.name AS organization_name, organizations.id AS organization_id FROM observations obs INNER JOIN intervals inter1 ON inter1.id = obs.ref_time_id INNER JOIN countries ON countries.id = obs.region_id INNER JOIN landportal.values vals1 ON vals1.id = obs.value_id INNER JOIN indicators ind ON ind.id = obs.indicator_id INNER JOIN indicatorTranslations ON indicatorTranslations.indicator_id = ind.id INNER JOIN topicTranslations ON topicTranslations.topic_id = ind.topic_id INNER JOIN dataset_indicator ON dataset_indicator.indicator_id = ind.id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasets.datasource_id = datasources.id INNER JOIN organizations ON organizations.id = datasources.organization_id INNER JOIN (SELECT obs2.indicator_id, MAX( inter2.value) AS max_val FROM observations obs2 INNER JOIN intervals inter2 ON obs2.ref_time_id = inter2.id INNER JOIN landportal.values vals2 ON obs2.value_id = vals2.id WHERE vals2.value IS NOT NULL GROUP BY obs2.indicator_id) sub ON (obs.indicator_id = sub.indicator_id AND inter1.value = max_val) WHERE countries.iso3 = '{1}' AND vals1.value IS NOT NULL AND indicatorTranslations.lang_code = '{0}' AND topicTranslations.lang_code = '{0}'",
  "continent_name": "SELECT name FROM regions INNER join regionTranslations ON regions.id = regionTranslations.region_id WHERE regions.un_code = {1} AND regionTranslations.lang_code = '{0}'",
  "indicator_source": "SELECT DISTINCT datasources.organization_id, datasources.name as organization_name FROM indicators INNER JOIN dataset_indicator ON indicators.id = dataset_indicator.indicator_id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id WHERE indicators.id = '{0}'",
  "all_countries": "SELECT DISTINCT c.id AS country_id, c.faoURI AS faoURI, c.iso3, c.iso2, r1.name AS country_name, regions.id AS region_id, r2.name as region_name, regions.un_code FROM countries c INNER JOIN regionTranslations r1 ON c.id = r1.region_id AND r1.lang_code = '{0}' INNER JOIN regions ON c.is_part_of_id = regions.id INNER JOIN regionTranslations r2 ON regions.id = r2.region_id AND r2.lang_code = '{0}' ORDER BY region_name, country_name",
  "indicator_average": "select AVG(landportal.values.value) avg from indicators inner join observations on observations.indicator_id = indicators.id inner join landportal.values on observations.value_id = landportal.values.id where indicators.id = '{0}' and landportal.values.value is not null group by indicators.id",
  "last_observation_not_null_by_country_and_indicator": "SELECT observations.id, observations.indicator_id, landportal.values.value value FROM observations INNER JOIN regions ON observations.region_id = regions.id INNER JOIN countries ON countries.id = regions.id INNER JOIN landportal.values ON observations.value_id = landportal.values.id INNER JOIN intervals ON observations.ref_time_id = intervals.id WHERE observations.indicator_id =  '{1}' AND landportal.values.value IS NOT NULL AND countries.iso3 =  '{0}' ORDER BY intervals.value DESC  LIMIT 1",
  "pref_tendency_indicator": "SELECT preferable_tendency FROM indicators WHERE indicators.id = '{0}'",
  "indicator_name": "SELECT name, description, indicators.preferable_tendency FROM indicatorTranslations INNER JOIN indicators ON indicator_id = indicators.id WHERE indicator_id = '{1}' and lang_code = '{0}'",
  "indicators": "SELECT indicators.id AS indicator_id, indicators.preferable_tendency, indicators.last_update, indicators.starred, indicatorTranslations.name AS indicator_name, indicatorTranslations.description AS indicator_description FROM indicators INNER JOIN indicatorTranslations ON indicators.id = indicatorTranslations.indicator_id AND indicatorTranslations.lang_code = '{0}' ORDER BY indicator_name",
  "indicators_by_ids": "SELECT indicatorTranslations.name as name, indicators.id as id FROM indicators INNER JOIN indicatorTranslations on indicatorTranslations.indicator_id = indicators.id WHERE indicatorTranslations.lang_code = '{0}' AND indicators.id in ({1})",
  "indicator_description": "SELECT trans.description as description FROM indicators INNER JOIN indicatorTranslations trans ON trans.indicator_id = indicators.id WHERE id = '{1}' AND trans.lang_code = '{0}'"
}<commit_msg>Fix source query<commit_after>{
  "topics": "SELECT indicators.id AS indicator_id, topics.id AS topic_id, topicTranslations.name AS topic_name, indicators.preferable_tendency, indicators.last_update, indicators.starred, indicatorTranslations.name AS indicator_name, indicatorTranslations.description AS indicator_description FROM indicators INNER JOIN topics ON indicators.topic_id = topics.id INNER JOIN topicTranslations ON topics.id = topicTranslations.topic_id AND topicTranslations.lang_code = '{0}' INNER JOIN indicatorTranslations ON indicators.id = indicatorTranslations.indicator_id AND indicatorTranslations.lang_code = '{0}'",
  "countries": "SELECT c.id AS country_id, c.faoURI AS faoURI, c.iso3, c.iso2, r1.name AS country_name, regions.id AS region_id, r2.name as region_name, regions.un_code, (SELECT count(1) FROM observations INNER JOIN countries ON observations.region_id = countries.id  WHERE countries.id = c.id) AS data FROM countries c INNER JOIN regionTranslations r1 ON c.id = r1.region_id AND r1.lang_code = '{0}' INNER JOIN regions ON c.is_part_of_id = regions.id INNER JOIN regionTranslations r2 ON regions.id = r2.region_id AND r2.lang_code = '{0}' ORDER BY region_name, country_name",
  "observations_by_region": "SELECT DISTINCT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value, ( SELECT vals2.value FROM observations obs1 INNER JOIN landportal.values as vals2 ON vals2.id = obs1.value_id INNER JOIN intervals inter2 ON inter2.id = obs1.ref_time_id WHERE inter2.value < ref_time_value AND obs1.indicator_id = ind_id AND vals2.value IS NOT NULL AND obs1.region_id = obs.region_id ORDER BY inter2.value DESC LIMIT 1 ) as previous_value , organizations.id AS organization_id, organizations.name as organization_name, r.un_code AS continent, r2.name AS continent_name FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN regions r ON r.id = c1.is_part_of_id INNER JOIN regionTranslations r2 ON r.id = r2.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN dataset_indicator ON ind.id = dataset_indicator.indicator_id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id INNER JOIN organizations ON organizations.id = datasources.organization_id WHERE {1} ind.id = '{2}' AND r1.lang_code = '{0}' AND r2.lang_code = '{0}' AND i1.lang_code ='{0}' AND vals.value IS NOT NULL",
  "observations_by_region_avg": "SELECT AVG(vals.value) AS value, intervals.value AS ref_time FROM observations AS obs INNER JOIN landportal.values AS vals ON obs.value_id = vals.id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN countries ON obs.region_id = countries.id INNER JOIN regions ON regions.id = countries.is_part_of_id WHERE obs.indicator_id = '{1}' {0} GROUP BY ref_time",
  "observations_by_country": "SELECT DISTINCT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value, orgs.id AS organization_id, orgs.name AS organization_name FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN dataset_indicator ON dataset_indicator.indicator_id = ind.id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id INNER JOIN organizations as orgs ON orgs.id = datasources.organization_id WHERE c1.iso3 = '{1}' AND ind.id = '{2}' AND r1.lang_code = '{0}' AND i1.lang_code = '{0}' AND vals.value IS NOT NULL",
  "observations_by_country_starred": "SELECT obs.id AS obs_id, r1.name AS country_name, c1.iso2, c1.iso3, c1.faoURI, ind.id AS ind_id, ind.starred AS ind_starred, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, i1.name AS ind_name, i1.description AS ind_description, intervals.value AS ref_time_value, vals.value FROM observations obs INNER JOIN countries c1 ON obs.region_id = c1.id INNER JOIN regionTranslations r1 ON c1.id = r1.region_id INNER JOIN indicators ind ON obs.indicator_id = ind.id INNER JOIN indicatorTranslations i1 ON ind.id = i1.indicator_id INNER JOIN intervals ON obs.ref_time_id = intervals.id INNER JOIN landportal.values as vals ON obs.value_id = vals.id WHERE c1.iso3 = '{1}' AND ind.starred = true AND vals.value IS NOT NULL AND i1.lang_code = '{0}' AND r1.lang_code = '{0}'",
  "continents": "SELECT reg.id AS continent_id, reg.un_code AS continent_un_code, t1.name AS continent_name FROM regions reg INNER JOIN regionTranslations t1 ON t1.region_id = reg.id WHERE reg.is_part_of_id = 1 AND t1.lang_code = '{0}'",
  "datasources": "SELECT distinct(ind.id) as ind_id, dat.id as dat_id, dat.name as dat_name, dat.organization_id, ind.starred, ind.preferable_tendency, ind.last_update, t1.name as ind_name, t1.description as ind_description FROM datasources AS dat INNER JOIN datasets ON datasets.datasource_id = dat.id INNER JOIN dataset_indicator ON dataset_indicator.dataset_id = datasets.id INNER JOIN indicators ind ON ind.id = dataset_indicator.indicator_id INNER JOIN indicatorTranslations t1 ON t1.indicator_id = ind.id WHERE t1.lang_code = '{0}'",
  "datasources_by_country": "SELECT distinct(ind.id) as ind_id, dat.id as dat_id, dat.name as dat_name, dat.organization_id, ind.starred, ind.preferable_tendency, ind.last_update, t1.name as ind_name, t1.description as ind_description, (SELECT count(1) FROM observations INNER JOIN countries ON observations.region_id = countries.id INNER JOIN landportal.values ON observations.value_id = values.id WHERE countries.iso3 = '{1}' AND landportal.values.value IS NOT NULL AND observations.indicator_id = ind.id) AS data FROM datasources AS dat INNER JOIN datasets ON datasets.datasource_id = dat.id INNER JOIN dataset_indicator ON dataset_indicator.dataset_id = datasets.id INNER JOIN indicators ind ON ind.id = dataset_indicator.indicator_id INNER JOIN indicatorTranslations t1 ON t1.indicator_id = ind.id WHERE t1.lang_code = '{0}'",
  "country": "SELECT countries.id, countries.taxonomy_id, countries.iso2, countries.iso3, countries.faoURI, t1.name as country_name, t2.name as region_name, regions.id as region_id, regions.un_code FROM countries INNER JOIN regions ON regions.id = countries.is_part_of_id INNER JOIN regionTranslations t1 ON t1.region_id = countries.id INNER JOIN regionTranslations t2 ON t2.region_id = countries.is_part_of_id WHERE t1.lang_code = '{0}' AND t2.lang_code = '{0}' AND countries.iso3 ='{1}'",
  "countries_without_region": "SELECT countries.id, countries.iso2, countries.iso3, countries.faoURI, t1.name AS country_name, (SELECT count(1) FROM observations WHERE observations.region_id = countries.id) AS data, countries.is_part_of_id FROM countries INNER JOIN regionTranslations t1 ON t1.region_id = countries.id WHERE t1.lang_code =  '{0}' ORDER BY country_name",
  "country_chart_indicators": "SELECT obs.id, inter1.value AS ref_time_value, countries.iso3, countries.iso2, countries.faoURI, regionTranslations.name AS country_name, ind.id AS ind_id, ind.last_update AS ind_last_update, ind.preferable_tendency AS ind_preferable_tendency, ind.starred AS ind_starred, indicatorTranslations.name AS ind_name, indicatorTranslations.description AS ind_description, vals1.value AS value FROM observations obs INNER JOIN intervals inter1 ON inter1.id = obs.ref_time_id INNER JOIN countries ON countries.id = obs.region_id INNER JOIN regionTranslations ON regionTranslations.region_id = countries.id INNER JOIN landportal.values vals1 ON vals1.id = obs.value_id INNER JOIN indicators ind ON ind.id = obs.indicator_id INNER JOIN indicatorTranslations ON indicatorTranslations.indicator_id = ind.id INNER JOIN (SELECT obs2.indicator_id, MAX( inter2.value) AS max_val FROM observations obs2 INNER JOIN intervals inter2 ON obs2.ref_time_id = inter2.id INNER JOIN landportal.values vals2 ON obs2.value_id = vals2.id WHERE vals2.value IS NOT NULL GROUP BY obs2.indicator_id ) sub ON (obs.indicator_id = sub.indicator_id AND inter1.value = max_val) WHERE countries.iso3 = '{1}' AND obs.indicator_id IN ({2}) AND vals1.value IS NOT NULL AND regionTranslations.lang_code = '{0}' AND indicatorTranslations.lang_code = '{0}'",
  "starred_indicators": "SELECT ind.preferable_tendency, ind.last_update, ind.topic_id, ind.starred, trans.description, trans.name, ind.id, trans2.name as topic_name FROM indicators ind INNER JOIN indicatorTranslations trans ON trans.indicator_id = ind.id INNER JOIN topicTranslations trans2 ON trans2.topic_id = ind.topic_id WHERE trans.lang_code = '{0}' AND trans2.lang_code = '{0}' AND ind.starred = TRUE",
  "organizations": "SELECT id, name, url FROM organizations",
  "organization": "SELECT id, name, url, description FROM organizations INNER JOIN organizationTranslations ON organizations.id = organizationTranslations.organization_id WHERE id =  '{0}' AND organizationTranslations.lang_code =  '{1}'",
  "indicators_by_organization": "SELECT DISTINCT(ind.id), ind.last_update, trans.name, trans.description, munit.name AS measurement_unit FROM indicators ind INNER JOIN indicatorTranslations trans ON trans.indicator_id = ind.id INNER JOIN measurementUnits munit ON munit.id = ind.measurement_unit_id INNER JOIN dataset_indicator di ON di.indicator_id = ind.id INNER JOIN datasets ON datasets.id = di.dataset_id INNER JOIN datasources ON datasets.datasource_id = datasources.id WHERE trans.lang_code = '{0}' AND datasources.organization_id = '{1}'",
  "topics_and_indicators_by_region": "SELECT DISTINCT(ind.id) AS ind_id, obs.id, inter1.value AS ref_time_value, ind.last_update AS ind_last_update, indicatorTranslations.name AS ind_name, vals1.value AS value, topicTranslations.name AS topic_name, topicTranslations.topic_id AS topic_id, organizations.name AS organization_name, organizations.id AS organization_id FROM observations obs INNER JOIN intervals inter1 ON inter1.id = obs.ref_time_id INNER JOIN countries ON countries.id = obs.region_id INNER JOIN landportal.values vals1 ON vals1.id = obs.value_id INNER JOIN indicators ind ON ind.id = obs.indicator_id INNER JOIN indicatorTranslations ON indicatorTranslations.indicator_id = ind.id INNER JOIN topicTranslations ON topicTranslations.topic_id = ind.topic_id INNER JOIN dataset_indicator ON dataset_indicator.indicator_id = ind.id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasets.datasource_id = datasources.id INNER JOIN organizations ON organizations.id = datasources.organization_id INNER JOIN (SELECT obs2.indicator_id, MAX( inter2.value) AS max_val FROM observations obs2 INNER JOIN intervals inter2 ON obs2.ref_time_id = inter2.id INNER JOIN landportal.values vals2 ON obs2.value_id = vals2.id WHERE vals2.value IS NOT NULL GROUP BY obs2.indicator_id) sub ON (obs.indicator_id = sub.indicator_id AND inter1.value = max_val) WHERE countries.iso3 = '{1}' AND vals1.value IS NOT NULL AND indicatorTranslations.lang_code = '{0}' AND topicTranslations.lang_code = '{0}'",
  "continent_name": "SELECT name FROM regions INNER join regionTranslations ON regions.id = regionTranslations.region_id WHERE regions.un_code = {1} AND regionTranslations.lang_code = '{0}'",
  "indicator_source": "SELECT DISTINCT datasources.organization_id, datasources.name as organization_name FROM indicators INNER JOIN dataset_indicator ON indicators.id = dataset_indicator.indicator_id INNER JOIN datasets ON datasets.id = dataset_indicator.dataset_id INNER JOIN datasources ON datasources.id = datasets.datasource_id WHERE indicators.id = '{0}'",
  "all_countries": "SELECT DISTINCT c.id AS country_id, c.faoURI AS faoURI, c.iso3, c.iso2, r1.name AS country_name, regions.id AS region_id, r2.name as region_name, regions.un_code FROM countries c INNER JOIN regionTranslations r1 ON c.id = r1.region_id AND r1.lang_code = '{0}' INNER JOIN regions ON c.is_part_of_id = regions.id INNER JOIN regionTranslations r2 ON regions.id = r2.region_id AND r2.lang_code = '{0}' ORDER BY region_name, country_name",
  "indicator_average": "select AVG(landportal.values.value) avg from indicators inner join observations on observations.indicator_id = indicators.id inner join landportal.values on observations.value_id = landportal.values.id where indicators.id = '{0}' and landportal.values.value is not null group by indicators.id",
  "last_observation_not_null_by_country_and_indicator": "SELECT observations.id, observations.indicator_id, landportal.values.value value FROM observations INNER JOIN regions ON observations.region_id = regions.id INNER JOIN countries ON countries.id = regions.id INNER JOIN landportal.values ON observations.value_id = landportal.values.id INNER JOIN intervals ON observations.ref_time_id = intervals.id WHERE observations.indicator_id =  '{1}' AND landportal.values.value IS NOT NULL AND countries.iso3 =  '{0}' ORDER BY intervals.value DESC  LIMIT 1",
  "pref_tendency_indicator": "SELECT preferable_tendency FROM indicators WHERE indicators.id = '{0}'",
  "indicator_name": "SELECT name, description, indicators.preferable_tendency FROM indicatorTranslations INNER JOIN indicators ON indicator_id = indicators.id WHERE indicator_id = '{1}' and lang_code = '{0}'",
  "indicators": "SELECT indicators.id AS indicator_id, indicators.preferable_tendency, indicators.last_update, indicators.starred, indicatorTranslations.name AS indicator_name, indicatorTranslations.description AS indicator_description FROM indicators INNER JOIN indicatorTranslations ON indicators.id = indicatorTranslations.indicator_id AND indicatorTranslations.lang_code = '{0}' ORDER BY indicator_name",
  "indicators_by_ids": "SELECT indicatorTranslations.name as name, indicators.id as id FROM indicators INNER JOIN indicatorTranslations on indicatorTranslations.indicator_id = indicators.id WHERE indicatorTranslations.lang_code = '{0}' AND indicators.id in ({1})",
  "indicator_description": "SELECT trans.description as description FROM indicators INNER JOIN indicatorTranslations trans ON trans.indicator_id = indicators.id WHERE id = '{1}' AND trans.lang_code = '{0}'"
}
{'fraction_non_alphanumeric': 0.07984045205251787, 'fraction_numerical': 0.015456207412331727, 'mean_word_length': 5.8642482318047, 'pattern_counts': {'":': 54, '<': 5, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4045879', 'n_tokens_mistral': 9648, 'n_tokens_neox': 9298, 'n_words': 2990}