24 Days of SQL Advent Calendar Challenge: My Solutions

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.

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:

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";

Comment:

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:

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;

Comment:

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";

Comment:

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:

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:

Green: If success percentage is greater than 75%.
Yellow: If success percentage is between 50% and 75% (inclusive).
Red: If success percentage is less than 50%.

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;

Comment:

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";

Comment:

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;

Comment:

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;

Comment:

Challenge - 14

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

Answer:

SELECT resort_name
FROM snowfall
WHERE snowfall_inches > 50;

Comment:

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:

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;

Comment:

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;

Comment:

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;

Comment:

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;

Comment:

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;

Comment:

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;

Comment:

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;

Comment:

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:

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;

Comment: