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(smp.followers_count) AS Followers_Gap

FROM 
    app_user u
    LEFT JOIN socialmediaprofile smp ON smp.UserID = u.UserID
    LEFT JOIN profilemarketingrequest pmr ON pmr.ProfileID = smp.ProfileID
    LEFT JOIN serviceprovider 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;

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,
    EXTRACT(DAY FROM CURRENT_DATE - pmr.Date_Created) AS Days_Since_Creation,
    EXTRACT(DAY FROM pmr.Timeline - CURRENT_DATE) AS Days_Remaining
FROM 
    profilemarketingrequest pmr
JOIN 
    socialmediaprofile smp ON smp.ProfileID = pmr.ProfileID
JOIN 
    app_user u ON u.UserID = smp.UserID
WHERE 
    pmr.Status IN ('in progress', 'pending')
ORDER BY 
    pmr.Date_Created DESC;

Track Progress of Post 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::decimal / pmr.Target_Likes::decimal) * 100, 2) AS Likes_Progress_Percentage,
    ROUND((p.Comments_Count::decimal / pmr.Target_Comments::decimal) * 100, 2) AS Comments_Progress_Percentage,
    -- Calculate the remaining time in days
    (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 (pmr.Timeline < CURRENT_DATE) THEN 'Deadline Passed'
        ELSE 'In Progress'
    END AS Request_Status
FROM 
    postmarketingrequest 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

CREATE OR REPLACE FUNCTION TrackMarketingRequestProgress(
    request_type VARCHAR,  -- Type of the request ('profile' or 'post')
    request_id INT
)
RETURNS TABLE (
    RequestID INT,
    TargetFollowers INT,
    CurrentFollowers INT,
    ProgressPercentage DECIMAL(5,2),
    Status VARCHAR,
    Deadline DATE,
    DaysRemaining INT
) AS $$
DECLARE
    target INT;
    current INT;
    progress_percentage DECIMAL(5,2);
    request_status VARCHAR;
    deadline DATE;
    current_date DATE := CURRENT_DATE;  -- Get today's date
BEGIN
    -- For profile marketing request
    IF request_type = 'profile' THEN
        -- Retrieve target followers from profile marketing request
        SELECT pmr.Target_Followers, smp.Followers_Count, pmr.Status, pmr.Timeline
        INTO target, current, request_status, deadline
        FROM profilemarketingrequest pmr
        JOIN socialmediaprofile smp ON pmr.ProfileID = smp.ProfileID
        WHERE pmr.RequestID = request_id;

        -- Calculate the percentage of the target achieved
        progress_percentage := (current / target) * 100;
        
        -- Return the progress report
        RETURN QUERY
        SELECT 
            request_id AS RequestID,
            target AS TargetFollowers,
            current AS CurrentFollowers,
            progress_percentage AS ProgressPercentage,
            request_status AS Status,
            deadline AS Deadline,
            (deadline - current_date) AS DaysRemaining;
    
    -- For post marketing request
    ELSIF request_type = 'post' THEN
        -- Retrieve target likes/comments from post marketing request
        SELECT pmr.Target_Likes, pmr.Target_Comments, p.Likes_Count, p.Comments_Count, pmr.Status, pmr.Timeline
        INTO target, current, request_status, deadline
        FROM postmarketingrequest pmr
        JOIN post p ON pmr.PostID = p.PostID
        WHERE pmr.RequestID = request_id;

        -- Calculate the percentage of the target achieved
        progress_percentage := (current / target) * 100;
        
        -- Return the progress report
        RETURN QUERY
        SELECT 
            request_id AS RequestID,
            target AS TargetLikesComments,
            current AS CurrentLikesComments,
            progress_percentage AS ProgressPercentage,
            request_status AS Status,
            deadline AS Deadline,
            (deadline - current_date) AS DaysRemaining;
    END IF;
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT * FROM TrackMarketingRequestProgress('profile', 101);
SELECT * FROM TrackMarketingRequestProgress('post', 202);

Expected output for a Profile Marketing Request:

RequestID	TargetFollowers	CurrentFollowers	ProgressPercentage	Status	Deadline	DaysRemaining
101	5000	3000	60.00	In Progress	2025-02-15	16

Expected output for a Post Marketing Request:

RequestID	TargetLikesComments	CurrentLikesComments	ProgressPercentage	Status	Deadline	DaysRemaining
202	2000	1200	60.00	In Progress	2025-02-10	11

Most Popular Marketing Request

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(
        CASE 
            WHEN pmr.Target_Followers IS NOT NULL THEN 
                EXTRACT(DAY FROM pmr.Timeline - pmr.Date_Created)  -- Calculating the number of days for profile-based requests
            ELSE
                EXTRACT(DAY FROM prm.Timeline - prm.Date_Created)  -- Calculating the number of days for post-based requests
        END
    ) AS Avg_Timeline_Days
FROM 
    profilemarketingrequest pmr
LEFT JOIN 
    postmarketingrequest 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;

Example output:

Request_Type	Target_Value	Total_Requests	Avg_Timeline
Profile-Based	10,000	25	14 days
Post-Based	5,000	18	10 days
Post-Based	3,000	15	7 days
Profile-Based	50,000	10	30 days

Get Monthly Marketing Request Statistics

This stored procedure retrieves monthly statistics for marketing requests, separating profile-based and post-based requests.

CREATE OR REPLACE FUNCTION GetMonthlyMarketingRequestStatistics(targetMonth INT, targetYear INT)
RETURNS TABLE (
    total_requests INT,
    total_completed_requests INT,
    avg_timeline DECIMAL,
    total_target_followers INT,
    total_target_likes INT,
    total_target_comments INT
) AS $$
BEGIN
    -- Calculate total number of marketing requests
    SELECT 
        COUNT(*) 
    INTO total_requests
    FROM (
        SELECT Date_Created FROM profilemarketingrequest 
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth 
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
        UNION ALL
        SELECT Date_Created FROM postmarketingrequest
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth 
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
    ) AS combined_requests;

    -- Calculate total number of completed requests
    SELECT 
        COUNT(*)
    INTO total_completed_requests
    FROM (
        SELECT Date_Created FROM profilemarketingrequest 
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth 
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
          AND Status = 'completed'
        UNION ALL
        SELECT Date_Created FROM postmarketingrequest 
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth 
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
          AND Status = 'completed'
    ) AS completed_requests;

    -- Calculate the average timeline for requests
    SELECT 
        AVG(EXTRACT(DAY FROM NOW() - Date_Created))
    INTO avg_timeline
    FROM (
        SELECT Date_Created FROM profilemarketingrequest
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
        UNION ALL
        SELECT Date_Created FROM postmarketingrequest
        WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
          AND EXTRACT(YEAR FROM Date_Created) = targetYear
    ) AS timeline_requests;

    -- Calculate total target followers for profile-based requests
    SELECT 
        SUM(Target_Followers)
    INTO total_target_followers
    FROM profilemarketingrequest
    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
      AND EXTRACT(YEAR FROM Date_Created) = targetYear;

    -- Calculate total target likes for post-based requests
    SELECT 
        SUM(Target_Likes)
    INTO total_target_likes
    FROM postmarketingrequest
    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
      AND EXTRACT(YEAR FROM Date_Created) = targetYear;

    -- Calculate total target comments for post-based requests
    SELECT 
        SUM(Target_Comments)
    INTO total_target_comments
    FROM postmarketingrequest
    WHERE EXTRACT(MONTH FROM Date_Created) = targetMonth
      AND EXTRACT(YEAR FROM Date_Created) = targetYear;

    -- Return the results as a record
    RETURN QUERY 
    SELECT total_requests, total_completed_requests, avg_timeline, 
           total_target_followers, total_target_likes, total_target_comments;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM GetMonthlyMarketingRequestStatistics(1, 2024);

Usage:

SELECT * FROM GetMonthlyMarketingRequestStatistics(1, 2024);