Each store's number of request, including how many have been solved, and how many are still in progress

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

Relational Algebra

• S(store_id, name, date_of_founding, physical_address, store_email, rating)
• FS(request_num, store_id)
• R(request_num, date_and_time, problem, notes_of_communication, customer_satisfaction)

JOIN stores with their requests:

• J1 ← S ⟕S.store_id = FS.store_id FS
• J2 ← J1 ⟕FS.request_num = R.request_num R

Calculate total number of requests for each store:

• total_requests = COUNT(request_num)

Calculate solved and unsolved requests:

• solved_requests = COUNT(request_num) WHERE customer_satisfaction IS NOT NULL
• requests_in_progress = COUNT(request_num) WHERE customer_satisfaction IS NULL
• R1 ← γstore_id, name;

COUNT(request_num) → total_requests, COUNT(customer_satisfaction) → solved_requests, COUNT(request_num) - COUNT(customer_satisfaction)

→ requests_in_progress

(J2)

Sort by total number of requests:

• R_final ← τtotal_requests DESC(R1)