Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/30/25 16:27:37 (3 weeks ago)

Author:

211585

Comment:

--

AdvancedReports

@@ +1 @@
+= Advanced database reports (SQL and stored procedures) =
+
+== Client Reports by Quarter ==
+This report provides a summary of client marketing activities by quarter, including the total number of requests, the total target goals, and the progress made for each type of marketing request (Profile Marketing Request and Post Marketing Request).
+
+{{{
+SELECT 
+    u.UserID,
+    u.Username,
+    EXTRACT(QUARTER FROM pmr.Date_Created) AS Quarter,
+    EXTRACT(YEAR FROM pmr.Date_Created) AS Year,
+    COUNT(DISTINCT pmr.RequestID) AS Total_Profile_Requests,
+    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'Completed') AS Completed_Profile_Requests,
+    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'In Progress') AS InProgress_Profile_Requests,
+    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'Pending') AS Pending_Profile_Requests,
+    SUM(pmr.Target_Followers) AS Total_Target_Followers,
+    AVG(pmr.Target_Followers) AS Average_Target_Followers,
+    SUM(pmr.Target_Followers) - SUM(sp.Followers_Count) AS Followers_Gap
+
+FROM 
+    user u
+    LEFT JOIN social_media_profile smp ON smp.UserID = u.UserID
+    LEFT JOIN profile_marketing_request pmr ON pmr.ProfileID = smp.ProfileID
+    LEFT JOIN service_provider sp ON sp.ProviderID = pmr.ProviderID
+WHERE 
+    pmr.Date_Created >= DATE_TRUNC('year', CURRENT_DATE) -- Optional: Filter by the current year
+GROUP BY 
+    u.UserID,
+    u.Username,
+    EXTRACT(QUARTER FROM pmr.Date_Created),
+    EXTRACT(YEAR FROM pmr.Date_Created)
+ORDER BY 
+    Year DESC,
+    Quarter DESC;
+
+}}}
+
+
+== Total Marketing Requests per User ==
+
+This query retrieves the total number of marketing requests submitted by each user.
+
+{{{
+SELECT 
+    u.user_id, 
+    u.username, 
+    COUNT(DISTINCT pmr.request_id) AS profile_requests, 
+    COUNT(DISTINCT ppr.request_id) AS post_requests, 
+    (COUNT(DISTINCT pmr.request_id) + COUNT(DISTINCT ppr.request_id)) AS total_requests
+FROM users u
+LEFT JOIN profile_marketing_requests pmr ON u.user_id = pmr.user_id
+LEFT JOIN post_marketing_requests ppr ON u.user_id = ppr.user_id
+GROUP BY u.user_id, u.username;
+
+}}}
+
+==  Track Progress of Profile Marketing Request ==
+This SQL report will track the progress for profile marketing requests and provide a detailed breakdown of the progress made, including the percentage of completion.
+
+{{{
+SELECT 
+    u.Username AS User_Name,
+    smp.Platform AS Social_Media_Platform,
+    pmr.RequestID AS Marketing_Request_ID,
+    pmr.Target_Followers AS Target_Followers,
+    smp.Followers_Count AS Current_Followers,
+    (smp.Followers_Count / pmr.Target_Followers) * 100 AS Progress_Percentage,
+    pmr.Status AS Request_Status,
+    pmr.Timeline AS Goal_Timeline,
+    pmr.Date_Created AS Request_Creation_Date,
+    DATEDIFF(CURRENT_DATE, pmr.Date_Created) AS Days_Since_Creation,
+    DATEDIFF(pmr.Timeline, CURRENT_DATE) AS Days_Remaining
+FROM 
+    profile_marketing_request pmr
+JOIN 
+    social_media_profile smp ON smp.ProfileID = pmr.ProfileID
+JOIN 
+    user u ON u.UserID = smp.UserID
+WHERE 
+    pmr.Status IN ('In Progress', 'Pending')
+ORDER BY 
+    pmr.Date_Created DESC;
+
+}}}
+
+==  Track Progress of Profile Marketing Request ==
+This SQL report will track the progress for post marketing requests and provide a detailed breakdown of the progress made, including the percentage of completion.
+
+{{{
+SELECT 
+    pmr.RequestID,
+    p.Content AS Post_Content,
+    p.Likes_Count AS Current_Likes,
+    p.Comments_Count AS Current_Comments,
+    pmr.Target_Likes,
+    pmr.Target_Comments,
+    -- Calculate percentage of likes and comments achieved
+    ROUND((p.Likes_Count / pmr.Target_Likes) * 100, 2) AS Likes_Progress_Percentage,
+    ROUND((p.Comments_Count / pmr.Target_Comments) * 100, 2) AS Comments_Progress_Percentage,
+    -- Calculate the remaining time in days
+    DATEDIFF(pmr.Timeline, CURRENT_DATE) AS Days_Remaining,
+    -- Determine the progress status based on the progress
+    CASE
+        WHEN pmr.Status = 'Completed' THEN 'Completed'
+        WHEN (p.Likes_Count >= pmr.Target_Likes AND p.Comments_Count >= pmr.Target_Comments) THEN 'Completed'
+        WHEN DATEDIFF(pmr.Timeline, CURRENT_DATE) <= 0 THEN 'Deadline Passed'
+        ELSE 'In Progress'
+    END AS Request_Status
+FROM 
+    post_marketing_request pmr
+JOIN 
+    post p ON pmr.PostID = p.PostID
+WHERE 
+    pmr.Status <> 'Completed'  -- To show only active or pending requests
+ORDER BY 
+    pmr.Date_Created DESC;
+
+}}}
+
+
+== Procedure: Track Marketing Request Progress == 
+
+{{{
+DELIMITER $$
+
+CREATE PROCEDURE TrackMarketingRequestProgress(
+    IN request_type VARCHAR(50),  -- Type of the request ('profile' or 'post')
+    IN request_id INT
+)
+BEGIN
+    DECLARE target INT;
+    DECLARE current INT;
+    DECLARE progress_percentage DECIMAL(5,2);
+    DECLARE request_status VARCHAR(50);
+    DECLARE deadline DATE;
+    DECLARE current_date DATE;
+    
+    SET current_date = CURDATE();  -- Get today's date
+    
+    -- For profile marketing request
+    IF request_type = 'profile' THEN
+        -- Retrieve target followers from profile marketing request
+        SELECT Target_Followers, Followers_Count, Status, Timeline
+        INTO target, current, request_status, deadline
+        FROM profile_marketing_request pmr
+        JOIN social_media_profile smp ON pmr.ProfileID = smp.ProfileID
+        WHERE pmr.RequestID = request_id;
+
+        -- Calculate the percentage of the target achieved
+        SET progress_percentage = (current / target) * 100;
+        
+        -- Return the progress report
+        SELECT 
+            request_id AS RequestID,
+            target AS TargetFollowers,
+            current AS CurrentFollowers,
+            progress_percentage AS ProgressPercentage,
+            request_status AS Status,
+            deadline AS Deadline,
+            DATEDIFF(deadline, current_date) AS DaysRemaining;
+    
+    -- For post marketing request
+    ELSEIF request_type = 'post' THEN
+        -- Retrieve target likes/comments from post marketing request
+        SELECT Target_Likes, Target_Comments, Likes_Count, Comments_Count, Status, Timeline
+        INTO target, current, request_status, deadline
+        FROM post_marketing_request pmr
+        JOIN post p ON pmr.PostID = p.PostID
+        WHERE pmr.RequestID = request_id;
+
+        -- Calculate the percentage of the target achieved
+        SET progress_percentage = (current / target) * 100;
+        
+        -- Return the progress report
+        SELECT 
+            request_id AS RequestID,
+            target AS TargetLikesComments,
+            current AS CurrentLikesComments,
+            progress_percentage AS ProgressPercentage,
+            request_status AS Status,
+            deadline AS Deadline,
+            DATEDIFF(deadline, current_date) AS DaysRemaining;
+    END IF;
+END $$
+
+DELIMITER ;
+
+}}}
+
+Usage:
+
+{{{
+CALL TrackMarketingRequestProgress('profile', 101);
+CALL TrackMarketingRequestProgress('post', 202);
+}}}
+
+
+== Most Popular Marketing Goal ==
+This report will provide an analysis of which marketing goal (e.g., increasing followers or increasing likes) is the most popular among users. Since there are two types of marketing requests—Profile Marketing Requests and Post Marketing Requests—we will treat them as separate entities and analyze the frequency of each goal type across both entities.
+{{{
+SELECT 
+    CASE 
+        WHEN pmr.Target_Followers IS NOT NULL THEN 'Profile-Based' 
+        ELSE 'Post-Based' 
+    END AS Request_Type,
+    CASE 
+        WHEN pmr.Target_Followers IS NOT NULL THEN pmr.Target_Followers
+        ELSE prm.Target_Likes + prm.Target_Comments
+    END AS Target_Value,
+    COUNT(*) AS Total_Requests,
+    AVG(prm.Timeline) AS Avg_Timeline
+FROM 
+    profile_marketing_request pmr
+LEFT JOIN 
+    post_marketing_request prm ON pmr.RequestID = prm.RequestID
+GROUP BY 
+    Request_Type, 
+    CASE 
+        WHEN pmr.Target_Followers IS NOT NULL THEN pmr.Target_Followers
+        ELSE prm.Target_Likes + prm.Target_Comments
+    END
+ORDER BY 
+    Total_Requests DESC, Target_Value DESC;
+
+}}}
+
+
+== Get Monthly Marketing Request Statistics ==
+
+This stored procedure retrieves monthly statistics for marketing requests, separating profile-based and post-based requests.
+{{{
+DELIMITER //
+
+CREATE PROCEDURE GetMonthlyMarketingRequestStatistics(IN targetMonth INT, IN targetYear INT)
+BEGIN
+    -- Declare variables to hold the statistics
+    DECLARE totalRequests INT;
+    DECLARE totalCompletedRequests INT;
+    DECLARE avgTimeline DECIMAL(10, 2);
+    DECLARE totalTargetFollowers INT;
+    DECLARE totalTargetLikes INT;
+    DECLARE totalTargetComments INT;
+
+    -- Get the total number of marketing requests in the given month
+    SELECT COUNT(*) INTO totalRequests
+    FROM profile_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear
+    UNION ALL
+    SELECT COUNT(*)
+    FROM post_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
+
+    -- Get the total number of completed requests
+    SELECT COUNT(*) INTO totalCompletedRequests
+    FROM profile_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear AND Status = 'Completed'
+    UNION ALL
+    SELECT COUNT(*)
+    FROM post_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear AND Status = 'Completed';
+
+    -- Get the average timeline for marketing requests in the given month
+    SELECT AVG(DATEDIFF(NOW(), Date_Created)) INTO avgTimeline
+    FROM profile_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear
+    UNION ALL
+    SELECT AVG(DATEDIFF(NOW(), Date_Created))
+    FROM post_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
+
+    -- Get the total target followers requested in the given month
+    SELECT SUM(Target_Followers) INTO totalTargetFollowers
+    FROM profile_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
+
+    -- Get the total target likes and comments requested in the given month
+    SELECT SUM(Target_Likes) INTO totalTargetLikes
+    FROM post_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
+
+    SELECT SUM(Target_Comments) INTO totalTargetComments
+    FROM post_marketing_request
+    WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
+
+    -- Output the results
+    SELECT 
+        totalRequests AS "Total Requests", 
+        totalCompletedRequests AS "Completed Requests",
+        avgTimeline AS "Average Timeline (Days)", 
+        totalTargetFollowers AS "Total Target Followers", 
+        totalTargetLikes AS "Total Target Likes",
+        totalTargetComments AS "Total Target Comments";
+
+END //
+
+DELIMITER ;
+
+}}}
+
+Usage:
+
+{{{
+CALL GetMonthlyMarketingRequestStatistics(1, 2025);
+}}}
+
+