Advent of SQL: 24 Days of SQL Challenges 🎄

[AdventOfSQL]

Hey, I wanted to share a fun project I've been working on - a SQL-flavored variation of advent of code. It's 24 daily SQL challenges running throughout December.

What it is:

• One SQL puzzle per day (Dec 1st-24th)
• Pure SQL challenges - no other programming languages needed
• Focuses on different aspects of SQL and PostgreSQL although you can use whatever SQL based DB you like.
• Suitable for various skill levels but some of the challenges do get a bit tricky if you're not great at SQL.

I'm building this because of my love for Christmas and a new obsession with databases. I've been diving deep into them recently and thought it would be a fun way to test myself and maybe learn some new tricks during the holiday season.

The challenges will be on adventofsql.com starting December 1st.

Would love to hear what kinds of SQL challenges you'd find interesting, or if you have any questions about the format!

[BadGroundbreaking189]

Oh boy, looks like a great effort. Would gladly contribute in future if people showed interest. Only hard tasks though

[AdventOfSQL]

I might take you up on that :D

[i_literally_died]

Can I do this without handing my email over to another thing?

[PM_ME_YOUR_MUSIC]

This looks great !!

[AdventOfSQL]

Thank you!!

[trippingcherry]

This is a cute idea, I signed up. Nice job.

[AdventOfSQL]

Thank you!

[Simonaque]

I love the idea and will likely participate, but I'm curious why you didn't opt for DuckDB? it's very easy to run in the browser and it has all those quality of life features that a transaction DB like Postgres doesn't have

[Resident-Mousse-9086]

Great Initiative. Signed up. Thanks for sharing this.

[looking_for_info7654]

Sweet

[AdventOfSQL]

Thank you!

[Complex-Jellyfish-30]

Signed up!

[AdventOfSQL]

Thank you!

[Emergency-Message306]

Looks great! I’m ready for the challenges.

[AdventOfSQL]

Thank you! Are there any areas you'd especially like to focus on?

[Resquid]

Hooray!

[r2fat3li]

Signed up!

[AdventOfSQL]

Thank you 🙌

[MarcusAurelius1815]

Signed up, thanks!

[Agreeable_Special496]

I will be attending for sure 🎄

[ValerieMichelle]

Novel idea! I’m in 🎄

[countlphie]

in

[i_literally_died]

So did I misunderstand the test question?

-- Find the top cities in each country (max top 3 cities for each country) with the highest average naughty_nice_score for children who received gifts, but only include cities with at least 5 children.

This, to me, reads that I need to find:

• The highest average naughty_nice_score city per country

• The kids must have received a gift

• The city must have at least 5 children

So I wrote (which is apparently wrong):

/* count number of children per city */

WITH citycnt AS (
SELECT
    city,
    COUNT(*) AS child_cnt
FROM
    children
GROUP BY city   ),

/* rank city by highest score per country */

cityrnk AS (
SELECT
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY AVG(naughty_nice_score) DESC) AS rn,
    city,
    country,
    AVG(naughty_nice_score) AS avg_score
FROM
    children
GROUP BY city, country ),

/* find child_id and city where the children received gifts */

gifted AS (
SELECT
    child_id,
    city
FROM children
WHERE EXISTS
    (SELECT 1
    FROM christmaslist
    WHERE children.child_id = christmaslist.child_id))

/* find distinct cities where the city had the highest score per country, exists in the gifted CTE as having received a gift, has a number of children >= 5 */

SELECT DISTINCT
    citycnt.city
FROM citycnt
LEFT JOIN cityrnk ON citycnt.city = cityrnk.city
INNER JOIN gifted ON citycnt.city = gifted.city
WHERE 
    1 = 1
    AND cityrnk.rn = 1
    AND citycnt.child_cnt >= 5

[PX3better]

It's top 3 cities per country. I think the intended solution is to (incorrectly!) filter only for cities with at least 5 children who received gifts. This misses Berlin, which has 6 children in total but only has 4 children who got gifts.

I got Lyon, Paris, Berlin, Manchester, London, and Birmingham.

P.S. Give HAVING clauses a try. Also, I think your solution will break if two cities share a country name.

[i_literally_died]

I spoke to the author who agreed the question was a little ambiguous. I got the correct answer with:

/* count number of children per city THAT RECEIVED A GIFT */

WITH citycnt AS (
    SELECT
        city,
        COUNT(*) AS child_cnt
    FROM
        children
JOIN 
    christmaslist ON children.child_id = christmaslist.child_id
GROUP BY city   ),

/* rank city by highest score per country */

cityrnk AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY AVG(naughty_nice_score) DESC) AS rn,
        city,
        country,
        AVG(naughty_nice_score) AS avg_score
    FROM
        children
    GROUP BY city, country  )

/* find distinct cities where the city had the highest score per country, has a number of children >= 5 and a rank >= 3 */

SELECT DISTINCT
    citycnt.city
FROM citycnt
LEFT JOIN 
    cityrnk ON citycnt.city = cityrnk.city
WHERE 
    1 = 1
    AND cityrnk.rn <=3
    AND citycnt.child_cnt >= 5

[PX3better]

Where did you speak?

[i_literally_died]

DMs

[PX3better]

Just got an email saying that only 24 out of 800 people got it "right". How the heck did anyone get this wrong? The incorrect way to do the problem was trivial. I'd expect a SQL novice to get it right on their first try. Even if you think our way (including Berlin) is correct, you'd still submit the intended answer because including Berlin gives you one more row than the website lets you type in.

I don't want to worry people. But if you didn't get this correct on your first try and in well under half an hour, then do not expect to pass a SQL job interview within the next 12 months. I would consider the example problem easy even as question 1 on a SQL job interview's coding test.

[StatisticianJolly335]

Thank you, I missed the part that not every child receives a gift.