/*
Isolate data to account by highest level character
Add columns to last logged zone (in #grafana)
*/


select
    zonemap.AreaName_Lang_enUS as Zone,
  COUNT(c.guid) as Count,
  sum( case when ca.spell in (8326, 20584) then 1 else 0 end) as `Dead Players`
from characters c
    join db_areatable_12340 zonemap on zonemap.id = c.zone
    join character_aura ca on c.guid = ca.guid
    where exists (select account from c)
    and name <> ""
    and zone <> 0
    and UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -14 DAY)) > logout_time
    group by zone