Since December 2015, Eric Wastl has been creating 25 daily coding puzzles each year known as The Advent of Code. In 2022, more than 250,000 developers from around the world worked each day to solve the challenges in a language of their choice, including some members of the PostgreSQL community.

In this talk I’ll discuss 10 things I learned working through the daily challenges, using only PostgreSQL and SQL, without the use of pl/pgsql. The talk will start with an overview of the extract, load, and transform (ELT) pattern using built-in functions to work with data, regardless of the form. Once loaded, I’ll show multiple ways to chain together PostgreSQL functions and SQL commands to analyze the data and solve puzzles. I’ll demonstrate how CTEs, recursive CTEs, arrays, LATERAL CROSS JOINS, string functions, window functions, CASE statements, and more can work together without any procedural code to transform and query unique datasets.

By the end of this session, you will understand how to approach data challenges differently, using the power of PostgreSQL and SQL to work more effectively. Whether you’re a seasoned professional or still learning your way around PostgreSQL, you’ll leave with at least one new tip to help in your next data puzzle.