Happy New Square Year

At a family dinner a few days ago, we realized that the upcoming year – 2025 – is a square number 🤩
Promptly the kids checked and announced that it’s the only year in the 21st century that is a square number.
We wondered how many square years there are per century over time, and, as always, such questions are answered using a simple SQL query.

with
   n as (select 30 centuries from dual),
   c as (select rownum century from n connect by level <= n.centuries),
   y as (select rownum*rownum year from n connect by level * level <= n.centuries*100),
   yc as (select y.year, to_number(to_char(to_date(y.year,'yyyy'),'cc')) century from y)
select
  c.century,
  count(yc.year) number_of_sqaure_years,
  decode(count(yc.year), 1, any_value(yc.year)) the_only_sqaure_in_the_century
from c, yc
where yc.century(+) = c.century
group by c.century
order by c.century;

   CENTURY NUMBER_OF_SQAURE_YEARS THE_ONLY_SQAURE_IN_THE_CENTURY
---------- ---------------------- ------------------------------
         1                     10
         2                      4
         3                      3
         4                      3
         5                      2
         6                      2
         7                      2
         8                      2
         9                      2
        10                      1                            961
        11                      2
        12                      1                           1156
        13                      2
        14                      1                           1369
        15                      1                           1444
        16                      2
        17                      1                           1681
        18                      1                           1764
        19                      1                           1849
        20                      1                           1936
        21                      1                           2025 -- We Are Here!
        22                      1                           2116
        23                      1                           2209
        24                      1                           2304
        25                      2
        26                      0
        27                      1                           2601
        28                      1                           2704
        29                      1                           2809
        30                      1                           2916

30 rows selected.

Looking at the first 30 centuries, the 10th century was the first to have only one square year. Since the 17th century, each century has exactly one square year, except for the 25th that has two and the 26th that has none.

Happy new year!