orange_people_view (view)

>10,000 rows count all

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: gender, citizenship_2_b, occupation, level2_main_occ, bigperiod_birth, bigperiod_death, birth_place_source, death_place_source, birth_place_choice_reason, death_place_choice_reason

Next page

CREATE VIEW orange_people_view AS
SELECT
  wikidata_code,
  name,
  gender,

  -- normalized birth/death years
  CASE WHEN birth_min IS NULL THEN NULL
       WHEN typeof(birth_min) = 'text' AND length(birth_min) >= 4 AND substr(birth_min,5,1) IN ('-','T')
         THEN CAST(substr(birth_min,1,4) AS INTEGER)
       ELSE CAST(birth_min AS INTEGER)
  END AS birth_year_min,

  CASE WHEN birth_max IS NULL THEN NULL
       WHEN typeof(birth_max) = 'text' AND length(birth_max) >= 4 AND substr(birth_max,5,1) IN ('-','T')
         THEN CAST(substr(birth_max,1,4) AS INTEGER)
       ELSE CAST(birth_max AS INTEGER)
  END AS birth_year_max,

  CASE WHEN death_min IS NULL THEN NULL
       WHEN typeof(death_min) = 'text' AND length(death_min) >= 4 AND substr(death_min,5,1) IN ('-','T')
         THEN CAST(substr(death_min,1,4) AS INTEGER)
       ELSE CAST(death_min AS INTEGER)
  END AS death_year_min,

  CASE WHEN death_max IS NULL THEN NULL
       WHEN typeof(death_max) = 'text' AND length(death_max) >= 4 AND substr(death_max,5,1) IN ('-','T')
         THEN CAST(substr(death_max,1,4) AS INTEGER)
       ELSE CAST(death_max AS INTEGER)
  END AS death_year_max,

  -- places & contextual fields
  birth_place_readable      AS birth_place,
  death_place_readable      AS death_place,

  -- nationality / country fields
  string_citizenship_raw_d  AS country_raw,
  citizenship_1_b,
  citizenship_2_b,

  -- occupation / periods
  level1_main_occ           AS occupation,
  level2_main_occ,
  level3_main_occ,
  bigperiod_birth,
  bigperiod_death,

  -- identifier
  curid,

  -- (kept before coords/sources) any helper or other fields you want earlier can go here

  -- coordinates and provenance (moved towards the right)
  birth_place_lat,
  birth_place_lon,
  birth_place_source,
  death_place_lat,
  death_place_lon,
  death_place_source,

  -- choice reasons moved to the far right
  birth_place_choice_reason,
  death_place_choice_reason,

  -- DuckDuckGo helper columns moved to the absolute right
  replace(replace(coalesce(name,''), '&', 'and'), ' ', '+') AS ddg_query,
  'https://duckduckgo.com/?q=' || replace(replace(coalesce(name,''), '&', 'and'), ' ', '+') AS ddg_search_url

FROM orange_places;