Romanian Diacritics Analytics.sql

-- From the Visibility Data
 select event_country,
        sum(if(event_issues = 0, 1, 0)) as 'No Issues',
        sum(if(event_issues = 1, 1, 0)) as 'Issues with ş',
        sum(if(event_issues = 2, 1, 0)) as 'Issues with ţ',
        sum(if(event_issues = 3, 1, 0)) as 'Issues with both',
        count(*) as 'Total'
   from DiacriticsVisibility_15606222
  group by event_country
;


-- From the Poll data
 select event_country,
        sum(if(lower(trim(event_text)) = 'arșiță', 1, 0)) as 'No Issues',
        sum(if(lower(trim(event_text)) = 'arşiță', 1, 0)) as 'Issues with ş',
        sum(if(lower(trim(event_text)) = 'arșiţă', 1, 0)) as 'Issues with ţ',
        sum(if(lower(trim(event_text)) = 'arşiţă', 1, 0)) as 'Issues with both',
        sum(if( (
                  instr(event_text, 'ș')
                + instr(event_text, 'ț')
                ) > 0
            and (
                  instr(event_text, 'ş')
                + instr(event_text, 'ţ')
                ) <= 0
            and (lower(trim(cast(event_text as char))) <> 'arsita'), 1, 0)
        ) as 'Wrong word, correct diacritics',
        sum(if( (
                  instr(event_text, 'ș')
                + instr(event_text, 'ț')
                ) <= 0
            and (
                  instr(event_text, 'ş')
                + instr(event_text, 'ţ')
                ) > 0
            and (lower(trim(cast(event_text as char))) <> 'arsita'), 1, 0)
        ) as 'Wrong word, incorrect diacritics',
        sum(if(
                (
                    (
                      instr(event_text, 'ș')
                    + instr(event_text, 'ţ')
                    ) > 0
                    and
                    (
                      instr(event_text, 'ş')
                    + instr(event_text, 'ț')
                    ) > 0
                )
            and (lower(trim(cast(event_text as char))) <> 'arsita'), 1, 0)
        ) as 'Wrong word, mixed diacritics',
        count(*) as 'Total'

   from DiacriticsPoll_15630673
  where timestamp > '20160601000000'
  group by event_country
;