The Great Christmas Analytics Crisis of 2024

Panic has erupted at the North Pole! Last year's Christmas delivery saw some unprecedented challenges that left Santa's logistics team scratching their heads. With the global population growing and children becoming increasingly spread out across different cities, the traditional "gut feeling" approach to gift delivery just isn't cutting it anymore.

During the post-Christmas debrief, Chief Elf Analyzer Tinsel Databeans discovered some concerning trends:

• Several small towns were accidentally skipped because they weren't properly factored into route planning
• Some reindeer teams were overloaded while others flew with half-empty sleighs
• The magic dust supply nearly ran out over Eastern Europe
• Multiple gifts were delivered to the wrong regions due to last-minute route changes

To prevent another near-disaster, Santa has commissioned a complete overhaul of their delivery analytics system. The elves have spent months collecting and digitizing years of delivery data into a comprehensive database. However, they're struggling to make sense of all this information.

The Naughty-Nice Clustering Crisis

One of the most critical issues they've identified is the need to optimize gift production and delivery based on "kindness clusters" - geographic areas where particularly good children tend to be concentrated. This information is crucial because:

• Workshop production lines need to be set up closer to areas with higher concentrations of nice children
• Magic dust allocation needs to be prioritized for regions with more deliveries
• Reindeer teams need to be strategically assigned to regions based on historical delivery volumes
• Backup delivery routes need to be planned for cities with consistently high nice-scores

The first challenge specifically addresses this by identifying the top 3 cities in each country with the highest average naughty-nice scores (minimum 5 children per city to ensure statistical significance). This data will help Santa:

• Establish primary gift distribution hubs in high-scoring cities
• Allocate appropriate resources to regions with concentrated nice scores
• Plan more efficient delivery routes by clustering deliveries around these priority cities
• Ensure proper reindeer team assignments based on regional delivery demands

Time is running out - Christmas is approaching, and the elves need your SQL expertise to transform this raw data into actionable insights. Can you help them make sense of their delivery database and ensure this Christmas runs smoother than ever?

psql postgres;

CREATE DATABASE santa_workshop;

\c santa_workshop

\i ~/downloads/advent_of_sql_day_0.sql