SELECT
  id, uuid, timestamp, userAgent, wiki
FROM MobileWikiAppShareAFact_12588711
WHERE timestamp >= '20180201'
  AND wiki RLIKE '^WikipediaApp'

library(tidyverse)

mobile_schemas <- wmf::mysql_read("SHOW TABLES;", "log") %>%
  filter(grepl("^MobileWikiApp[A-Za-z]+_[0-9]+$", Tables_in_log))

query <- "SELECT
  wiki RLIKE '^WikipediaApp' AS malformed,
  COUNT(*) AS events
FROM {schema}
WHERE timestamp >= '20180101'
GROUP BY malformed;"
malformations <- lapply(mobile_schemas$Tables_in_log, function(schema) {
  result <- try(wmf::mysql_read(glue::glue(query), "log"))
  if (inherits(result, "try-error")) return(NULL)
  else {
    if (all(c(0, 1) %in% result$malformed)) {
      counts <- tidyr::spread(result, malformed, events)
      output <- c(
        events = counts$`1`,
        proportion = counts$`1` / (counts$`0` + counts$`1`)
      )
      return(output)
    } else {
      return(data.frame(
        events = if_else(result$malformed[1] == 0, 0, result$events[1]),
        proportion = if_else(result$malformed[1] == 0, 0, 1.0)
      ))
    }
  }
}) %>% set_names(mobile_schemas$Tables_in_log) %>% do.call(rbind, .)

malformations$schema <- row.names(malformations)
malformations %>%
  mutate(
    affected = if_else(events > 0, "★", ""),
    proportion = sprintf("%.2f%%", proportion * 100)
  ) %>%
  select(affected, schema, events, proportion) %>%
  knitr::kable(format = "markdown", align = c("c", "l", "r", "r"))