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);