title: Get total count of last seven days for top 5 countries of all time
body: <p>I have a table called <code>events</code> which looks like this:</p>
  
  <pre><code> id |    date    | country |  event | count
  --------------------------------------------- 
   1  | 2018-03-16 |   RS    | views  | 33577
   2  | 2018-03-21 |   US    | plays  | 232712
   3  | 2018-03-14 |   AR    | clicks | 469159
   4  | 2018-03-18 |   US    | plays  | 415732
   5  | 2018-03-26 |   US    | views  | 442159
   6  | 2018-03-22 |   BA    | plays  | 181610
   7  | 2018-03-21 |   UK    | plays  | 57899
   8  | 2018-03-17 |   CZ    | views  | 64605
   9  | 2018-03-17 |   BA    | plays  | 375444
   10 | 2018-03-22 |   US    | plays  | 148073
   11 | 2018-03-23 |   BA    | views  | 181644
   12 | 2018-03-22 |   CZ    | clicks | 411953
   13 | 2018-03-15 |   AR    | views  | 329597
   14 | 2018-03-18 |   BA    | clicks | 480834
   15 | 2018-03-17 |   CZ    | plays  | 279060
   16 | 2018-03-19 |   MN    | views  | 611975
   17 | 2018-03-26 |   MN    | clicks | 227115
   18 | 2018-03-24 |   BA    | plays  | 176248
   19 | 2018-03-14 |   UK    | views  | 288072
   20 | 2018-03-18 |   UK    | plays  | 598980
  </code></pre>
  
  <p>I need to get sum of counts of each event for the last seven days of the top 5 countries of all times.
  Currently, I have a query that does that:</p>
  
  <pre><code>SELECT
      `c`.`country`,
      `event`,
      SUM(`count`) AS `total`
  FROM
      `eem_events` `a`
  INNER JOIN(
      SELECT
          `country`
      FROM
          `eem_events` `b`
      GROUP BY
          `country`
      ORDER BY
          SUM(`count`)
      DESC
  LIMIT 5
  ) AS `c`
  WHERE
      `date` &gt; DATE_SUB(NOW(), INTERVAL 7 DAY)
  GROUP BY
      `c`.`country`,
      `event`
  </code></pre>
  
  <p>This query returns this:</p>
  
  <pre><code> country | event  | count 
  -----------------------------
     AR    | views  | 43084323
     AR    | plays  | 46487773
     AR    | clicks | 44581794
     BA    | views  | 43084323
     BA    | plays  | 46487773
     BA    | clicks | 44581794
     DE    | views  | 43084323
     DE    | plays  | 46487773
     DE    | clicks | 44581794
     MN    | views  | 43084323
     MN    | plays  | 46487773
     MN    | clicks | 44581794
     UK    | views  | 43084323
     UK    | plays  | 46487773
     UK    | clicks | 44581794 
  </code></pre>
  
  <p>What I would like to get is following:</p>
  
  <pre><code> country |   views   |   plays   |  clicks   
  -----------------------------------------------
     AR    | 43084323  | 46487773  | 44581794
     BA    | 43084323  | 46487773  | 44581794
     DE    | 43084323  | 46487773  | 44581794
     MN    | 43084323  | 46487773  | 44581794
     UK    | 43084323  | 46487773  | 44581794 
  </code></pre>
  
  <p>I am trying to do this for like 12 hours now and I don't have any more ideas. You can see that for views, plays and clicks there are same values. Could you please help me with refactoring my SQL query to get this result?</p>
  
  <p>I am using MySQL / MariaDB.</p>