## POW – Explain That SQL #1

Someone asked me to quickly write them some code in any language that would accomplish a certain task. The following, which is in PostgreSQL, is the second solution that I came up with after doing the first one in my favorite language, JavaScript:

SELECT ARRAY_TO_STRING(ARRAY(
SELECT CHR(num
+ CASE
WHEN num < 26 THEN 65
WHEN num BETWEEN 26 AND 51 THEN 71
ELSE -4
END
)
FROM (
SELECT FLOOR(RANDOM() * 62)::INT AS num,
generate_series(1, 12)
) AS t
), '')

The question is, what does this SELECT statement actually do? If you were asked to write that as a JavaScript, substituting the 12 for a variable passed into the function, what would your function look like?

As usual, the answer to this Problem of the Week will be made available a week from today, on Wednesday, August 22, 2012.

The answer to this Problem of the Week can now be found here.

## POW Answer – Fill In The Blanks In SQL

The following is one answer to this Problem of the Week that works in PostgreSQL (and probably in other flavors of SQL):

SELECT id
FROM (SELECT id + 1 AS id FROM stars UNION SELECT 1 AS id) t
WHERE NOT EXISTS (SELECT s.id FROM stars AS s WHERE s.id = t.id)
ORDER BY id
LIMIT 1;

## PostgreSQL – Sum Of An Array

Something I had to do today was figure out how to find the SUM of an array in PostgreSQL. I went through many different ways, but this seemed to be the best:

SELECT SUM(t) FROM UNNEST(ARRAY[1,2,3,4,5,6,7,8,9]) t;

If you know of a shorter or better way to do this for a variable size array please let me know in the comments section below. If it is better, I will post it and of course give you all the credit. 8)