Thanks for trying Advent of SQL 2024! 🎉   Read the review of the 2024 edition here.

Santa's jumbled letters 🎅

Beginner

'Twas the month before Christmas, and all through Santa's high-tech command center, not a keyboard was clicking, not even a printer... Until suddenly, BEEP! BEEP! BEEP!

"Oh dear, oh my!" exclaimed Pixel, the head of Santa's IT elf team, adjusting his candy-cane striped glasses. "The Northern Lights are acting up again! They've scrambled our letters database!"

You see, this year, Santa had modernized his mail system to handle the billions of electronic letters coming in from children worldwide. But the magical Aurora Borealis, extra sparkly this season, had interfered with the database servers, turning perfectly good Christmas wishes into a jumble of integers!

To make matters worse, the backup system (managed by two excitable elf twins, Binky and Blinky) had split the data across different tables, and somehow mixed in random "holiday sparkles" (aka noise) into the data.

Table Schemas

-- Binky's Table
CREATE TABLE letters_a (
    id SERIAL PRIMARY KEY,
    value INTEGER
);

-- Blinky's Table
CREATE TABLE letters_b (
    id SERIAL PRIMARY KEY,
    value INTEGER
);

Example Data

-- Binky's data (letters_a)
INSERT INTO letters_a (id, value) VALUES
(1, 68),    -- D
(2, 101),   -- e
(4, 97),    -- a
(5, 114),   -- r
(6, 32),    -- (space)
(7, 83),    -- S
(8, 35),    -- # (noise)
(9, 97),    -- a
(10, 110),  -- n
(11, 116),  -- t
(12, 97),   -- a
(13, 44),   -- ,
(14, 32),   -- (space)
(15, 73),   -- I
(16, 42),   -- * (noise)
(17, 32),   -- (space)
(18, 119),  -- w
(19, 111),  -- o
(20, 117),  -- u
(21, 108),  -- l
(22, 100);  -- d

-- Blinky's data (letters_b)
INSERT INTO letters_b (id, value) VALUES
(23, 32),   -- (space)
(24, 36),   -- $ (noise)
(25, 108),  -- l
(26, 105),  -- i
(27, 107),  -- k
(28, 101),  -- e
(29, 32),   -- (space)
(30, 97),   -- a
(31, 32),   -- (space)
(32, 80),   -- P
(33, 111),  -- o
(34, 37),   -- % (noise)
(35, 110),  -- n
(36, 121),  -- y
(37, 32),   -- (space)
(38, 102),  -- f
(39, 111),  -- o
(40, 114),  -- r
(41, 32),   -- (space)
(42, 67),   -- C
(43, 104),  -- h
(44, 38),   -- & (noise)
(45, 114),  -- r
(46, 105),  -- i
(47, 115),  -- s
(48, 116),  -- t
(49, 109),  -- m
(50, 97),   -- a
(51, 115),  -- s
(52, 44),   -- ,
(53, 32),   -- (space)
(54, 76),   -- L
(55, 111),  -- o
(56, 118),  -- v
(57, 101),  -- e
(58, 32),   -- (space)
(59, 83),   -- S
(60, 117),  -- u
(61, 64),   -- @ (noise)
(62, 115),  -- s
(63, 105),  -- i
(64, 101);  -- e

Sample result:

decoded_message
------------------
Dear Santa, I would like a Pony for Christmas, Love Susie

Expected submission:

Dear Santa, I would like a Pony for Christmas, Love Susie

The challenge 🎁

Download challenge data

These tables contain pieces of a child's Christmas wish, but they're all mixed up with magical interference from the Northern Lights! We need to:

  1. Filter out the holiday sparkles (noise)
  2. Combine Binky and Blinky's tables
  3. Decode the values back into regular letters
  4. Make sure everything's in the right order!
Valid characters
  • All lower case letters a - z
  • All upper case letters A - Z
  • Space
  • !
  • "
  • '
  • (
  • )
  • ,
  • -
  • .
  • :
  • ;
  • ?

Rate this challenge