SQL Advent Calendar.txt

----------------------------
Day 1 of SQL Advent Calendar
----------------------------

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.

Table name: rentals

rental_id	customer_name	activity	rental_date
1	Emily	Skiing	2024-01-01
2	Michael	Snowboarding	2024-01-02
3	Emily	Snowboarding	2024-01-03
4	Sarah	Skiing	2024-01-01
5	Michael	Skiing	2024-01-02
6	Michael	Snowtubing	2024-01-02

Question level of difficulty:
Medium

Solution: 

select 
    customer_name, 
    count(distinct activity) as activity_count
from rentals
group by customer_name
having count(distinct activity) > 1

----------------------------
Day 2 of SQL Advent Calendar
----------------------------

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

Table name: gifts

gift_name	recipient	weight_kg
Toy Train	John	2.5
Chocolate Box	Alice	0.8
Teddy Bear	Sophia	1.2
Board Game	Liam	0.9

Question level of difficulty:
Easy

Solution: 

select gift_name
from gifts
where weight_kg > 1

----------------------------
Day 3 of SQL Advent Calendar
----------------------------

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.

Table name: candy_nutrition

candy_id	candy_name	calories	candy_category
1	Candy Cane	200	Sweets
2	Chocolate Bar	250	Chocolate
3	Gingerbread Cookie	150	Baked Goods
4	Lollipop	100	Sweets
5	Dark Chocolate Truffle	180	Chocolate
6	Marshmallow	900	Sweets
7	Sugar Cookie	140	Baked Goods

Question level of difficulty:
Hard

Solution: 

select 
    candy_name, 
    candy_category as category, 
    calories, 
    rank() over (partition by candy_category order by calories) as rank_in_category
from candy_nutrition

----------------------------
Day 4 of SQL Advent Calendar
----------------------------

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.

Table name: ski_resorts

resort_id	resort_name	region
1	Snowy Peaks	Rocky Mountains
2	Winter Wonderland	Wasatch Range
3	Frozen Slopes	Alaska Range
4	Powder Paradise	Rocky Mountains
Table name: snowfall

resort_id	snowfall_inches
1	60
2	45
3	75
4	55

Question level of difficulty:
Medium

Solution:

select 
    region, 
    avg(snowfall_inches) as average_snowfall
from ski_resorts r
inner join snowfall s
on r.resort_id = s.resort_id
group by region
order by average_snowfall desc


----------------------------
Day 5 of SQL Advent Calendar
----------------------------

Today's Question:

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

Table name: beach_temperature_predictions

beach_name	country	expected_temperature_c	date
Bondi Beach	Australia	32	2024-12-24
Copacabana Beach	Brazil	28	2024-12-24
Clifton Beach	South Africa	31	2024-12-25
Brighton Beach	New Zealand	25	2024-12-25

Question level of difficulty:
Easy

Solution:

select beach_name
from beach_temperature_predictions
where expected_temperature_c > 30
and date = '2024-12-25'

----------------------------
Day 6 of SQL Advent Calendar
----------------------------

Today's 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.

Table name: polar_bears

bear_id	bear_name	age
1	Snowball	10
2	Frosty	7
3	Iceberg	15
4	Chilly	5
Table name: tracking

tracking_id	bear_id	distance_km	date
1	1	25	2024-12-01
2	2	40	2024-12-02
3	1	30	2024-12-03
4	3	50	2024-12-04
5	2	35	2024-12-05
6	4	20	2024-12-06
7	3	55	2024-12-07
8	1	45	2024-12-08

Question level of difficulty:
Hard

Solution:

with cte1 as 
(
    select 
        bear_id, 
        sum(distance_km) as total_distance_traveled
    from tracking
    where substr(date,1,7) = '2024-12'
    group by 1
)

select
    p.bear_id,
    bear_name, 
    total_distance_traveled
from polar_bears p
inner join cte1 t
on p.bear_id = t.bear_id
order by 3 desc
limit 3

----------------------------
Day 7 of SQL Advent Calendar
----------------------------

Today's 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.

Table name: vendors

vendor_id	vendor_name	market_location
1	Cozy Crafts	Downtown Square
2	Sweet Treats	Central Park
3	Winter Warmers	Downtown Square
Table name: sales

sale_id	vendor_id	item_name	quantity_sold	price_per_unit
1	1	Knitted Scarf	15	25
2	2	Hot Chocolate	50	3.5
3	3	Wool Hat	20	18
4	1	Handmade Ornament	10	15
5	2	Gingerbread Cookie	30	5

Question level of difficulty:
Medium

Solution:

with cte1 as (
    select 
        vendor_id,
        sum(quantity_sold*price_per_unit) as total_revenue
    from sales
    group by 1
)

select vendor_name, total_revenue
from vendors v
inner join cte1 t
on v.vendor_id = t.vendor_id
order by 2 desc
limit 2

----------------------------
Day 8 of SQL Advent Calendar
----------------------------

Today's Question:

You are managing inventory in Santa's workshop. Which gifts are meant for "good" recipients? 
List the gift name and its weight.

Table name: gifts

gift_id	gift_name	recipient_type	weight_kg
1	Toy Train	good	2.5
2	Lumps of Coal	naughty	1.5
3	Teddy Bear	good	1.2
4	Chocolate Bar	good	0.3
5	Board Game	naughty	1.8

Question level of difficulty:
Easy

Solution:

select 
    gift_name, 
    weight_kg
from gifts
where recipient_type = 'good'

----------------------------
Day 9 of SQL Advent Calendar
----------------------------

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.

Table name: events

event_id	event_name
1	Christmas Eve Dinner
2	New Years Feast
3	Winter Solstice Potluck
Table name: menu

dish_id	dish_name	event_id	calories	weight_g
1	Roast Turkey	1	3500	5000
2	Chocolate Yule Log	1	2200	1000
3	Cheese Fondue	2	1500	800
4	Holiday Fruitcake	3	4000	1200
5	Honey Glazed Ham	2	2800	3500

Question level of difficulty:
Hard

Solution:

with cte1 as (
select 
    dish_name, 
    e.event_name, 
    calories/weight_g as calories_per_gram,
    dense_rank() over (partition by event_name order by calories/weight_g desc) as r
from events e
inner join menu m 
on e.event_id = m.event_id
)

select 
    dish_name, 
    event_name, 
    calories_per_gram
from cte1
where r<=3
order by 2,3 desc

-----------------------------
Day 10 of SQL Advent Calendar
-----------------------------

Today's 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%.

Table name: resolutions

resolution_id	friend_name	resolution	is_completed
1	Alice	Exercise daily	1
2	Alice	Read 20 books	0
3	Bob	Save money	0
4	Bob	Eat healthier	1
5	Charlie	Travel more	1
6	Charlie	Learn a new skill	1
7	Diana	Volunteer monthly	1
8	Diana	Drink more water	0
9	Diana	Sleep 8 hours	1

Question level of difficulty:
Medium

Solution:

select 
    friend_name, 
    count(resolution_id),
    sum(case when is_completed=1 then 1 end) as num_completed_resolutions,
    round(sum(case when is_completed=1 then 1 end)*100.0/count(resolution_id)) as success_percentage,
    case when sum(case when is_completed=1 then 1 end)/count(resolution_id) > 0.75 then 'green'
    when sum(case when is_completed=1 then 1 end)/count(resolution_id) <= 0.75 
    and sum(case when is_completed=1 then 1 end)/count(resolution_id) >= 0.5 then 'yellow'
    when sum(case when is_completed=1 then 1 end)/count(resolution_id) < 0.5 then 'red'
    end as success_category
from resolutions
group by 1

-----------------------------
Day 11 of SQL Advent Calendar
-----------------------------

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.

Table name: family_members

member_id	name	relationship	birthday
1	Dawn	Sister	2024-12-24
2	Bob	Father	2024-05-20
3	Charlie	Brother	2024-12-25
4	Diana	Mother	2024-03-15

Question level of difficulty:
Easy

Solution:

select 
    name
from family_members
where substring(birthday,1,7) = '2024-12'

-----------------------------
Day 12 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: snow_globes

globe_id	globe_name	volume_cm3	material
1	Winter Wonderland	500	Glass
2	Santas Workshop	300	Plastic
3	Frozen Forest	400	Glass
4	Holiday Village	600	Glass
Table name: figurines

figurine_id	globe_id	figurine_type
1	1	Snowman
2	1	Tree
3	2	Santa Claus
4	2	Elf
5	2	Gift Box
6	3	Reindeer
7	3	Tree
8	4	Snowman
9	4	Santa Claus
10	4	Tree
11	4	Elf
12	4	Gift Box

Question level of difficulty:
Hard

Solution:

with sum_figurines as (
select 
    globe_name,
    volume_cm3,
    material,
    count(figurine_id) as num_figurines
from snow_globes s
inner join figurines f
on s.globe_id = f.globe_id 
group by 1,2,3
)

select 
    globe_name,
    num_figurines,
    material
from 
(
select 
    globe_name,
    num_figurines,
    material,
    dense_rank() over (order by num_figurines desc) r
from sum_figurines
) t1
where r<=3

-----------------------------
Day 13 of SQL Advent Calendar
-----------------------------

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

Table name: gifts

gift_id	gift_name	recipient	weight_kg
1	Toy Train	John	2.5
2	Chocolate Box	Alice	0.8
3	Teddy Bear	Sophia	1.2
4	Board Game	John	0.9

Question level of difficulty:
Medium

Solution : 

select 
    recipient, 
    sum(weight_kg) as total_weight_kg 
from gifts
group by recipient

-----------------------------
Day 14 of SQL Advent Calendar
-----------------------------

Today's Question:

Which ski resorts had snowfall greater than 50 inches?

Table name: snowfall

resort_name	location	snowfall_inches
Snowy Peaks	Colorado	60
Winter Wonderland	Utah	45
Frozen Slopes	Alaska	75

Solution:

select 
    resort_name
from snowfall
where snowfall_inches > 

-----------------------------
Day 15 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: family_members

member_id	name	age
1	Alice	30
2	Bob	58
3	Charlie	33
4	Diana	55
5	Eve	5
6	Frank	60
7	Grace	32
8	Hannah	8
9	Ian	12
10	Jack	3
Table name: parent_child_relationships

parent_id	child_id
2	1
3	5
4	1
6	7
6	8
7	9
7	10
4	8

Question level of difficulty:
Hard

Solution:

with parent_details as (
select 
    f.name, 
    count(child_id) as child_count
from parent_child_relationships p 
inner join family_members f
on p.parent_id = f.member_id
group by 1
)

select 
    name, 
    child_count
from (
select
    name,
    child_count,
    dense_rank() over (order by child_count desc) r
from parent_details
) t1
where r<=3
limit 3

-----------------------------
Day 16 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: candy_sales

sale_id	candy_name	quantity_sold	price_per_unit	category
1	Candy Cane	20	1.5	Sweets
2	Chocolate Bar	10	2	Chocolate
3	Lollipop	5	0.75	Sweets
4	Dark Chocolate Truffle	8	2.5	Chocolate
5	Gummy Bears	15	1.2	Sweets
6	Chocolate Fudge	12	3	Chocolate
Question level of difficulty:
Medium

Solution:

select 
    category,
    sum(quantity_sold*price_per_unit) as total_revenue
from candy_sales
group by 1
order by 2 desc

-----------------------------
Day 17 of SQL Advent Calendar
-----------------------------

Today's 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).

Table name: grinch_pranks

prank_id	prank_name	location	difficulty
1	Stealing Stockings	Whoville	Beginner
2	Christmas Tree Topple	Whoville Town Square	Advanced
3	Present Swap	Cindy Lous House	Beginner
4	Sleigh Sabotage	Mount Crumpit	Expert
5	Chimney Block	Mayors Mansion	Expert

Question level of difficulty:
Easy

Solution:

select 
    prank_name, 
    location
from grinch_pranks
where difficulty in ('Advanced','Expert')
order by 1 desc, 2 desc

-----------------------------
Day 18 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: activities

activity_id	activity_name
1	Surfing Lessons
2	Jet Skiing
3	Sunset Yoga
Table name: activity_ratings

rating_id	activity_id	rating
1	1	4.7
2	1	4.8
3	1	4.9
4	2	4.6
5	2	4.7
6	2	4.8
7	2	4.9
8	3	4.8
9	3	4.7
10	3	4.9
11	3	4.8
12	3	4.9

Question level of difficulty:
Hard

with avg_activity_rating as (
select 
    activity_name,
    avg(rating) as avg_rating
from activities a
inner join activity_ratings ar
on a.activity_id = ar.activity_id
group by 1
)
select     
    activity_name,
    avg_rating
from (
select 
    activity_name,
    avg_rating,
    dense_rank() over (order by avg_rating desc) as r
from avg_activity_rating
) t1
where r <=2

-----------------------------
Day 19 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: polar_bears

bear_id	bear_name	age
1	Snowball	10
2	Frosty	7
3	Iceberg	15
Table name: meal_log

log_id	bear_id	food_type	food_weight_kg	date
1	1	Seal	30	2024-12-01
2	2	Fish	15	2024-12-02
3	1	Fish	10	2024-12-03
4	3	Seal	25	2024-12-04
5	2	Seal	20	2024-12-05
6	3	Fish	18	2024-12-06

Question level of difficulty:
Medium

Solution:

select
    bear_name,
    max(food_weight_kg) as biggest_meal_kg
from polar_bears p
inner join meal_log m 
on p.bear_id = m.bear_id
where date between '2024-12-01' and '2024-12-31'
group by 1
order by 2 desc

-----------------------------
Day 20 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: vendors

vendor_id	vendor_name	market_location
1	Cozy Crafts	Downtown Square
2	Sweet Treats	Central Park
3	Winter Warmers	Downtown Square
Table name: item_prices

item_id	vendor_id	item_name	price_usd
1	1	Knitted Scarf	25
2	2	Hot Chocolate	5
3	2	Gingerbread Cookie	3.5
4	3	Wool Hat	18
5	3	Santa Pin	2

Question level of difficulty:
Easy

Solution:

select
    distinct vendor_name
from vendors v
inner join item_prices i 
on v.vendor_id = i.vendor_id
where price_usd<10

-----------------------------
Day 21 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: gifts

gift_id	gift_name	recipient_type	weight_kg
1	Toy Train	good	2.5
2	Lumps of Coal	naughty	1.5
3	Teddy Bear	good	1.2
4	Chocolate Bar	good	0.3
5	Board Game	naughty	1.8

Question level of difficulty:
Hard

Solution:

with recipient_type_weight as (
select 
   recipient_type,
   sum(weight_kg) as total_weight
from gifts
group by 1
)
select 
    recipient_type, 
    total_weight, 
    round(total_weight*100.0/(select sum(weight_kg) from gifts),2) as weight_percentage
from recipient_type_weight

-----------------------------
Day 22 of SQL Advent Calendar
-----------------------------

Today's 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).

Table name: guests

guest_id	guest_name
1	Cindy Lou
2	The Grinch
3	Max the Dog
4	Mayor May Who
Table name: guest_gifts

gift_id	guest_id	gift_name
1	1	Toy Train
2	1	Plush Bear
3	2	Bag of Coal
4	2	Sleigh Bell
5	3	Dog Treats

Question level of difficulty:
Medium

Solution:

select 
    guest_name
from guests
where guest_id not in (
    select guest_id from guest_gifts
)

-----------------------------
Day 23 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: grinch_weight_log

log_id	day_of_month	weight
1	1	250
2	2	248
3	3	249
4	4	247
5	5	246
6	6	248

Question level of difficulty:
Medium

Solution:

select
    weight - lag(weight) over (order by day_of_month) as weight_change
from grinch_weight_log

-----------------------------
Day 24 of SQL Advent Calendar
-----------------------------

Today's 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.

Table name: deliveries

delivery_date	gifts_delivered
2024-12-20	120
2024-12-21	150
2024-12-22	200
2024-12-23	300
2024-12-24	500

Question level of difficulty:
Hard

Solution:

select
    delivery_date,
    sum(gifts_delivered) over (order by delivery_date) as rolling_no_gifts
from deliveries
group by 1