SQL Queries That You’ll Never Need (But Should Try Anyway)

Let’s head to Codewars, a user-run site for creating and solving technical problems.

Long time readers of this blog (🦗🦗) will know I was once quite obsessed with solving technical problems on Codewars. This is where I honed my SQL querying abilities, writing so many select statements. So, so many…

One of the features of codewars is that users can "translate" problems from one language to another. And that's why we end up with problems where you write SQL queries nobody in their right mind would ever create. Which I solved anyway. Because I’m clearly some kind of masochist.

Recursive Expressions

Hey, ever wanted to generate the Fibonacci sequence but for some reason you could only use SQL so you didn’t bother? Say no more!

WITH RECURSIVE fib(n, prev, current) AS (
  VALUES (1, 1::BIGINT, 0::BIGINT)
  UNION ALL
  SELECT n + 1, current, prev + current FROM fib WHERE n < 51
)
SELECT DISTINCT fibo.n, fib.current AS res
FROM fibo
JOIN fib on fibo.n = fib.n
ORDER BY fibo.n ASC

Very simple and easy to understand. I won't bother to explain it. (Note because this is on the internet Poe's Law will likely come into effect and I have to mention that this is sarcasm).

Never used a recursive CTE before? Well you’re one of the lucky ones. I’ve written a few and I still have no idea how. They're like the final boss of SQL.

Codewars is full of SQL challenges like this, and I think sometimes we need to step back and think that just because we can do something, doesn't mean we should.

Anyway on my next blog I'm going to talk about how obsessed I am with solving problems in one line whilst absolutely disregarding all good programming principles and scarificing readability.

Author’s note: 2 weeks later I used a recursive CTE for generating