MATCH many rows against many rows within SQL query and find percentile match?

Question

At the moment I'm building a recruitment platform. An employer can post a job and receive applications. The employer can set many skill requirements which a jobseeker has to match. The jobseeker can also add many skills which they posses.

What I'm trying to find out is how many of each jobseekers_skills match the employer_requirements for each job in order to display the percentile match in the view. I would ideally like to find the match based on skill_string which exists in both the jobseeker_skills table and employer_requirements table

Here is the database arrangement for each of the 3 tables:

applications:

id | job_string | jobseeker_string | employer_string | application_string | date_created

employer_requirements:

id | skill_name | requirement_level | skill_string | job_string | employer_string | date_created

jobseeker_skills:

id | skill_name | level | jobseeker_string | skill_string | string | date_created

I have the following code which gets all the applications based on the '$job_str' which is passed. The code below is just a simple get but unsure of where to go from here.

function skills_match($job_str){

    $this->db->select('*')
             ->from('applications')
             ->where('job_string', $job_str)
             ->join('users', 'users.string = applications.jobseeker_string', 'left');

    $applications = $this->db->get();

    return $applications;

}

Applications Table - Sample Data: +--------+------------------+------------------+------------------+ | id | job_string | jobseeker_string | employer_string | +--------+------------------+------------------+------------------+ | 1 | vs71FVTBb12DdGlf | uMIsuDJaBuDmo8iq | biQxyPekn6iayIgm | | 2 | vs71FVTBb12DdGlf | x7phHsVnwJ1K1yHy | biQxyPekn6iayIgm | | 3 | vs71FVTBb12DdGlf | Fm1TIJLxz6Xg6QPk | biQxyPekn6iayIgm | +--------+------------------+------+-----+---------+-------+------+

Employer Requirements - Sample Data:

+--------+------------------+-------------+------------------+------------------+ | id | job_string | skill_name | skill_string | employer_string | +--------+------------------+-------------+------------------+-----------------+| | 1 | vs71FVTBb12DdGlf |PHP | 9Y8XeCWqJXzkZ5dD | biQxyPekn6iayIgm | | 2 | vs71FVTBb12DdGlf |JavaScript | O6es19t5CgcRHvct | biQxyPekn6iayIgm | | 3 | vs71FVTBb12DdGlf |HTML | wx4evsXC62BWiN7p | biQxyPekn6iayIgm | | 4 | vs71FVTBb12DdGlf |Python | jx15rH1vrGLmsVmq | biQxyPekn6iayIgm | | 5 | vs71FVTBb12DdGlf |SQL | EksP7mEip0Hs4zKd | biQxyPekn6iayIgm | | 6 | vs71FVTBb12DdGlf |LESS | fj40m4hkiuDGtbzr | biQxyPekn6iayIgm | +--------+------------------+-------------+------+-----+---------+-------+------+ Jobseeker Skills - Sample Data:

+--------+------------------+------------------+------------------+ | id | jobseeker_string | skill_name | skill_string | +--------+------------------+------------------+------------------+ | 1 | uMIsuDJaBuDmo8iq | PHP | 9Y8XeCWqJXzkZ5dD | | 2 | uMIsuDJaBuDmo8iq | Backbone | 4VIiAxZoL1VbPnTa | | 3 | x7phHsVnwJ1K1yHy | LESS | fj40m4hkiuDGtbzr | | 2 | x7phHsVnwJ1K1yHy | Ruby | gTZg4fwYuzMMFcBw | | 3 | x7phHsVnwJ1K1yHy | SQL | EksP7mEip0Hs4zKd | | 1 | Fm1TIJLxz6Xg6QPk | PHP | 9Y8XeCWqJXzkZ5dD | | 2 | Fm1TIJLxz6Xg6QPk | Python | jx15rH1vrGLmsVmq | | 3 | Fm1TIJLxz6Xg6QPk | HTML | wx4evsXC62BWiN7p | | 3 | Fm1TIJLxz6Xg6QPk | Git | aR9B9ns1sHlGrzFw | +--------+------------------+------+-----+---------+-------+------+

Based on the above the this should output either a percentage or the no. of matched skills:

Applications - Below is the number/percentage of matched skills for each application: uMIsuDJaBuDmo8iq - 1/6 (16.666%) x7phHsVnwJ1K1yHy - 2/6 (33.333%) Fm1TIJLxz6Xg6QPk - 3/6 (50%)

Any questions then please fire away. Thanks for your help in advance.

Answer 1

First of all, these are 2 questions:

1. Which of the applicants match my business the best
2. Which of the employers match my skills the best.

The 2 questions might look the same, but they are not.

First question: I want all applicants which match any of my requirements, ordered by the amount of requirements i have. First i get all matches:

select *
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where job_string = 'vs71FVTBb12DdGlf';

Then i group em, count em etc:

select 
  jobseeker_string, 
  count(1) / (select count(1) from Requirements where job_string = 'vs71FVTBb12DdGlf') as match_percentage
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where job_string = 'vs71FVTBb12DdGlf'
group by jobseeker_string;

Second Question: Is a bit more difficult, as the applicant might want to know if he/she matches a certain percentage of the jobs skill, but also of his own skills (this might apply to the first question aswell). Query below:

select 
  job_string, 
  count(1) / (select count(1) from Requirements where jobseeker_string  = 'uMIsuDJaBuDmo8iq') as my_match,
  count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
where jobseeker_string = 'uMIsuDJaBuDmo8iq'
group by job_string;

Please note: query is written out of my head, it might contain some typos

if you want to order by, you could do it like this:

select * from
  ([[insert the above query here]]) t
order by field.

Combined:

select 
  job_string, 
  jobseeker_string
  count(1) / (select count(1) from Requirements where jobseeker_string  = r.jobseeker_string ) as seeker_match,
  count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
from Requirements r 
inner join Jobseeker j
on r.skill_string = j.r.skill_string 
group by job_string, jobseeker_string;

Applicatons

select * from 
  (select 
    job_string, 
    jobseeker_string
    count(1) / (select count(1) from Requirements where jobseeker_string  = r.jobseeker_string ) as seeker_match,
    count(1) / (select count(1) from Requirements where job_string = r.job_string) as job_match
  from Requirements r 
  inner join Jobseeker j
  on r.skill_string = j.r.skill_string 
  group by job_string, jobseeker_string) t
inner join applications a
on t.job_string = a.job_string and t.jobseeker_string = a.t.jobseeker_string