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