All Tracks Problem

Fredo and Interview Data

Easy-Medium, 簡単-普通


Recently, a Data Analyst Hiring contest was conducted at HackerEarth and our guy Fredo is assigned the task to provide a list of all shortlisted students. He is given three tables:
1. Candidates : It consists of Name of candidate, his UserId and his Skill (in particular Skill rating). All candidates have different UserId and Skills.
2. Problems: It consists of ProblemName and it's Score.
3. Submissions: It consists of UserId and ProblemName. Any row of this table indicates that the particular candidate has solved that problem and will be awarded the score attached to that problem.

The candidates will get shortlisted on the basis of their total scores,i.e., candidate having more total score will be preferred over one having less total score. If two candidates have same total scores, the one having more skill rating will be preferred.
The cutoff score is 50, meaning that only those students having total score atleast 50 can be shortlisted. If there are more than 8 candidates clearing the cut off score , only top 8 would be selected.

The output table should be ordered by total score. In case total score of two candidates is the same, it should be ordered by their skill rating.
Total score of a candidate means the sum of the scores of all the problems solved by him.
It is guaranteed that atleast one candidate will be shorlisted.

Input Format:

Table : Candidates

Field Type
Name text
UserId text
Skills int

Table: Problems

Field Type
ProblemName text
Score int

Table: Submissions

Field Type
UserId text
ProblemName text

Output Format:

Field Type
Name text
UserId text
Total int

Sample Input:

Sample Candidates Table:

Name UserId Skills
Abraham abc 70
Clive def 90
Gabriel ghi 100
John jkl 50

Sample Problems Table:

ProblemName Score
A 10
B 20
C 30
D 40
E 50

Sample Submissions Table:

UserId ProblemName
abc A
def B
ghi C
abc D
jkl D
jkl E

Sample Output:

Name UserId Total
John jkl 90
Abraham abc 50
Time Limit: 5 sec(s) for each input file.
Memory Limit: 256 MB
Source Limit: 1024 KB
Marking Scheme: Marks are awarded when all the testcases pass.
Allowed Languages: MySQL, PostgreSQL, MSSQL, Oracle Database


Initializing Code Editor...
Your Rating:


View All Notifications