Solving Project Euler #17 with Snowflake SQL
I recently worked through Project Euler Problem #17 using Snowflake SQL. The problem asks you to count the total letters in all number words from 1 to 1,000 (for example, "three hundred and forty-two" has 23 letters).
Most solutions use Python or JavaScript for the string manipulation, but I wanted to see what a pure SQL approach would look like.
The Challenge
Project Euler #17 asks: "If all the numbers from 1 to 1000 were written out in words, how many letters would be used?"
For example:
Most developers would reach for Python or their scripting language of choice. But what if you're living in the SQL world? What if Snowflake is your daily driver?
The SQL Approach
Working with SQL for this problem meant thinking about it differently than I would in a procedural language. Instead of loops and conditionals, I focused on:
Implementation Details
The solution uses several Snowflake features:
Table Generation: GENERATOR() and SEQ4() create the sequence from 1 to 1,000.
Lookup Tables: A CTE maps numbers to their word equivalents (1='one', 20='twenty', etc.).
Pattern Matching: LEFT() and RIGHT() extract digit positions to handle compound numbers like "twenty-one."
String Operations: Concatenation builds the full number words, handling special cases like "and" in "one hundred and one."
Aggregation: SUM(LEN()) counts the total letters across all generated number words.
The Code
Here's the full solution:
CREATE OR REPLACE TEMPORARY TABLE temp_numbers AS
SELECT SEQ4() + 1 AS num
FROM TABLE(GENERATOR(ROWCOUNT => (99)));
WITH number_words as (
SELECT 1 as number_x ,'one' as word UNION ALL
SELECT 2 ,'two' UNION ALL
SELECT 3 ,'three' UNION ALL
SELECT 4 ,'four' UNION ALL
SELECT 5 ,'five' UNION ALL
SELECT 6 ,'six' UNION ALL
SELECT 7 ,'seven' UNION ALL
SELECT 8 ,'eight' UNION ALL
SELECT 9 ,'nine' UNION ALL
SELECT 10 ,'ten' UNION ALL
SELECT 11 ,'eleven' UNION ALL
SELECT 12 ,'twelve' UNION ALL
SELECT 13 ,'thirteen' UNION ALL
SELECT 14 ,'fourteen' UNION ALL
SELECT 15 ,'fifteen' UNION ALL
SELECT 16 ,'sixteen' UNION ALL
SELECT 17 ,'seventeen' UNION ALL
SELECT 18 ,'eighteen' UNION ALL
SELECT 19 ,'nineteen' UNION ALL
SELECT 20 ,'twenty' UNION ALL
SELECT 30 ,'thirty' UNION ALL
SELECT 40 ,'forty' UNION ALL
SELECT 50 ,'fifty' UNION ALL
SELECT 60 ,'sixty' UNION ALL
SELECT 70 ,'seventy' UNION ALL
SELECT 80 ,'eighty' UNION ALL
SELECT 90 ,'ninety' UNION ALL
SELECT 100 ,'onehundred'
),
onehundred as (
SELECT 'onehundredand' as hundred_column
),
twohundred as (
SELECT 'twohundredand' as hundred_column
),
threehundred as (
SELECT 'threehundredand' as hundred_column
),
fourhundred as(
SELECT 'fourhundredand' as hundred_column
),
fivehundred as (
SELECT 'fivehundredand' as hundred_column
),
sixhundred as (
SELECT 'sixhundredand' as hundred_column
),
sevenhundred as (
SELECT 'sevenhundredand' as hundred_column
),
eighthundred as (
SELECT 'eighthundredand' as hundred_column
),
ninehundred as (
SELECT 'ninehundredand' as hundred_column
)
SELECT SUM(
len(number_1) + len(number_2) + len(number_3) + len(number_4) + len(number_5) + len(number_6) + len(number_7) + len(number_8) + len(number_9) + len(number_y)
) + len('onehundredtwohundredthreehundredfourhundredfivehundredsixhundredsevenhundredeighthundredninehundredonethousand') --edge cases
FROM (
SELECT num,
n.word as initial_word,
n2.word as tens,
n3.word as ones,
len(num) as len_num,
case when initial_word is not null then initial_word else CONCAT(tens,ones) END as number_y,
CONCAT(o1.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_1,
CONCAT(o2.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_2,
CONCAT(o3.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_3,
CONCAT(o4.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_4,
CONCAT(o5.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_5,
CONCAT(o6.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_6,
CONCAT(o7.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_7,
CONCAT(o8.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_8,
CONCAT(o9.hundred_column,case when initial_word is not null then initial_word else CONCAT(tens,ones) END) as number_9
from temp_numbers as t LEFT JOIN number_words as n
on t.num = n.number_x LEFT JOIN number_words as n2
on left(num,1) = left(n2.number_x,1) and n2.number_x > 19 and n2.number_x < 91
and len(num) = 2 LEFT JOIN number_words as n3
on right(num,1) = n3.number_x and num > 20
CROSS JOIN onehundred as o1
CROSS JOIN twohundred as o2
CROSS JOIN threehundred as o3
CROSS JOIN fourhundred as o4
CROSS JOIN fivehundred as o5
CROSS JOIN sixhundred as o6
CROSS JOIN sevenhundred as o7
CROSS JOIN eighthundred as o8
CROSS JOIN ninehundred as o9
) as X
The approach uses cross joins with separate CTEs for each hundred level, which generates all possible hundred combinations. The final SELECT concatenates the appropriate hundred prefix with the two-digit number word, then sums up all the letter counts.
Some Observations
A few things stood out while building this:
The lookup table pattern is reusable. Adding support for larger numbers means extending the mapping rather than rewriting logic.
SQL's declarative nature makes each transformation explicit. You can trace exactly how "342" becomes "three hundred and forty-two."
These techniques show up in real work too—generating formatted labels, building dynamic reports, or constructing structured identifiers.
The solution isn't as concise as Python, but it demonstrates that SQL can handle algorithmic problems beyond typical analytics queries.
Closing Thoughts
I spend most of my time in SQL, so it felt natural to try solving this problem without switching tools. It's a different mindset from procedural code, but that's part of what makes it interesting.
If you work primarily in a data warehouse, you've probably run into situations where you need to do something beyond a standard query. Sometimes it makes sense to pull the data and process it elsewhere. Other times, it's worth seeing what you can do directly in SQL.
This solution demonstrates that Snowflake and SQL are capable of handling more than relational database queries, the answer (21,124) was arrived at via cartesian products, string manipulation, edge case handling and pattern matching. The great thing about Project Euler is that there are a myriad of ways in which to solve the problem and this query demonstrates the ability of Snowflake to handle algorithmic challenges.
#DataEngineering #Snowflake #SQL #ProjectEuler #CreativeProblemSolving #LearningInPublic #DataScience