The Great Toy Tag Migration of 2024

'Twas the month before Christmas, and all through Santa's workshop, the elves were in a panic! During the annual toy database upgrade, something went terribly wrong with the magical toy-tracking system. The enchanted database that keeps track of all toy descriptions and magical properties had undergone a massive update, changing how toys were tagged and categorized.

Head Elf Database Administrator (HEDA) Pixelspring discovered that while they still had all the previous toy tags and the new ones, they desperately needed to understand what exactly changed during the upgrade. Some toys gained new magical properties, others lost their old enchantments, and some maintained their original charm.

Santa needs your help! As a consulting Database Wizard, you must help the elves analyze these changes so they can ensure each toy maintains its Christmas magic before being delivered to children around the world. Your task is to write a query that will help identify which magical properties (tags) were added, which remained constant, and which were lost during the great toy tag migration.

The fate of Christmas organization rests in your hands - can you help the elves make sense of their toy tags before Santa's big night?

Table Schema

DROP TABLE IF EXISTS toy_production CASCADE;
CREATE TABLE toy_production (
  toy_id INT,
  toy_name VARCHAR(100),
  previous_tags TEXT[],
  new_tags TEXT[]
);

Example data

INSERT INTO toy_production VALUES
(1, 'Robot', ARRAY['fun', 'battery'], ARRAY['smart', 'battery', 'educational', 'scientific']),
(2, 'Doll', ARRAY['cute', 'classic'], ARRAY['cute', 'collectible', 'classic']),
(3, 'Puzzle', ARRAY['brain', 'wood'], ARRAY['educational', 'wood', 'strategy']);

Sample result:

toy_id | toy_name |           added_tags           | unchanged_tags | removed_tags
-------+----------+--------------------------------+----------------+--------------
     1 | Robot    | {scientific,educational,smart} | {battery}      | {fun}
     3 | Puzzle   | {strategy,educational}         | {wood}         | {brain}
     2 | Doll     | {collectible}                  | {classic,cute} | {}

Solution to submit

1

3

1

1

psql postgres;

CREATE DATABASE santa_workshop;

\c santa_workshop

\i ~/downloads/advent_of_sql_day_4.sql