source: database/Advanced Reports for database/Each store's number of request, including how many have been solved, and how many are still in progress.txt@ 6149556

CREATE OR REPLACE FUNCTION get_store_request_statistics()
RETURNS TABLE (
    store_id INT,
    store_name TEXT,
    total_requests BIGINT,
    solved_requests BIGINT,
    requests_in_progress BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.store_id,
        s.name AS store_name,
        COUNT(r.request_num) AS total_requests,
        COUNT(
            CASE
                WHEN r.customer_satisfaction IS NOT NULL
                THEN 1
            END
        ) AS solved_requests,
        COUNT(
            CASE
                WHEN r.customer_satisfaction IS NULL
                THEN 1
            END
        ) AS requests_in_progress
    FROM store s
    LEFT JOIN for_store fs
        ON s.store_id = fs.store_id
    LEFT JOIN request r
        ON fs.request_num = r.request_num
    GROUP BY
        s.store_id,
        s.name
    ORDER BY
        total_requests DESC;
END;
$$;