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

{{{#!sql
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)