SELECT
  tn.account_id,
  global_account_number,
  site_id,
  sippeer_id,
  SUM(IF(uc_type <> 'Seats' AND is_tollfree <> 1 AND latest >= '2017-07-25', 1, 0)) AS DidUC_active,
  SUM(IF(uc_type <> 'Seats' AND is_tollfree <> 1 AND latest < '2017-07-25', 1, 0))  AS DidUC_disconnected,
  SUM(IF(uc_type <> 'Seats' AND is_tollfree = 1 AND latest >= '2017-07-25', 1, 0))  AS DidUCTF_active,
  SUM(IF(uc_type <> 'Seats' AND is_tollfree = 1 AND latest < '2017-07-25', 1, 0))   AS DidUCTF_disconnected,
  SUM(IF(uc_usage_id = 2 AND latest >= '2017-07-25', 1, 0))                         AS SeatUC250_active,
  SUM(IF(uc_usage_id = 2 AND latest < '2017-07-25', 1, 0))                          AS SeatUC250_disconnected,
  SUM(IF(uc_usage_id = 4 AND latest >= '2017-07-25', 1, 0))                         AS SeatUC500_active,
  SUM(IF(uc_usage_id = 4 AND latest < '2017-07-25', 1, 0))                          AS SeatUC500_disconnected,
  SUM(IF(uc_usage_id = 5 AND latest >= '2017-07-25', 1, 0))                         AS SeatUC1000_active,
  SUM(IF(uc_usage_id = 5 AND latest < '2017-07-25', 1, 0))                          AS SeatUC1000_disconnected,
  SUM(IF(cnam_enabled = '1' AND latest >= '2017-07-25', 1, 0))                      AS CnamFeature_active,
  SUM(IF(cnam_enabled = '1' AND latest < '2017-07-25', 1, 0))                       AS CnamFeature_disconnected
FROM (SELECT
        fullnumber,
        account_id,
        MAX(to_date) AS latest
      FROM tn_history
      WHERE to_date >= CAST('2017-07-24' AS DATETIME(3)) AND from_date < CAST('2017-07-25' AS DATETIME(3)) AND uc_type IS NOT NULL
      GROUP BY fullnumber, account_id) AS subselect
  JOIN tn_history AS tn ON subselect.fullnumber = tn.fullnumber AND subselect.account_id = tn.account_id AND subselect.latest = tn.to_date
  LEFT JOIN account ac ON ac.id = tn.account_id
GROUP BY account_id, site_id, sippeer_id;