Prepare AR2013 — derive conflict-episode-year observations from the AR2013 COW panel

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'pmsc_competition' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  COALESCE(numgov, 0) AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'civil_war_duration' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  COALESCE(duration, 0) AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT  -- conflict_episode: nominal concept; value_numeric=1 means episode exists this year; entity_id encodes conflict metadata\n  'conflict_episode' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  1 AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'gdp_per_capita' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  lngdp AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND lngdp IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'ethnic_fractionalization' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  lnethfrac AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND lnethfrac IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'ethnic_wars' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  ethnicconflict AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND ethnicconflict IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'conflict_intensity' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  intensity AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND intensity IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'mountainous_terrain' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  lnmntest AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND lnmntest IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'polity' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  polity AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND polity IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'proportion_of_forces' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  lnprop1 AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND lnprop1 IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'support_rebels' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  rsup AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND rsup IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'support_government' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  govsup AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND govsup IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "SELECT\n  'conflict_terminated' AS construct_id,\n  LOWER(CAST(cowcode AS VARCHAR)) || '_'\n    || REPLACE(sidea, ' ', '_') || '_'\n    || REPLACE(sideb, ' ', '_') || '_'\n    || CAST(CAST(start AS INTEGER) AS VARCHAR) AS entity_id,\n  year AS time_value,\n  terminate AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset\nWHERE cowcode IS NOT NULL\n  AND year IS NOT NULL\n  AND terminate IS NOT NULL\n"
}

{
  "dataset_id": "ar2013_cow_conflict_year",
  "sql": "WITH cow_to_iso AS (\n  SELECT ccode_cow, MIN(iso3) AS iso3\n  FROM pax_infrastructure_country_codes_crosswalk__country_codes_crosswalk\n  WHERE iso3 IS NOT NULL\n  GROUP BY ccode_cow\n),\npsed_gov_year AS (\n  SELECT\n    LOWER(\"Country Code\") AS iso3_lower,\n    \"Year\" AS year,\n    COUNT(*) AS gov_event_count\n  FROM pax_private_security_events_database__psed_1990_2012\n  WHERE (\n    COALESCE(\"Client Types - Local Government\", 0)\n    + COALESCE(\"Client Types - National Government\", 0)\n    + COALESCE(\"Client Types - Foreign Government\", 0)\n  ) \u003e 0\n    AND \"Country Code\" IS NOT NULL\n    AND \"Year\" BETWEEN 1990 AND 2012\n  GROUP BY LOWER(\"Country Code\"), \"Year\"\n)\nSELECT\n  'pmsc_government_event_count' AS construct_id,\n  LOWER(CAST(a.cowcode AS VARCHAR)) || '_'\n    || REPLACE(a.sidea, ' ', '_') || '_'\n    || REPLACE(a.sideb, ' ', '_') || '_'\n    || CAST(CAST(a.start AS INTEGER) AS VARCHAR) AS entity_id,\n  a.year AS time_value,\n  COALESCE(p.gov_event_count, 0) AS value_numeric,\n  'conflict-episode-year' AS unit_of_analysis\nFROM dataset a\nLEFT JOIN cow_to_iso c ON a.cowcode = c.ccode_cow\nLEFT JOIN psed_gov_year p ON LOWER(c.iso3) = p.iso3_lower AND a.year = p.year\nWHERE a.cowcode IS NOT NULL AND a.year IS NOT NULL\n"
}