<commit_before><% content_for(:page_title, 'Leaderboard') %>

<table class="table table-striped table-bordered">
  <thead class="table-header">
    <th rowspan="2">Organisation</th>
    <th rowspan="2">Sites</th>
    <th colspan="3">Mappings
      <tr>
        <th>Total</th>
        <th>Unresolved</th>
        <th>Errors</th>
      </tr>
    </th>
  </thead>
  <tbody>
    <% Organisation.all.each do |org| %>
    <tr>
      <td><%= org.title %></td>
      <% @sites = Site.where(organisation_id: org.id) %>
      <td><%= @sites.count %></td>
      <% all_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM mappings, sites
                          WHERE mappings.site_id = sites.id
                          AND sites.organisation_id = #{org.id};").first %>
      <td><%= all_mappings.first %></td>
      <% unresolved_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM mappings, sites
                                WHERE mappings.site_id = sites.id
                                AND mappings.type = 'unresolved'
                                AND sites.organisation_id = #{org.id};").first %>
      <td><%= unresolved_mappings.first %></td>
      <% erroring_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM hits, mappings, sites
                                                                    WHERE mappings.site_id = sites.id
                                                                    AND hits.http_status = '404'
                                                                    AND hits.mapping_id = mappings.id
                                                                    AND sites.organisation_id = #{org.id};").first %>
      <td><%= erroring_mappings.first %></td>
    </tr>
    <% end %>
  </tbody>
</table><commit_msg>Use SQL's INNER JOIN instead of listing multiple tables in the FROM<commit_after><% content_for(:page_title, 'Leaderboard') %>

<table class="table table-striped table-bordered">
  <thead class="table-header">
    <th rowspan="2">Organisation</th>
    <th rowspan="2">Sites</th>
    <th colspan="3">Mappings
      <tr>
        <th>Total</th>
        <th>Unresolved</th>
        <th>Errors</th>
      </tr>
    </th>
  </thead>
  <tbody>
    <% Organisation.all.each do |org| %>
    <tr>
      <td><%= org.title %></td>
      <% @sites = Site.where(organisation_id: org.id) %>
      <td><%= @sites.count %></td>
      <% all_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM mappings
                          INNER JOIN sites ON mappings.site_id = sites.id
                          AND sites.organisation_id = #{org.id};").first %>
      <td><%= all_mappings.first %></td>
      <% unresolved_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM mappings
                                  INNER JOIN sites ON mappings.site_id = sites.id
                                  AND mappings.type = 'unresolved'
                                  AND sites.organisation_id = #{org.id};").first %>
      <td><%= unresolved_mappings.first %></td>
      <% erroring_mappings = ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM hits
                                                                    INNER JOIN mappings ON mappings.id = hits.mapping_id
                                                                    AND hits.http_status = '404'
                                                                    AND hits.mapping_id = mappings.id
                                                                    INNER JOIN sites ON sites.organisation_id = #{org.id}
                                                                    AND mappings.site_id = sites.id;").first %>
      <td><%= erroring_mappings.first %></td>
    </tr>
    <% end %>
  </tbody>
</table>
{'fraction_non_alphanumeric': 0.13615023474178403, 'fraction_numerical': 0.003129890453834116, 'mean_word_length': 1.477390180878553, 'pattern_counts': {'":': 0, '<': 91, '<?xml version=': 0, '>': 91, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 29, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4994248', 'n_tokens_mistral': 1078, 'n_tokens_neox': 986, 'n_words': 211}