sql


Count how many submissions per score

Recently, we had access to a database that contained the scores of a programming competitions system.
The database contained several contests, each contest contained several challenges and any competitor could make multiple submissions.
We wanted to extract a couple of charts showing

  • how many submissions we had per score and
  • how many submissions we had per score while filtering out the best submission (max score) per contestant per challenge per contest

The following code will return the number of submissions per score per challenge per contest.

SELECT contest_id, challenge_id, TRUNCATE(score, 1), COUNT(*)
FROM submissions
GROUP BY contest_id, challenge_id, TRUNCATE(score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(score, 1);

The next one will return the number of submissions per score per challenge per contest while filtering out the best submission (max score) per contestant per challenge per contest:

SELECT contest_id, challenge_id, TRUNCATE(max_score, 1), COUNT(*)
FROM
(
  SELECT contest_id, challenge_id, competitor_id, MAX(score) AS max_score
  FROM submissions
  GROUP BY contest_id, challenge_id, competitor_id
) AS max_scores
GROUP BY contest_id, challenge_id, TRUNCATE(max_score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(max_score, 1);


WordPress: Google AdSense Plugin 3

Recently, we were trying to changing the position of the Google Ads that are shown on a website using the Google AdSense plugin for WordPress.

After clicking on the Manage Ads button at the configuration page (http://example.com/wp-admin/options-general.php?page=GooglePublisherPlugin) we would get an error that another theme had set the active ads and that the plugin cannot proceed to modify them unless they are disabled from the other theme (Specifically we would get the following error: Due to a theme change, ads are not being shown. Please reconfigure your ads or restore the previous theme.).

(a) Disabling the plugin, (b) Uninstalling the plugin (c) Reconnecting with another AdSense account did not help at all.

What we did at the end was to remove the GooglePublisherPlugin entry from the wp_option database of our installation.

DELETE FROM `MyDatabase`.`wp_options` WHERE `option_name` = "GooglePublisherPlugin";

Next time we visited the configuration page, we were disconnected from the previous account and we had to reconnect.

GetStarted-AdSense
After reconnecting, we followed the configuration procedure, we pressed the Manage Ads button, the website was analyzed, we got the confirmation message and we clicked on the Save & Activate button to store the changes.

For a few hours, no ads were shown on the site. We did not take any action and on the next day that ads started appearing as expected.