Admiral Stats のデータから、各艦娘をLv99まで育成済みの提督数を調べる SQL

kekkon_yobigun.sh

cat kekkon_yobigun.sql | mysql admiral_stats_development -uroot > kekkon_yobigun.tsv

kekkon_yobigun.sql

SELECT
  sm.ship_type,
  sm.ship_name,
  sm.implemented_at + INTERVAL 9 HOUR AS implemented_at_jst,
  lv99_cnt.cnt
FROM (
  SELECT
    lv99.book_no,
    COUNT(lv99.admiral_id) AS cnt
  FROM (
    SELECT
      ss.admiral_id,
      sm.book_no
    FROM ship_statuses ss
    LEFT JOIN ship_masters sm ON ss.book_no = sm.book_no
    WHERE
      ss.exported_at >= '2017-12-11 15:00:00' AND ss.exported_at < '2018-02-09 15:00:00'
      AND ss.level = 99
      AND sm.remodel_level = 0
    GROUP BY ss.admiral_id, ss.book_no
  ) lv99
  GROUP BY lv99.book_no ORDER BY cnt DESC
) lv99_cnt
LEFT JOIN ship_masters sm ON lv99_cnt.book_no = sm.book_no;