WITH top_device AS (
  SELECT
    CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS date,
    useragent.wmf_app_version AS app_version,
    event.app_install_id AS install_id,
    SUM(event.totalPages) AS total_pages,
    SUM(event.length) AS total_time,
    COUNT(1) AS n_sessions
  FROM event.MobileWikiAppSessions
  WHERE revision = 18115099
    AND year = ${year} AND month = ${month} AND day = ${day} AND hour = ${hour}
    AND useragent.os_family = 'Android'
    AND useragent.wmf_app_version > '2.7.2'
    AND useragent.wmf_app_version != 'r'
    AND NOT useragent.is_bot
  GROUP BY
    CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')),
    useragent.wmf_app_version, event.app_install_id
  ORDER BY n_sessions DESC
  LIMIT 1
)
SELECT event
FROM top_device
LEFT JOIN event.MobileWikiAppSessions AS sessions ON (
  sessions.event.app_install_id = top_device.install_id
  AND sessions.revision = 18115099
  AND sessions.year = ${year} AND sessions.month = ${month} AND sessions.day = ${day} AND sessions.hour = ${hour}
)
ORDER BY event.client_dt ASC
LIMIT 1000000;