24 Days of SQL Advent Calendar Challenge: My Solutions

24 Days of SQL Advent Calendar Challenge: My Solutions

Share:

25 Dec 2024

Introduction

SQL is a powerful tool for querying and managing data, but like any skill, it takes practice to master. This holiday season, I challenged myself to complete 24 Days of SQL Advent Calendar Challenge, a series of SQL problems designed to test and improve my skills. Each challenge presented unique scenarios that required creativity, logic, and a deep understanding of SQL concepts.

In this post, I’ll share my solutions to the challenges along with explanations for how I approached each problem. My goal is to provide clear and concise answers while offering insights into the thought process behind the queries. Whether you’re a beginner looking to learn or an experienced SQL user seeking new ideas, I hope you find this post helpful and inspiring.

About the Challenge

This challenge was created by Dawn Choo, a renowned data scientist with over 90,000 followers on LinkedIn. Be sure to follow her for some fantastic resources and insights!

Unfortunately, the challenge was closed in January, so it’s no longer available for practice. But this inspired me to create SQL Challenge Hub—a platform where you can practice solving real-life SQL challenges for FREE! anytime, anywhere.

Challenge - 01

Question: A ski resort company want to know which customers rented ski equipment for more than one type of activity (e.g., skiing and snowboarding). List the customer names and the number of distinct activities they rented equipment for.

Answer:

SELECT
    customer_name,
    COUNT(DISTINCT activity) AS dist_activity
FROM rentals
GROUP BY customer_name
HAVING dist_activity > 1;

Comment: This is a straight forward question. The question is to get the distinct count of the activities per customer. So, I grouped the results by customer name and then filtered the result to get the customers with more than one distinct activities.

Challenge - 02

Question: Santa wants to know which gifts weigh more than 1 kg. Can you list them?

Answer:

SELECT DISTINCT gift_name
FROM gifts
WHERE weight_kg > 1;

Comment: Well, isn’t this an easy one!

Challenge - 03

Question: You’re trying to identify the most calorie-packed candies to avoid during your holiday binge. Write a query to rank candies based on their calorie count within each category. Include the candy name, category, calories, and rank (rank_in_category) within the category.

Answer:

SELECT 
    candy_name, 
    candy_category, 
    calories, 
    RANK() OVER(
      PARTITION BY candy_category 
      ORDER BY calories
    ) AS rank_in_category 
FROM 
  candy_nutrition;

Comment: This one is interesting! The question tells us to rank the candies within each category. So I created windows/ partitions for each category and within that window applied the rank() function and then sorted the results by calories.

Challenge - 04

Question: You’re planning your next ski vacation and want to find the best regions with heavy snowfall. Given the tables resorts and snowfall, find the average snowfall for each region and sort the regions in descending order of average snowfall. Return the columns region and average_snowfall.

Answer:

SELECT
    ski.region,
    AVG(snow.snowfall_inches) AS average_snowfall
FROM ski_resorts AS ski
    LEFT JOIN snowfall AS snow
        USING(resort_id)
GROUP BY ski.region
ORDER BY average_snowfall DESC;

Comment: USING is a useful keyword for joining two tables when the column names used for the join are the same.

Challenge - 05

Question: This year, we’re celebrating Christmas in the Southern Hemisphere! Which beaches are expected to have temperatures above 30°C on Christmas Day?

Answer:

SELECT 
    beach_name,
    expected_temperature_c
FROM beach_temperature_predictions
WHERE expected_temperature_c > 30
    AND date = "2024-12-25";

Challenge - 06

Question: Scientists are tracking polar bears across the Arctic to monitor their migration patterns and caloric intake. Write a query to find the top 3 polar bears that have traveled the longest total distance in December 2024. Include their bear_id, bear_name, and total_distance_traveled in the results.

Answer:

SELECT
    pb.bear_id,
    pb.bear_name,
    SUM(tr.distance_km) AS total_distance_traveled
FROM polar_bears AS pb
    INNER JOIN tracking AS tr
        ON pb.bear_id = tr.bear_id
WHERE strftime('%m', date) = "12"   -- BETWEEN also works.
    AND strftime('%Y', date) = "2024"
GROUP BY
    pb.bear_id,
    pb.bear_name
ORDER BY total_distance_traveled DESC
LIMIT 3;

Comment: The strftime function in SQLite is a powerful tool for formatting date and time values. It allows you to extract specific parts of a date or time, or to represent them in a variety of formats. This function isn’t available in MySQL, and because I learned SQL with MySQL, I was unaware of it. I struggled a bit to solve the problem, but I eventually learned the function and found a solution.

Challenge - 07

Question: The owner of a winter market wants to know which vendors have generated the highest revenue overall. For each vendor, calculate the total revenue for all their items and return a list of the top 2 vendors by total revenue. Include the vendor_name and total_revenue in your results.

Answer:

SELECT
    v.vendor_name,
    SUM(
        s.quantity_sold * s.price_per_unit
        ) AS total_revenue
FROM vendors AS v
    LEFT JOIN sales AS s
        USING(vendor_id)    -- Same name in both tables.
GROUP BY v.vendor_name
ORDER BY total_revenue DESC
LIMIT 2;

Challenge - 08

Question: You are managing inventory in Santa’s workshop. Which gifts are meant for “good” recipients? List the gift name and its weight.

Answer:

SELECT
    gift_name,
    weight_kg
FROM gifts
WHERE recipient_type = "good";

Challenge - 09

Question: A community is hosting a series of festive feasts, and they want to ensure a balanced menu. Write a query to identify the top 3 most calorie-dense dishes (calories per gram) served for each event. Include the dish_name, event_name, and the calculated calorie density in your results.

Answer:

WITH ranked_dishes AS (
    SELECT
        m.dish_name,
        e.event_name,
        CAST(m.calories AS float) / m.weight_g AS cal_density,
        ROW_NUMBER() OVER (
            PARTITION BY e.event_name
            ORDER BY CAST(m.calories AS float) / m.weight_g DESC
            ) AS rank
    FROM events AS e
        LEFT JOIN menu AS m
            USING(event_id)
)
SELECT
    dish_name,
    event_name,
    cal_density
FROM ranked_dishes
WHERE rank <= 3
ORDER BY
    event_name,
    cal_density DESC;

Comment: The CAST function here was necessary to convert the data into decimals. I was getting wrong results without it for some reason. Any idea?

Challenge - 10

Question: You are tracking your friends’ New Year’s resolution progress. Write a query to calculate the following for each friend: number of resolutions they made, number of resolutions they completed, and success percentage (% of resolutions completed) and a success category based on the success percentage:

Answer:

WITH temp AS    -- First CTE for grouping.
    (
    SELECT
        friend_name,
        COUNT(resolution) AS res_made,
        SUM(is_completed) AS res_completed
    FROM resolutions
    GROUP BY friend_name
    ),
cte AS          -- Second CTE for success percentage.
    (
    SELECT *,
        (CAST(res_completed AS float) / res_made) * 100 AS per_success
    FROM temp
    )
SELECT *,
    CASE
        WHEN per_success < 50 THEN "Red"
        WHEN per_success <= 75 THEN "Yellow"
        WHEN per_success > 75 THEN "Green"
    END AS success_cat
FROM cte;

Challenge - 11

Question: You are preparing holiday gifts for your family. Who in the family_members table are celebrating their birthdays in December 2024? List their name and birthday.

Answer:

SELECT
    name,
    birthday
FROM family_members
WHERE strftime('%m', birthday) = "12"
    AND strftime('%Y', birthday) = "2024";

Challenge - 12

Question: A collector wants to identify the top 3 snow globes with the highest number of figurines. Write a query to rank them and include their globe_name, number of figurines, and material.

Answer:

SELECT
    gl.globe_name,
    COUNT(fg.figurine_id) AS num_figurines,
    gl.material
FROM snow_globes AS gl
    LEFT JOIN figurines AS fg
        USING(globe_id)
GROUP BY gl.globe_name
ORDER BY num_figurines DESC
LIMIT 3;

Challenge - 13

Question: We need to make sure Santa’s sleigh is properly balanced. Find the total weight of gifts for each recipient.

Answer:

SELECT
    recipient,
    SUM(weight_kg) AS total_weight
FROM gifts
GROUP BY recipient;

Challenge - 14

Question: Which ski resorts had snowfall greater than 50 inches?

Answer:

SELECT resort_name
FROM snowfall
WHERE snowfall_inches > 50;

Challenge - 15

Question: A family reunion is being planned, and the organizer wants to identify the three family members with the most children. Write a query to calculate the total number of children for each parent and rank them. Include the parent’s name and their total number of children in the result.

Answer:

SELECT
    f.name,
    COUNT(r.child_id) AS total_children
FROM family_members AS f
    INNER JOIN parent_child_relationships AS r
        ON f.member_id = r.parent_id
GROUP BY f.name
ORDER BY total_children DESC
LIMIT 3;

Comment: Aliasing table names makes SQL queries shorter, more readable, and easier to maintain, especially when dealing with long table names or when joining multiple tables.

Challenge - 16

Question: As the owner of a candy store, you want to understand which of your products are selling best. Write a query to calculate the total revenue generated from each candy category.

Answer:

SELECT
    category,
    SUM(
        quantity_sold * price_per_unit
    ) AS total_revenue
FROM candy_sales
GROUP BY category;

Challenge - 17

Question: The Grinch is planning out his pranks for this holiday season. Which pranks have a difficulty level of “Advanced” or “Expert”? List the prank name and location (both in descending order).

Answer:

SELECT
    prank_name,
    location
FROM grinch_pranks
WHERE difficulty IN ("Advanced", "Expert")
ORDER BY
    prank_name DESC,
    location DESC;

Challenge - 18

Question: A travel agency is promoting activities for a “Summer Christmas” party. They want to identify the top 2 activities based on the average rating. Write a query to rank the activities by average rating.

Answer:

SELECT
    a.activity_name,
    AVG(r.rating) AS average_rating
FROM activities AS a
    LEFT JOIN activity_ratings AS r
        USING(activity_id)
GROUP BY a.activity_name
ORDER BY average_rating DESC
LIMIT 2;

Challenge - 19

Question: Scientists are studying the diets of polar bears. Write a query to find the maximum amount of food (in kilograms) consumed by each polar bear in a single meal December 2024. Include the bear_name and biggest_meal_kg, and sort the results in descending order of largest meal consumed.

Answer:

SELECT
    b.bear_name,
    MAX(m.food_weight_kg) AS biggest_meal_kg
FROM polar_bears AS b
    INNER JOIN meal_log AS m
        USING(bear_id)
WHERE m.date BETWEEN
    "2024-12-01" AND "2024-12-31"
GROUP BY bear_name
ORDER BY biggest_meal_kg DESC;

Challenge - 20

Question: We are looking for cheap gifts at the market. Which vendors are selling items priced below $10? List the unique (i.e. remove duplicates) vendor names.

Answer:

SELECT DISTINCT vendor_name
FROM vendors
    INNER JOIN item_prices
        USING(vendor_id)
WHERE price_usd < 10;

Challenge - 21

Question: Santa needs to optimize his sleigh for Christmas deliveries. Write a query to calculate the total weight of gifts for each recipient type (good or naughty) and determine what percentage of the total weight is allocated to each type. Include the recipient_type, total_weight, and weight_percentage in the result.

Answer:

SELECT
    recipient_type,
    SUM(weight_kg) AS total_weight,
    (
        SUM(weight_kg) * 1.0 / SUM(weight_kg) OVER()
    ) * 100 AS weight_percentage
FROM gifts
GROUP BY recipient_type;

Challenge - 22

Question: We are hosting a gift party and need to ensure every guest receives a gift. Using the guests and guest_gifts tables, write a query to identify the guest(s) who have not been assigned a gift (i.e. they are not listed in the guest_gifts table).

Answer:

SELECT guest_name
FROM guests
    LEFT JOIN guest_gifts
        USING(guest_id)
WHERE gift_id IS NULL;

Challenge - 23

Question: The Grinch tracked his weight every day in December to analyze how it changed daily. Write a query to return the weight change (in pounds) for each day, calculated as the difference from the previous day’s weight.

Answer:

SELECT
    day_of_month,
    weight - LAG(weight, 1) OVER(
        ORDER BY day_of_month
        ) AS weight_change
FROM grinch_weight_log;

Comment: The LAG function in SQL is a window function that allows you to access data from a previous row within the same result set. It’s incredibly useful for comparing values in the current row with values in a preceding row, often for tasks like calculating differences, trends, or moving averages.

Challenge - 24

Question: Santa is tracking how many presents he delivers each night leading up to Christmas. He wants a running total to see how many gifts have been delivered so far on any given night. Using the deliveries table, calculate the cumulative sum of gifts delivered, ordered by the delivery date.

Answer:

SELECT
    delivery_date,
    SUM(gifts_delivered) OVER(
        ORDER BY delivery_date
    ) AS sum_gifts_delivered
FROM deliveries;

Conclusion

Finally, this challenge was such a rewarding experience! It didn’t just sharpen my SQL skills—it also reminded me how fun and engaging problem-solving can be.

If you are reading this post, it means you also love SQL and solving problems. Feel free to explore the challenges at SQL Challenge Hub. I am sure you will find them both challenging and interesting. And if you have any suggestions or recommendations, I would love to hear from you!

Previous post:

Differences Between CODE() and UNICODE() Functions in Excel