Fetches the PSED Dataverse file (DOI 10.7910/DVN/ZKKOK7, file 3365449), registers it as a DuckDB table, and derives the country-year construct observations the quick_start playbook consumes. Source rows: 1,291 events, 70 countries, 1990–2012 (498 non-empty country-year cells). File is tab-delimited ASCII (despite the legacy claim of comma- delimited). ISO3 country codes are lowercased on derive to match praxis entity_id casing.
{
"dataset_id": "psed_1990_2012",
"delimiter": "\t",
"format": "csv",
"source": "https://dataverse.harvard.edu/api/access/datafile/3365449",
"source_url": "https://dataverse.harvard.edu/api/access/datafile/3365449",
"unit_of_analysis": "event"
}{
"dataset_id": "psed_1990_2012",
"sql": "SELECT\n 'pmsc_event_count' AS construct_id,\n LOWER(\"Country Code\") AS entity_id,\n \"Year\" AS time_value,\n COUNT(*) AS value_numeric,\n 'country-year' AS unit_of_analysis\nFROM dataset\nWHERE \"Country Code\" IS NOT NULL AND \"Year\" BETWEEN 1990 AND 2012\nGROUP BY LOWER(\"Country Code\"), \"Year\"\n"
}{
"dataset_id": "psed_1990_2012",
"sql": "SELECT\n 'pmsc_government_event_count' AS construct_id,\n LOWER(\"Country Code\") AS entity_id,\n \"Year\" AS time_value,\n COUNT(*) AS value_numeric,\n 'country-year' AS unit_of_analysis\nFROM dataset\nWHERE (\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\nGROUP BY LOWER(\"Country Code\"), \"Year\"\n"
}{
"dataset_id": "psed_1990_2012",
"sql": "SELECT\n 'pmsc_rebel_event_count' AS construct_id,\n LOWER(\"Country Code\") AS entity_id,\n \"Year\" AS time_value,\n COUNT(*) AS value_numeric,\n 'country-year' AS unit_of_analysis\nFROM dataset\nWHERE COALESCE(\"Client Types - Rebel\", 0) \u003e 0\n AND \"Country Code\" IS NOT NULL\n AND \"Year\" BETWEEN 1990 AND 2012\nGROUP BY LOWER(\"Country Code\"), \"Year\"\n"
}{
"dataset_id": "psed_1990_2012",
"sql": "SELECT\n 'pmsc_abuse_allegation' AS construct_id,\n LOWER(\"Country Code\") AS entity_id,\n \"Year\" AS time_value,\n COUNT(*) AS value_numeric,\n 'country-year' AS unit_of_analysis\nFROM dataset\nWHERE (\n COALESCE(\"Allegations Of Abuse - Physical\", 0)\n + COALESCE(\"Allegations Of Abuse - Environmental\", 0)\n + COALESCE(\"Allegations Of Abuse - Labor\", 0)\n + COALESCE(\"Allegations Of Abuse - Development\", 0)\n + COALESCE(\"Allegations Of Abuse - Health\", 0)\n) \u003e 0\n AND \"Country Code\" IS NOT NULL\n AND \"Year\" BETWEEN 1990 AND 2012\nGROUP BY LOWER(\"Country Code\"), \"Year\"\n"
}