Changes between Version 1 and Version 2 of AdvancedReports


Ignore:
Timestamp:
01/30/25 16:59:39 (3 weeks ago)
Author:
211585
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v1 v2  
    1111    EXTRACT(YEAR FROM pmr.Date_Created) AS Year,
    1212    COUNT(DISTINCT pmr.RequestID) AS Total_Profile_Requests,
    13     COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'Completed') AS Completed_Profile_Requests,
    14     COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'In Progress') AS InProgress_Profile_Requests,
    15     COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'Pending') AS Pending_Profile_Requests,
     13    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'completed') AS Completed_Profile_Requests,
     14    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'in progress') AS InProgress_Profile_Requests,
     15    COUNT(DISTINCT pmr.RequestID) FILTER (WHERE pmr.Status = 'pending') AS Pending_Profile_Requests,
    1616    SUM(pmr.Target_Followers) AS Total_Target_Followers,
    1717    AVG(pmr.Target_Followers) AS Average_Target_Followers,
    18     SUM(pmr.Target_Followers) - SUM(sp.Followers_Count) AS Followers_Gap
     18    SUM(pmr.Target_Followers) - SUM(smp.followers_count) AS Followers_Gap
    1919
    2020FROM
    21     user u
    22     LEFT JOIN social_media_profile smp ON smp.UserID = u.UserID
    23     LEFT JOIN profile_marketing_request pmr ON pmr.ProfileID = smp.ProfileID
    24     LEFT JOIN service_provider sp ON sp.ProviderID = pmr.ProviderID
     21    app_user u
     22    LEFT JOIN socialmediaprofile smp ON smp.UserID = u.UserID
     23    LEFT JOIN profilemarketingrequest pmr ON pmr.ProfileID = smp.ProfileID
     24    LEFT JOIN serviceprovider sp ON sp.ProviderID = pmr.ProviderID
    2525WHERE
    2626    pmr.Date_Created >= DATE_TRUNC('year', CURRENT_DATE) -- Optional: Filter by the current year
    … …  
    3737
    3838
    39 == Total Marketing Requests per User ==
    40 
    41 This query retrieves the total number of marketing requests submitted by each user.
    42 
    43 {{{
    44 SELECT
    45     u.user_id,
    46     u.username,
    47     COUNT(DISTINCT pmr.request_id) AS profile_requests,
    48     COUNT(DISTINCT ppr.request_id) AS post_requests,
    49     (COUNT(DISTINCT pmr.request_id) + COUNT(DISTINCT ppr.request_id)) AS total_requests
    50 FROM users u
    51 LEFT JOIN profile_marketing_requests pmr ON u.user_id = pmr.user_id
    52 LEFT JOIN post_marketing_requests ppr ON u.user_id = ppr.user_id
    53 GROUP BY u.user_id, u.username;
    54 
    55 }}}
    56 
    5739==  Track Progress of Profile Marketing Request ==
    5840This SQL report will track the progress for profile marketing requests and provide a detailed breakdown of the progress made, including the percentage of completion.
    … …  
    6951    pmr.Timeline AS Goal_Timeline,
    7052    pmr.Date_Created AS Request_Creation_Date,
    71     DATEDIFF(CURRENT_DATE, pmr.Date_Created) AS Days_Since_Creation,
    72     DATEDIFF(pmr.Timeline, CURRENT_DATE) AS Days_Remaining
     53    EXTRACT(DAY FROM CURRENT_DATE - pmr.Date_Created) AS Days_Since_Creation,
     54    EXTRACT(DAY FROM pmr.Timeline - CURRENT_DATE) AS Days_Remaining
    7355FROM
    74     profile_marketing_request pmr
     56    profilemarketingrequest pmr
    7557JOIN
    76     social_media_profile smp ON smp.ProfileID = pmr.ProfileID
     58    socialmediaprofile smp ON smp.ProfileID = pmr.ProfileID
    7759JOIN
    78     user u ON u.UserID = smp.UserID
     60    app_user u ON u.UserID = smp.UserID
    7961WHERE
    80     pmr.Status IN ('In Progress', 'Pending')
     62    pmr.Status IN ('in progress', 'pending')
    8163ORDER BY
    8264    pmr.Date_Created DESC;
    83 
    84 }}}
    85 
    86 ==  Track Progress of Profile Marketing Request ==
     65}}}
     66
     67==  Track Progress of Post Marketing Request ==
    8768This SQL report will track the progress for post marketing requests and provide a detailed breakdown of the progress made, including the percentage of completion.
    8869
    … …  
    9677    pmr.Target_Comments,
    9778    -- Calculate percentage of likes and comments achieved
    98     ROUND((p.Likes_Count / pmr.Target_Likes) * 100, 2) AS Likes_Progress_Percentage,
    99     ROUND((p.Comments_Count / pmr.Target_Comments) * 100, 2) AS Comments_Progress_Percentage,
     79    ROUND((p.Likes_Count::decimal / pmr.Target_Likes::decimal) * 100, 2) AS Likes_Progress_Percentage,
     80    ROUND((p.Comments_Count::decimal / pmr.Target_Comments::decimal) * 100, 2) AS Comments_Progress_Percentage,
    10081    -- Calculate the remaining time in days
    101     DATEDIFF(pmr.Timeline, CURRENT_DATE) AS Days_Remaining,
     82    (pmr.Timeline - CURRENT_DATE) AS Days_Remaining,
    10283    -- Determine the progress status based on the progress
    10384    CASE
    104         WHEN pmr.Status = 'Completed' THEN 'Completed'
     85        WHEN pmr.Status = 'completed' THEN 'completed'
    10586        WHEN (p.Likes_Count >= pmr.Target_Likes AND p.Comments_Count >= pmr.Target_Comments) THEN 'Completed'
    106         WHEN DATEDIFF(pmr.Timeline, CURRENT_DATE) <= 0 THEN 'Deadline Passed'
     87        WHEN (pmr.Timeline < CURRENT_DATE) THEN 'Deadline Passed'
    10788        ELSE 'In Progress'
    10889    END AS Request_Status
    10990FROM
    110     post_marketing_request pmr
     91    postmarketingrequest pmr
    11192JOIN
    11293    post p ON pmr.PostID = p.PostID
    11394WHERE
    114     pmr.Status <> 'Completed'  -- To show only active or pending requests
     95    pmr.Status <> 'completed'  -- To show only active or pending requests
    11596ORDER BY
    11697    pmr.Date_Created DESC;
    … …  
    122103
    123104{{{
    124 DELIMITER $$
    125 
    126 CREATE PROCEDURE TrackMarketingRequestProgress(
    127     IN request_type VARCHAR(50),  -- Type of the request ('profile' or 'post')
    128     IN request_id INT
     105CREATE OR REPLACE FUNCTION TrackMarketingRequestProgress(
     106    request_type VARCHAR,  -- Type of the request ('profile' or 'post')
     107    request_id INT
    129108)
     109RETURNS TABLE (
     110    RequestID INT,
     111    TargetFollowers INT,
     112    CurrentFollowers INT,
     113    ProgressPercentage DECIMAL(5,2),
     114    Status VARCHAR,
     115    Deadline DATE,
     116    DaysRemaining INT
     117) AS $$
     118DECLARE
     119    target INT;
     120    current INT;
     121    progress_percentage DECIMAL(5,2);
     122    request_status VARCHAR;
     123    deadline DATE;
     124    current_date DATE := CURRENT_DATE;  -- Get today's date
    130125BEGIN
    131     DECLARE target INT;
    132     DECLARE current INT;
    133     DECLARE progress_percentage DECIMAL(5,2);
    134     DECLARE request_status VARCHAR(50);
    135     DECLARE deadline DATE;
    136     DECLARE current_date DATE;
    137    
    138     SET current_date = CURDATE();  -- Get today's date
    139    
    140126    -- For profile marketing request
    141127    IF request_type = 'profile' THEN
    142128        -- Retrieve target followers from profile marketing request
    143         SELECT Target_Followers, Followers_Count, Status, Timeline
     129        SELECT pmr.Target_Followers, smp.Followers_Count, pmr.Status, pmr.Timeline
    144130        INTO target, current, request_status, deadline
    145         FROM profile_marketing_request pmr
    146         JOIN social_media_profile smp ON pmr.ProfileID = smp.ProfileID
     131        FROM profilemarketingrequest pmr
     132        JOIN socialmediaprofile smp ON pmr.ProfileID = smp.ProfileID
    147133        WHERE pmr.RequestID = request_id;
    148134
    149135        -- Calculate the percentage of the target achieved
    150         SET progress_percentage = (current / target) * 100;
     136        progress_percentage := (current / target) * 100;
    151137       
    152138        -- Return the progress report
     139        RETURN QUERY
    153140        SELECT
    154141            request_id AS RequestID,
    … …  
    158145            request_status AS Status,
    159146            deadline AS Deadline,
    160             DATEDIFF(deadline, current_date) AS DaysRemaining;
     147            (deadline - current_date) AS DaysRemaining;
    161148   
    162149    -- For post marketing request
    163     ELSEIF request_type = 'post' THEN
     150    ELSIF request_type = 'post' THEN
    164151        -- Retrieve target likes/comments from post marketing request
    165         SELECT Target_Likes, Target_Comments, Likes_Count, Comments_Count, Status, Timeline
     152        SELECT pmr.Target_Likes, pmr.Target_Comments, p.Likes_Count, p.Comments_Count, pmr.Status, pmr.Timeline
    166153        INTO target, current, request_status, deadline
    167         FROM post_marketing_request pmr
     154        FROM postmarketingrequest pmr
    168155        JOIN post p ON pmr.PostID = p.PostID
    169156        WHERE pmr.RequestID = request_id;
    170157
    171158        -- Calculate the percentage of the target achieved
    172         SET progress_percentage = (current / target) * 100;
     159        progress_percentage := (current / target) * 100;
    173160       
    174161        -- Return the progress report
     162        RETURN QUERY
    175163        SELECT
    176164            request_id AS RequestID,
    … …  
    180168            request_status AS Status,
    181169            deadline AS Deadline,
    182             DATEDIFF(deadline, current_date) AS DaysRemaining;
     170            (deadline - current_date) AS DaysRemaining;
    183171    END IF;
    184 END $$
    185 
    186 DELIMITER ;
    187 
     172END;
     173$$ LANGUAGE plpgsql;
    188174}}}
    189175
    … …  
    191177
    192178{{{
    193 CALL TrackMarketingRequestProgress('profile', 101);
    194 CALL TrackMarketingRequestProgress('post', 202);
    195 }}}
    196 
    197 
    198 == Most Popular Marketing Goal ==
     179SELECT * FROM TrackMarketingRequestProgress('profile', 101);
     180SELECT * FROM TrackMarketingRequestProgress('post', 202);
     181}}}
     182Expected output for a Profile Marketing Request:
     183||**RequestID** ||**TargetFollowers**   ||**CurrentFollowers**  ||**ProgressPercentage**        ||**Status**    ||**Deadline**  ||DaysRemaining||
     184||101   ||5000  ||3000  ||60.00 ||In Progress   ||2025-02-15    ||16||
     185
     186Expected output for a Post Marketing Request:
     187||**RequestID** ||**TargetLikesComments**       ||**CurrentLikesComments**      ||**ProgressPercentage**        ||**Status**    ||**Deadline**  ||**DaysRemaining**||
     188||202   ||2000  ||1200  ||60.00 ||In Progress   ||2025-02-10    ||11||
     189
     190== Most Popular Marketing Request ==
    199191This 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.
    200192{{{
    … …  
    209201    END AS Target_Value,
    210202    COUNT(*) AS Total_Requests,
    211     AVG(prm.Timeline) AS Avg_Timeline
     203    AVG(
     204        CASE
     205            WHEN pmr.Target_Followers IS NOT NULL THEN
     206                EXTRACT(DAY FROM pmr.Timeline - pmr.Date_Created)  -- Calculating the number of days for profile-based requests
     207            ELSE
     208                EXTRACT(DAY FROM prm.Timeline - prm.Date_Created)  -- Calculating the number of days for post-based requests
     209        END
     210    ) AS Avg_Timeline_Days
    212211FROM
    213     profile_marketing_request pmr
     212    profilemarketingrequest pmr
    214213LEFT JOIN
    215     post_marketing_request prm ON pmr.RequestID = prm.RequestID
     214    postmarketingrequest prm ON pmr.RequestID = prm.RequestID
    216215GROUP BY
    217216    Request_Type,
    … …  
    222221ORDER BY
    223222    Total_Requests DESC, Target_Value DESC;
    224 
    225 }}}
    226 
     223}}}
     224Example output:
     225||**Request_Type** ||   **Target_Value**||      **Total_Requests**||    **Avg_Timeline**||
     226||Profile-Based ||10,000        ||25    ||14 days||
     227||Post-Based    ||5,000 ||18    ||10 days||
     228||Post-Based    ||3,000 ||15    ||7 days||
     229||Profile-Based ||50,000        ||10    ||30 days||
    227230
    228231== Get Monthly Marketing Request Statistics ==
    … …  
    230233This stored procedure retrieves monthly statistics for marketing requests, separating profile-based and post-based requests.
    231234{{{
    232 DELIMITER //
    233 
    234 CREATE PROCEDURE GetMonthlyMarketingRequestStatistics(IN targetMonth INT, IN targetYear INT)
     235CREATE OR REPLACE FUNCTION GetMonthlyMarketingRequestStatistics(targetMonth INT, targetYear INT)
     236RETURNS TABLE (
     237    total_requests INT,
     238    total_completed_requests INT,
     239    avg_timeline DECIMAL,
     240    total_target_followers INT,
     241    total_target_likes INT,
     242    total_target_comments INT
     243) AS $$
    235244BEGIN
    236     -- Declare variables to hold the statistics
    237     DECLARE totalRequests INT;
    238     DECLARE totalCompletedRequests INT;
    239     DECLARE avgTimeline DECIMAL(10, 2);
    240     DECLARE totalTargetFollowers INT;
    241     DECLARE totalTargetLikes INT;
    242     DECLARE totalTargetComments INT;
    243 
    244     -- Get the total number of marketing requests in the given month
    245     SELECT COUNT(*) INTO totalRequests
    246     FROM profile_marketing_request
    247     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear
    248     UNION ALL
    249     SELECT COUNT(*)
    250     FROM post_marketing_request
    251     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
    252 
    253     -- Get the total number of completed requests
    254     SELECT COUNT(*) INTO totalCompletedRequests
    255     FROM profile_marketing_request
    256     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear AND Status = 'Completed'
    257     UNION ALL
    258     SELECT COUNT(*)
    259     FROM post_marketing_request
    260     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear AND Status = 'Completed';
    261 
    262     -- Get the average timeline for marketing requests in the given month
    263     SELECT AVG(DATEDIFF(NOW(), Date_Created)) INTO avgTimeline
    264     FROM profile_marketing_request
    265     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear
    266     UNION ALL
    267     SELECT AVG(DATEDIFF(NOW(), Date_Created))
    268     FROM post_marketing_request
    269     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
    270 
    271     -- Get the total target followers requested in the given month
    272     SELECT SUM(Target_Followers) INTO totalTargetFollowers
    273     FROM profile_marketing_request
    274     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
    275 
    276     -- Get the total target likes and comments requested in the given month
    277     SELECT SUM(Target_Likes) INTO totalTargetLikes
    278     FROM post_marketing_request
    279     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
    280 
    281     SELECT SUM(Target_Comments) INTO totalTargetComments
    282     FROM post_marketing_request
    283     WHERE MONTH(Date_Created) = targetMonth AND YEAR(Date_Created) = targetYear;
    284 
    285     -- Output the results
    286     SELECT
    287         totalRequests AS "Total Requests",
    288         totalCompletedRequests AS "Completed Requests",
    289         avgTimeline AS "Average Timeline (Days)",
    290         totalTargetFollowers AS "Total Target Followers",
    291         totalTargetLikes AS "Total Target Likes",
    292         totalTargetComments AS "Total Target Comments";
    293 
    294 END //
    295 
    296 DELIMITER ;
     245    -- Calculate total number of marketing requests
     246    SELECT
     247        COUNT(*)
     248    INTO total_requests
     249    FROM (
     250        SELECT Date_Created FROM profilemarketingrequest
     251        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     252          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     253        UNION ALL
     254        SELECT Date_Created FROM postmarketingrequest
     255        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     256          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     257    ) AS combined_requests;
     258
     259    -- Calculate total number of completed requests
     260    SELECT
     261        COUNT(*)
     262    INTO total_completed_requests
     263    FROM (
     264        SELECT Date_Created FROM profilemarketingrequest
     265        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     266          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     267          AND Status = 'completed'
     268        UNION ALL
     269        SELECT Date_Created FROM postmarketingrequest
     270        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     271          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     272          AND Status = 'completed'
     273    ) AS completed_requests;
     274
     275    -- Calculate the average timeline for requests
     276    SELECT
     277        AVG(EXTRACT(DAY FROM NOW() - Date_Created))
     278    INTO avg_timeline
     279    FROM (
     280        SELECT Date_Created FROM profilemarketingrequest
     281        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     282          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     283        UNION ALL
     284        SELECT Date_Created FROM postmarketingrequest
     285        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     286          AND EXTRACT(YEAR FROM Date_Created) = targetYear
     287    ) AS timeline_requests;
     288
     289    -- Calculate total target followers for profile-based requests
     290    SELECT
     291        SUM(Target_Followers)
     292    INTO total_target_followers
     293    FROM profilemarketingrequest
     294    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     295      AND EXTRACT(YEAR FROM Date_Created) = targetYear;
     296
     297    -- Calculate total target likes for post-based requests
     298    SELECT
     299        SUM(Target_Likes)
     300    INTO total_target_likes
     301    FROM postmarketingrequest
     302    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     303      AND EXTRACT(YEAR FROM Date_Created) = targetYear;
     304
     305    -- Calculate total target comments for post-based requests
     306    SELECT
     307        SUM(Target_Comments)
     308    INTO total_target_comments
     309    FROM postmarketingrequest
     310    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
     311      AND EXTRACT(YEAR FROM Date_Created) = targetYear;
     312
     313    -- Return the results as a record
     314    RETURN QUERY
     315    SELECT total_requests, total_completed_requests, avg_timeline,
     316           total_target_followers, total_target_likes, total_target_comments;
     317END;
     318$$ LANGUAGE plpgsql;
     319
     320SELECT * FROM GetMonthlyMarketingRequestStatistics(1, 2024);
    297321
    298322}}}
    … …  
    301325
    302326{{{
    303 CALL GetMonthlyMarketingRequestStatistics(1, 2025);
    304 }}}
    305 
    306 
     327SELECT * FROM GetMonthlyMarketingRequestStatistics(1, 2024);
     328}}}
     329
     330