title: MySQL query to rank customer by country
body: <p>My goal is pretty simple: I want to rank my customer by country depending on their bookings number.</p>
  
  <hr>
  
  <p>Here my table:</p>
  
  <p>___BillableDatas</p>
  
  <pre><code>|--------|---------------|------------|----------|
  | BIL_Id | BIL_BookingId | BIL_Date   | BIL_Type |
  |--------|---------------|------------|----------|
  | 1      | 90            | 2017-05-04 | Night    |
  | 2      | 90            | 2017-05-05 | Night    |
  | 3      | 90            | 2017-05-06 | Night    |
  | 4      | 91            | 2017-05-14 | Night    |
  | 5      | 92            | 2017-05-20 | Night    |
  | 6      | 93            | 2017-05-28 | Night    |
  |--------|---------------|------------|----------|
  </code></pre>
  
  <p>___Bookings</p>
  
  <pre><code>|--------|--------------|
  | BOO_Id | BOO_ClientId |
  |--------|--------------|
  | 90     | 30           |
  | 91     | 31           |
  | 92     | 32           |
  | 93     | 33           |
  |--------|--------------|
  </code></pre>
  
  <p>___Kardex</p>
  
  <pre><code>|--------|---------------------------|
  | KDX_Id | KDX_PostalAddress_Country |
  |--------|---------------------------|
  | 30     | FR                        |
  | 31     | CA                        |
  | 32     | CA                        |
  | 33     | ES                        |
  |--------|---------------------------|
  </code></pre>
  
  <p>___CountryList</p>
  
  <pre><code>|----------|---------|
  | CTY_Code | CTY_en  |
  |----------|---------|
  | FR       | France  |
  | CA       | Canada  |
  | ES       | Spain   |
  |----------|---------|
  </code></pre>
  
  <hr>
  
  <p>I would like something like that for the period between <code>2017-05-01</code> to <code>2017-05-30</code>:</p>
  
  <pre><code>|------|---------|----------|-----------|------------|-------------|
  | Rank | Country | night_nb | night_pct | booking_nb | booking_pct |
  |------|---------|----------|-----------|------------|-------------|
  | 1    | Canada  | 2        | 33.33     | 2          | 50          |
  | 2    | France  | 3        | 50.00     | 1          | 25          |
  | 3    | Espagne | 1        | 16.66     | 1          | 25          |
  |------|---------|----------|-----------|------------|-------------|
  </code></pre>
  
  <hr>
  
  <p>Here my attempted code:</p>
  
  <pre><code>SELECT CTY_Code, CTY_en
     , night_nb 
     , 100*night_nb/@sum_night_nb AS night_pct
     , booking_nb 
     , 100*booking_nb/@sum_booking_nb AS booking_pct
  FROM     (
       SELECT     CTY_Code, CTY_en
                , @sum_night_nb := count(BIL_Id) night_nb
                , @sum_booking_nb := count(distinct BIL_BookingId) booking_nb
       FROM       ___CountryList cty 
       INNER JOIN ___Kardex kdx
               ON KDX_PostalAddress_Country = CTY_Code
       LEFT JOIN  (___Bookings boo
       INNER JOIN ___BillableDatas bil
               ON BIL_BookingId = BOO_Id
              AND BIL_Type = "Night")
               ON KDX_Id = BOO_ClientId 
              AND BIL_Date between "2017-05-01" AND "2017-05-30"
       GROUP BY   CTY_en with rollup
       ) base
  WHERE CTY_en is not null
  ORDER BY booking_nb DESC
  , CTY_en ASC
  </code></pre>