Fizzbuzz with modern SQL
A popular (technical) interview question for programming jobs is the Fizz Buzz problem.
In a nutshell, the problem is to write a program that outputs the numbers from 1 to n and prints “Fizz” for numbers that can be divided by 3, “Buzz” for numbers that can be divided by 5 and “Fizz Buzz” for numbers that can be divided by 15.
There are many solutions for the Fizz Buzz problem in various programming languages. However, I rarely see any for SQL, let alone standard SQL.
However this problem is actually quite easy to solve with 100% standard SQL. Wen can use a recursive common table expression to generate the numbers and a CASE expression to calculate what to display:
with recursive numbers (i) as (
select *
from (values (1)) as t
union all
select n.i + 1
from numbers n
where n.i < 100
)
select i,
case
when mod(i, 15) = 0 then 'Fizz Buzz'
when mod(i, 5) = 0 then 'Buzz'
when mod(i, 3) = 0 then 'Fizz'
else cast(i as varchar)
end as fz
from numbers
order by i;