Multiple COUNTS within the same SELECT statement

Here’s another interesting problem that I solved. This one relates to SQL Server.

The problem:

To write a single database query that would allow me to get multiple row counts depending on certain pre-defined conditions. Let me make it clear … I had a table called nodes and I needed to count how many times certain non-unique records had been saved. Since the table was huge in size, the idea of using multiple queries scared me so using a single query to solve the problem was the way I found to optimize the performance and the algorithm.

The solution:

Many developers are used to write statements like select count(*) from certaintable where tablecolumn = specialcondition … that works great when you just need to count one thing at a time. My solution was to approach the problem by moving the where condition to the select section of the statement.

The query:

sum(case when node_id < 300 then 1 else 0 end),
sum(case when node_id > 200 then 1 else 0 end),
sum(case when node_id between 200 and 300 then 1 else 0 end)
from node

The recipe:

The secret sauce was to use the sum/case combo instead of the standard count function. By testing each condition I wanted with a case statement, and adding up the number of times each condition turned out to be true (using the sum function), I was able to achieve my goal.

How to shift the elements of an array

Just recently I came across this request to shift an entire slice of an array while keeping the algorithm cost to its bare minimum. Let’s put it this way. It’s pretty simple if you have something like ABCDE and you want to shift the elements so it becomes CDEAB. Now, the big problem is this – what if you have 1 billion bytes in this array and you need to perform sort of the same operation? That’s trick, right? Most likely, I wouldn’t be able to afford an additional high amount of memory to perform this.

So here’s what I came up with. The code is written in Python.

def f_ArrayExercise(a, i):
    n = 0
    while i <= len(a) - 1:
        a[i], a[n] = a[n], a[i]
        i += 1
        n += 1
        if len(a)%2 and len(a) > 1:
            a[len(a)-1], a[len(a)-2] = a[len(a)-2], a[len(a)-1]
    print a

The secret here is to swap one element at a time in order to save in memory space. However, note that with minor modifications we can easily swap pre-determined chuncks of the array at a time instead of limiting ourselves to one element – just in case we know we can afford a couple more elements. That would help optimize the processing time.

The first argument of the function is the array itself, the second argument is the exact spot you want to use to start shifting the array.

When you play around with this function, you get something like this:

>>> f_ArrayExercise([], 2)
>>> f_ArrayExercise([“A”], 2)
>>> f_ArrayExercise([“A”,”B”], 2)
[‘A’, ‘B’]
>>> f_ArrayExercise([“A”,”B”], 1)
[‘B’, ‘A’]
>>> f_ArrayExercise([“A”,”B”,”C”], 2)
[‘C’, ‘A’, ‘B’]
>>> f_ArrayExercise([“A”,”B”,”C”,”D”], 2)
[‘C’, ‘D’, ‘A’, ‘B’]
>>> f_ArrayExercise([“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”], 2)
[‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘A’, ‘B’]
>>> f_ArrayExercise([“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”, “I”], 2)
[‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘A’, ‘B’]
And that’s the end of this Array Manipulation Exercise.