max

Count how many submissions per score

 in SQL  tagged count / group by / max / order by / sql / truncate by

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);

A peculiar way to get the biggest (max/maximum) value between two variables using bitwise operations 3

 in C  tagged bit / bitwise / C++ / clock / CLOCKS_PER_SEC / max / maximum / rand / srand / XOR by

Recently, we wanted to make a test and see how we could find the maximum value between two variables using bitwise operations.

We ended up with the following peculiar way to get the biggest value between two variables using bitwise operations


r = a ^ ((a ^ b) & -(a < b));

The above formula has two modes:

  1. When a < b
  2. When a >= b

 

When a < b then the formula will change as follows:


r = a ^ ((a ^ b) & 0xFFFFFFFF);

As we all (should) know, when one of the operators on a bitwise AND operation is composed only from 1s, then the result is whatever value the other operator was holding.
So, the formula then simplifies as follows:


r = a ^ (a ^ b);

which is equal to


r = b;

because we when we apply twice the same value using XOR on another value, we revert back to the original value (so the second ^a nullifies the first ^a)

 

When a >= b then the formula will change as follows:


r = a ^ ((a ^ b) & 0x00000000);

When one of the operators on a bitwise AND operation is composed only from 0s, then the result is always 0 no matter what value the other operator was holding.
So, the formula then simplifies as follows:


r = a ^ (0x00000000);

which is equal to


r = a;

because when one of the operators in a XOR operation is only composed from 0s then the result will be the value of the other operator, no matter what it was.

 

Full example

Below you will find a full example that compares the execution speed of the two methods by executing each several thousands of time on the same random data.

[download id=”3875″]


#include <stdio.h>
#include <time.h>
#include <stdlib.h>

int main() {
    {
        const clock_t start = clock();

        srand(10);
        unsigned long int i;
        unsigned int max = 0;
        for (i = 0; i < 1000000000; i++) {
            const int a = rand();
            max = max < a ? a : max;
        }
        const clock_t end = clock();
        const float seconds = (float) (end - start) / CLOCKS_PER_SEC;
        printf("Seconds elapsed %f\tIf statement. Overall max value = %u\n", seconds, max);
    }

    {
        const clock_t start = clock();

        srand(10);
        unsigned long int i;
        unsigned int max = 0;
        for (i = 0; i < 1000000000; i++) {
            const int a = rand();
            max = a ^ ((a ^ max) & -(a < max));
        }
        const clock_t end = clock();
        const float seconds = (float) (end - start) / CLOCKS_PER_SEC;
        printf("Seconds elapsed %f\tBitwise operation. Overall max value = %u\n", seconds, max);
    }
    return 0;
}

Results

Our results show that using the traditional if statement with assignment is faster than using our formula as expected.
Which makes sense as there is an if statement in the formula as well and then additional operations to get the result, instead of just the assignment.

Seconds elapsed 5.770000 If statement. Overall max value = 2147483647
Seconds elapsed 6.180000 Bitwise operation. Overall max value = 2147483647

10 times bigger input

Seconds elapsed 57.450001 If statement. Overall max value = 2147483647
Seconds elapsed 63.869999 Bitwise operation. Overall max value = 2147483647