166|SQL – The Guild of Gold

Cady_Eaton · February 27, 2025, 1:21am

Difficulty=Easy
Time to Complete 25 mins
Q1
SELECT DISTINCT QuestLeader
FROM Quests;
Q2
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID=Treasure_Types.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC
Q3
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
Q4
SELECT QuestLeader, QuestName, Round(AVG(TreasureValue),2) AS AvgTreasureValue
FROM Treasures INNER JOIN Quests ON Treasures.QuestID=Quests.QuestID
GROUP BY QuestLeader ,QuestName
ORDER BY Avg(TreasureValue) DESC
Q5
SELECT TypeName, COUNT(Treasures.TypeID) AS CountFound
FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID=Treasure_Types.TypeID
GROUP BY TypeName
ORDER BY CountFound;

Noah_Bluth · February 27, 2025, 1:59am

Time to complete 40 min
Rating: Intermediate

SELECT DISTINCT (QuestLeader)
FROM Quests;

SELECT TypeName, t.TreasureValue AS TotalValue
FROM Treasure_Types AS tt INNER JOIN Treasures AS t ON t.TypeID = tt.TypeID
ORDER BY TreasureValue DESC;

SELECT TOP 5 q.QuestName, SUM(t.TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY q.QuestName
ORDER BY SUM(t.TreasureValue) DESC;

SELECT q.QuestLeader, q.QuestName, Round(Avg(t.TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON q.QuestID = t.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY Round(Avg(t.TreasureValue), 2) DESC;

SELECT tt.TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasure_Types AS tt INNER JOIN Treasures AS t ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName;

Christian_Townsend · February 27, 2025, 2:03am

Time 45.490237 minutes
Rating: Expert
Comments: I could not figure out the joins for some reason
Question 1:
SELECT Distinct QuestLeader
from Quests
Question 2:
SELECT Treasure_Types.TypeName, sum(Treasures.TreasureValue) as TotalTreasureValue
from Treasures
inner join Treasure_Types on Treasures.TypeID = Treasure_Types.TypeID
group by Treasure_Types.TypeName
order by sum(Treasures.TreasureValue) desc
Question 3:
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
Question 4:
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests INNER JOIN Treasures ON Treasures.QuestID = Quests.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
Question 5:
SELECT TypeName, Count(Treasures.TypeID) AS CountFound
FROM Treasures INNER JOIN Treasure_Types ON Treasures.TypeID = Treasures.Types.TypeID
GROUP BY TypeName
ORDER BY Count(Treasures.TypeID);

Detroit_Kane · February 27, 2025, 2:28am

Time: 20 Min
Difficulty: Medium
Solution

SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TT.TypeName, SUM(T.TreasureVal) AS TotalValue
FROM Treasure_Types TT
INNER JOIN Treasures T ON T.TypeID=TT.TypeID
GROUP BY TT.TypeName
Order By SUM(T.TreasureVal)

SELECT TOP5 QuestName, SUM(T.TreasureVal) AS TotalLoot
FROM Quests Q
INNER JOIN Treasures T ON Q.QuestID = T.QuestID
ORDER BY SUM(T.TreasureVal) Desc;

SELECT Q.QuestLeader, Q.QuestName, ROUND(AVG(T.TreasureVal),2) AS AvgTreasureValue
FROM Quests Q
INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY Q.QuestLeader, Q.QuestName
ORDER BY Avg(TreasureValue) DESC;

5.SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);

Ford_Zhao · February 27, 2025, 2:39am

Time: 20 mins
Difficulty: Easy
Solution:

Select distinct questleader
From quests
Select typename, sum(treasurevalue) as totalvalue
From treasures
Inner join treasure_types on treasures.typeid = treasure_types.typeid
Group by typename
Order by sum(treasurevalue) desc
Select questname, sum(treasurevalue) as totalloot
From quests
Inner join treasures on quests.questid = treasures.questid
Group by questname
Order by sum(treasurevalue) desc
Limit 5
Select QuestLeader, QuestName, round(avg(treasurevalue),2) as AvgTreasureValue
From quests
Inner join treasures on quests.questid = treasures.questid
Group by QuestLeader, QuestName
Order by avg(treasurevalue) desc
Select typename, count(typeid) as countfound
From treasures
Inner join treasure_types on treasures.typeid = treasure_types.typeid
Group by typename
Order by count(typeid)

Cash_Clark · February 27, 2025, 2:48am

SELECT DISTINCT QuestLeader
FROM Quests;
2.
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
3.
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
4.
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
5.
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);

Abigail_Dillon · February 27, 2025, 2:59am

Time: 20 minutes
Rating: Intermediate
Solution:

SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;

SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q
INNER JOIN Treasures AS t ON t.QuestID = q.QuestID GROUP BY QuestName
ORDER BY Sum( TreasureValue) DESC;
4. SELECT QuestLeader, QuestName, Round( Avg (TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q
INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
5. SELECT TypeName, count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count( t.TypeID);

Tyler_Rhoads · February 27, 2025, 3:06am

Time: 25 Mins
Rating: Medium

SELECT DISTINCT QuestLeader
FROM Quests

SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC

SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC

SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC

SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)

Spencer_Moore · February 27, 2025, 3:24am

Time to complete: 35
difficulty Intermediate
solutions:

1: SELECT Distinct Field3 AS QuestLeader
From Quests;
2: SELECT TypeName, SUM(TreasureValue) AS TotalValue
From Treasures
Inner Join Treasure_Types ON Treasures.TypeID = Treasure_types.TypeID
Group By TypeName
3: SELECT TOP 5 Field3, SUM(TreasureValue) AS TotalLoot
From (Quests
Inner Join Treasures ON Quests.QuestID = Treasures.QuestID)
Group BY Field3
Order By TotalLoot
4: SELECT Field3, field2, round(AVG(TreasureValue),2) AS TreasuerValueAVG
From Quests
Inner Join Treasures ON Quests.QuestID = Treasures.questID
Group By Field3, Field2
Order By TreasureValueAVG
5: SELECT TypeName, count(TreasureType)
From Treasures
Inner Join Treasure_Types ON Treasures.TypeID = Treasure_Types.TypeID
Group BY TypeName
Order By count(TreasureType)

Knox_Stokes · February 27, 2025, 3:25am

Time to Complete: 25
Rating: Intermediate

1. SELECT DISTINCT QuestLeader
FROM Quest;

2. SELECT tt.TypeName, SUM(t.TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types As tt ON tt.TypeID = t.TypeID
GROUP BY tt.TypeName
ORDER BY SUM(t.TreasureValue) DESC;

3. SELECT q.QuestName, SUM(t.TreasureValue) AS TotalLoot
FROM Quests AS q 
INNER JOIN Treasures AS t ON t.QuestID = q.QuestID 
GROUP BY q.QuestName
ORDER BY SUM(t.TreasureValue) DESC;

4. SELECT QuestLeader, QuestName, AVG(TreasureValue) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasure AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY AVG(TreasureValue) DESC;

5. SELECT TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName

ORDER BY Count (t.TypeID);

Dayton_Larson · February 27, 2025, 3:37am

Time: 20 minutes
Difficulty Intermediate
Solution:

1.SELECT DISTINCT QuestLeader
FROM Quests

2.SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tr ON t.TypeID = tr.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC

3.SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC

4.SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC

5.SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)

Ethan_Hanks · February 27, 2025, 3:49am

Completion time: 20min

q1
SELECT QuestLeader
FROM Quests
;

q2
SELECT tt.TypeName, sum(t.TreasureValue) AS TotalValue
FROM Treasure_Types tt INNER JOIN Treasures t ON tt.typeid=t.typeid
GROUP BY tt.TypeName
;

q3
SELECT TOP 5 q.questname, sum(t.treasurevalue) as TotalLoot
from Quests q INNER JOIN Treasures t ON q.questid=t.questid
GROUP BY q.questname
ORDER BY sum(t.treasurevalue) DESC
;

q4
SELECT q.QuestLeader, q.QuestName, ROUND(AVG(t.TreasureValue), 2) AS AvgTreasureValue
FROM Quests q INNER JOIN Treasures t ON t.QuestID = q.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY ROUND(AVG(t.TreasureValue), 2) DESC
;

q5
SELECT tt.typename, COUNT(t.typeid) as CountFound
From Treasures t INNER JOIN Treasure_Types tt ON tt.typeid=t.typeid
GROUP BY tt.typename
;

Casey_Noack · February 27, 2025, 3:50am

20 Minutes to complete
Beginner Difficulty,
Solution:
1.
SELECT quest leader
from Quests;

SELECT TypeName, SUM(TreasureValue) as TotalValue
FROM Treasures as t Inner join Treasure_Types as tr on tr.TypeID = t.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) desc;

SELECT TOP 5 q.QuestName, SUM(t.TreasureValue) as TotalLoot
FROM Quests as q INNER JOIN Treasures as t on t.QuestID = q.QuestID
group by q.QuestName
order by SUM(t.TreasureValue) desc;

SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) as AvgTreasureValue
FROM Quests as q inner join Treasures as t on t.QuestID = q.QuestID
group by QuestLeader, QuestName
order by Avg(TreasureValue) desc;
5.
SELECT TypeName, COUNT(T.TypeID) as CountFound
from Treasures as T INNER join Treasure_Types as TT ON T.TypeID = TT.TypeID
group by TypeName
order by COUNT(T.TypeID);

Josh_Tanner · February 27, 2025, 3:50am

Time to Complete: 15 Minutes
Difficulty: Moderate
Notes: My Access file got switched up somehow, the Quests table had the columns of “Field1, Field2, Field3” Instead of what it should have been"
Solutions:
1.
SELECT DISTINCT QuestLeader
FROM Quests;
2.
SELECT tt.TypeName, Sum(t.TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName
ORDER BY Sum(t.TreasureValue) DESC;
3.
SELECT TOP 5 q.QuestName, Sum(t.TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY q.QuestName
ORDER BY Sum(t.TreasureValue) DESC;
4.
SELECT q.QuestLeader, q.QuestName, Round(Avg(t.TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(t.TreasureValue) DESC;
5.
SELECT tt.TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY tt.TypeName
ORDER BY Count(t.TypeID);

Ainsley_Barlow · February 27, 2025, 3:59am

Time to complete: 20
Difficulty: easy
Comments: there were some problems running these queries
Solution:

SELECT DISTINCT questleader
FROM quests;
SELECT t.typename, SUM(tt.treasurevalue) AS totalvalue
FROM treasures AS t
JOIN treasure_types AS tt ON t.typeid = tt.typeid
ORDER BY SUM(tt.treasurevalue) DESC;
SELECT TOP 5 q.questname, SUM(t.treasurevalue) AS TotalLoot
FROM quests AS q
JOIN treasures AS t ON t.questid = q.questid
GROUP BY q.questname
ORDER BY SUM(t.treasurevalue) DESC;
SELECT q.questleader, q.questname, ROUND(AVG(t.treasurevalue),2) AS AvgTreasureValue
FROM quests AS q
JOIN treasures AS t ON q.questid = t.questid
GROUP BY q.questleader, q.questname
ORDER BY Avg(t.treasurevalue) DESC;
SELECT tt.typename, COUNT(t.typeid) AS CountFound
FROM treasure_types AS tt
JOIN treasures AS t ON tt.typeid = t.typeid
ORDER BY COUNT(t.typeid);

Chase_Hopkins · February 27, 2025, 4:12am

Time: 30 minutes
Difficulty: Easy
Solution:

select distinct questleader
from quests;
select tt.typename, sum(treasurevalue) as TotalValue
from (treasures as t inner join treasure_types as tt on tt.typeid=t.typeid)
group by typename
order by totalvalue desc;
SELECT TOP 5 q.QuestName, Sum(t.TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY TotalLoot;
SELECT q.QuestLeader, q.QuestName, ROUND(AVG(TreasureValue),2) as AvgTreasureValue
FROM (Quests AS q INNER JOIN Treasures AS t ON q.questid=t.questid)
GROUP BY q.QuestLeader, q.QuestName
Order BY AvgTreasureValue DESC;
SELECT TypeName, COUNT(t.TypeID) AS CountFound
FROM Treasure_types AS tt INNER JOIN Treasures AS t ON tt.typeID=t.typeID
GROUP BY TypeName
ORDER BY CountFound ASC;

Jerem_Porter · February 27, 2025, 4:13am

Time to complete 50 minutes
Difficulty: Beginner/Intermediate
Notes: There was an error in the data itself where there was a type error. It took some time for me to fix it.
Solutions:
Q1
SELECT DISTINCT QuestLeader
FROM Quests;

Q2
SELECT type.TypeName, sum(tr.TreasureValue)
FROM Treasures AS tr INNER JOIN Treasure_Types AS type ON tr.TypeID = type.TypeID
GROUP BY type.TypeName
ORDER BY sum(tr.TreasureValue);

Q3
SELECT TOP 5 q.QuestName, sum(t.TreasureValue)
FROM Quests AS q
INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY q.QuestName\

Q4
SELECT q.QuestLeader, q.QuestName, round(avg(t.TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON q.QuestID = t.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY avg(t.TreasureValue) DESC

Q5
SELECT y.TypeName, count(y.TypeID)
FROM Treasures AS t
INNER JOIN Treasure_Types AS y ON t.TypeID = y.TypeID
GROUP BY TypeName
ORDER BY count(y.TypeID)

Ryan_Martin · February 27, 2025, 4:27am

Time to Complete: 25 minutes
Difficulty: Intermediate

SELECT DISTINCT QuestLeader
FROM Quests;

SELECT t.TypeName, SUM(t.Value) AS TotalValue
FROM Treasure AS t
GROUP BY t.TypeName
ORDER BY TotalValue DESC;

3.
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;

4. 
SELECT q.QuestLeader, q.QuestName, ROUND(AVG(t.Value), 2) AS AvgTreasureValue
FROM Quests AS q
INNER JOIN Treasure AS t ON q.QuestID = t.QuestID
GROUP BY q.QuestLeader, q.QuestName
ORDER BY AvgTreasureValue DESC;

5. 
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID)

Tiana_Holt · February 27, 2025, 4:29am

Time: 25
Difficulty: medium
Solution:

SELECT DISTINCT QuestLeader
FROM Quests;
SELECT TypeName, Sum(TreasureValue) AS TotalValue
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Sum(TreasureValue) DESC;
SELECT TOP 5 QuestName, Sum(TreasureValue) AS TotalLoot
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestName
ORDER BY Sum(TreasureValue) DESC;
SELECT QuestLeader, QuestName, Round(Avg(TreasureValue), 2) AS AvgTreasureValue
FROM Quests AS q INNER JOIN Treasures AS t ON t.QuestID = q.QuestID
GROUP BY QuestLeader, QuestName
ORDER BY Avg(TreasureValue) DESC;
SELECT TypeName, Count(t.TypeID) AS CountFound
FROM Treasures AS t INNER JOIN Treasure_Types AS tt ON t.TypeID = tt.TypeID
GROUP BY TypeName
ORDER BY Count(t.TypeID);

Jacob_Critchfield · February 27, 2025, 5:15am

Time to Complete: 20
Difficulty: Medium

Select Distinct QuestLeader
from quests;

Select TypeName, Sum(TreasureValue) as TotalValue
From Treasures t
Inner Join Treasure_Types tt on t.typeID = tt.type_id
group by TypeName
Order by sum(treasureValue) desc;

Select Top 5 QuestName, Sum(TreasureValue) as TotalLoot
from quests q
inner join Treasures t on q.questID = t.questID
group by QuestName
Order by Sum(TreasureValue) desc;

Select QuestLeader, QuestName, Round(Avg(TreasureValue), 2) as AvgTreasureValue
From quests q
inner join Treasures t on t.questID = q.questID
Group by questLeader, questName
Order by avg(TreasureValue) Desc;

Select TypeName, Count(t.typeID) as CountFound
From Treasures t
inner join Treasure_Types tt on t.typeid = tt.typeID
Group by TypeName
Order by Count(t.typeID);

Topic		Replies	Views
199\|SQL – LOTR Ledgers Challenges beginner , sql	3	97	March 15, 2025
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	992	February 27, 2025
105\|EXCEL – Buried Treasure Challenges excel , intermediate , power-query , other	2	183	July 8, 2024
168\|SQL – Trading Card Treasure Trove Challenges beginner , sql , consulting , valuation , trading-cards	44	299	March 16, 2025
169\|SQL – Workforce Wisdom Challenges beginner , sql , hr , new-hires	66	318	February 27, 2025

166|SQL – The Guild of Gold

Related topics