Changes between Initial Version and Version 1 of analytics-and-statistics

Timestamp:

02/12/25 15:25:09 (10 days ago)

Author:

181557

Comment:

--

analytics-and-statistics

@@ +1 @@
+= Advanced Database Reports (SQL Queries and Stored Procedures) =
+
+This page includes advanced SQL queries and stored procedures for generating reports from the *Legends of the Balkans* game database. The queries are based on the following tables:
+
+== 1. Player Progress Report ==
+This query provides a summary of each player's progress—including their character details and any associated faction reputation.
+
+{{{
+SELECT p.PlayerID,
+       p.Username,
+       c.Name AS CharacterName,
+       c.Health,
+       c.EXP,
+       c.Gold,
+       f.Name AS FactionName,
+       r.ReputationPoints,
+       r.Rank
+FROM Player p
+JOIN Character c ON p.PlayerID = c.PlayerID
+LEFT JOIN Reputation r ON c.CharacterID = r.CharacterID
+LEFT JOIN Faction f ON r.FactionID = f.FactionID;
+}}}
+
+== 2. Faction Reputation Update After Quest Completion ==
+The following stored procedure updates a character’s reputation for a given faction based on the EXP reward from a completed quest.
+
+{{{
+CREATE OR REPLACE FUNCTION UpdateFactionReputation(
+    char_id INT,
+    quest_id INT,
+    faction_id INT
+)
+RETURNS VOID AS
+$$
+DECLARE
+    reputation_change INT;
+BEGIN
+    -- Get the EXP reward from the quest
+    SELECT EXP_Reward INTO reputation_change
+    FROM Quest
+    WHERE QuestID = quest_id;
+
+    -- Update the Reputation record for the character and faction
+    UPDATE Reputation
+    SET ReputationPoints = ReputationPoints + reputation_change
+    WHERE CharacterID = char_id AND FactionID = faction_id;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+== 3. Completed Quests Report for a Specific Player ==
+This query retrieves the list of quests completed by a specific player. (Note: The join table *Character_Quest* contains only the CharacterID and QuestID.)
+
+{{{
+SELECT p.Username,
+       q.Name AS QuestName
+FROM Player p
+JOIN Character c ON p.PlayerID = c.PlayerID
+JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
+JOIN Quest q ON cq.QuestID = q.QuestID
+WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID
+}}}
+
+== 4. List of Players in a Specific Faction ==
+This query lists all players whose characters have a reputation record with a specific faction. Replace the faction name as needed.
+
+{{{
+SELECT DISTINCT p.PlayerID,
+       p.Username,
+       c.Name AS CharacterName
+FROM Player p
+JOIN Character c ON p.PlayerID = c.PlayerID
+JOIN Reputation r ON c.CharacterID = r.CharacterID
+JOIN Faction f ON r.FactionID = f.FactionID
+WHERE f.Name = 'Kingdom of Zeta';  -- Replace with the desired faction name
+}}}
+
+== 5. Market Listings for a Specific Player ==
+This query retrieves all items listed on the market by a specific player.
+
+{{{
+SELECT m.MarketID,
+       i.Name AS ItemName,
+       m.Price
+FROM Market m
+JOIN Item i ON m.ItemID = i.ItemID
+WHERE m.SellerID = ?;  -- Replace ? with the specific Seller's PlayerID
+}}}
+
+== 6. Top Players by Total Experience and Completed Quests ==
+This query provides a list of top players, ordered by the total EXP of all their characters and the number of quests they have completed.
+
+{{{
+SELECT p.PlayerID,
+       p.Username,
+       SUM(c.EXP) AS TotalEXP,
+       COUNT(cq.QuestID) AS CompletedQuests
+FROM Player p
+JOIN Character c ON p.PlayerID = c.PlayerID
+LEFT JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
+GROUP BY p.PlayerID, p.Username
+ORDER BY TotalEXP DESC, CompletedQuests DESC;
+}}}
+
+== 7. Items Owned by a Specific Player ==
+This query lists the items owned by a specific player's characters.
+
+{{{
+SELECT p.Username,
+       i.Name AS ItemName
+FROM Player p
+JOIN Character c ON p.PlayerID = c.PlayerID
+JOIN Character_Item ci ON c.CharacterID = ci.CharacterID
+JOIN Item i ON ci.ItemID = i.ItemID
+WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID
+}}}
+
+== 8. Items Listed in the Market ==
+This query retrieves all items currently listed on the market along with seller information.
+
+{{{
+SELECT m.MarketID,
+       i.Name AS ItemName,
+       m.Price,
+       p.Username AS Seller
+FROM Market m
+JOIN Item i ON m.ItemID = i.ItemID
+JOIN Player p ON m.SellerID = p.PlayerID;
+}}}
+
+== 9. List of Guilds and Their Leaders ==
+This query lists all guilds along with the username of their leader.
+
+{{{
+SELECT g.GuildID,
+       g.Name AS GuildName,
+       p.Username AS LeaderName
+FROM Guild g
+JOIN Player p ON g.LeaderID = p.PlayerID;
+}}}
+
+== 10. Characters in Guilds ==
+This query retrieves characters that belong to guilds, along with their guild name.
+
+{{{
+SELECT p.Username,
+       c.Name AS CharacterName,
+       g.Name AS GuildName
+FROM Character_Guild cg
+JOIN Character c ON cg.CharacterID = c.CharacterID
+JOIN Player p ON c.PlayerID = p.PlayerID
+JOIN Guild g ON cg.GuildID = g.GuildID;
+}}}