I have a relational database with below tables and attributes:

`Beneficiary (ID, name, address, city_ID)

Application (ID, request_ID, volunteer_ID, modified, is_valid)

Request (ID, beneficiary_ID, title, number_of_volunteers_needed, priority_value, start_date,      end_date, register_by_date)

Request_skill (request_ID, skill_name, min_need, value)

Request_location (request_ID, city_ID)

Volunteer (ID, birthdate, city_ID, name, email, address, travel_readiness)

Interest (name)

Skill (name, description)

City (ID, name, geolocation)

VolunteerRange (volunteer_ID, city_ID)

Interest_assignment (volunteer_ID, interest_name)

Skill_assignment (volunteer_ID, skill_name)`

I would like to create a scoring system to calculate the matching percentage
from all the attributes of a volunteer: e.g volunteer_interest, travel
readiness, volunteer_range, number of skill matches between volunteer’s skills and request’s skills, age,..etc and then suggest top 5 candidates for each request.

Below is my draft code in sql. Appreciate if everyone can give advices on how to build suitable scoring system and improve my code logic.

`WITH Skill_Match AS ( SELECT v.id AS volunteer_id, r.id AS request_id, COUNT(rs.skill_name) AS matched_skills FROM Volunteer v JOIN Skill_Assignment sa ON v.id = sa.volunteer_id JOIN Request_Skill rs ON sa.skill_name = rs.skill_name WHERE rs.request_id = r.id GROUP BY v.id, r.id ), Interest_Match AS ( SELECT v.id AS volunteer_id, r.id AS request_id, COUNT(ia.interest_name) AS matched_interests FROM Volunteer v JOIN Interest_Assignment ia ON v.id = ia.volunteer_id JOIN Request_Interest ri ON ia.interest_name = ri.interest_name WHERE ri.request_id = r.id GROUP BY v.id, r.id ), Travel_Score AS ( SELECT v.id AS volunteer_id, v.travel_readiness AS travel_score FROM Volunteer v ), Volunteer_Range AS ( SELECT v.id AS volunteer_id, r.id AS request_id, CASE WHEN v.city_id = rl.city_id THEN 15 WHEN v.city_id IN (SELECT neighboring_city_id FROM City_Neighbors WHERE city_id = rl.city_id) THEN 10 ELSE 0 END AS range_score FROM Volunteer v JOIN Request_Location rl ON rl.request_id = r.id ), Previous_Experience AS ( SELECT v.id AS volunteer_id, r.id AS request_id, CASE WHEN EXISTS (SELECT 1 FROM Application a WHERE a.volunteer_id = v.id AND a.beneficiary_id = r.beneficiary_id) THEN 10 ELSE 0 END AS experience_score FROM Volunteer v JOIN Request r ON r.beneficiary_id = v.id ) SELECT r.id AS request_id, v.id AS volunteer_id, (sm.matched_skills * 40.0 / (SELECT COUNT(*) FROM Request_Skill WHERE request_id = r.id)) + (im.matched_interests * 20.0 / (SELECT COUNT(*) FROM Request_Interest WHERE request_id = r.id)) + ts.travel_score + vr.range_score + pe.experience_score AS total_score FROM Request r JOIN Volunteer v ON v.id = r.id JOIN Skill_Match sm ON sm.volunteer_id = v.id AND sm.request_id = r.id JOIN Interest_Match im ON im.volunteer_id = v.id AND im.request_id = r.id JOIN Travel_Score ts ON ts.volunteer_id = v.id JOIN Volunteer_Range vr ON vr.volunteer_id = v.id AND vr.request_id = r.id JOIN Previous_Experience pe ON pe.volunteer_id = v.id AND pe.request_id = r.id ORDER BY r.id, total_score DESC LIMIT 5;`