LeetCode 184. Department Highest Salary

Tutd Schema:

employee :: {id Integer, name Text, salary Integer, departmentId Integer};
employee := relation{tuple{id 1, name "Joe", salary 70000, departmentId 1},
                    tuple{id 2, name "Jim", salary 90000, departmentId 1},
                    tuple{id 3, name "Henry", salary 80000, departmentId 2},
                    tuple{id 4, name "Sam", salary 60000, departmentId 2},
                    tuple{id 5, name "Max", salary 90000, departmentId 1}};
key employee_pkey {id} employee;

department :: {id Integer, name Text};
department := relation{tuple{id 1, name "IT"}, tuple{id 2, name "Sales"}}
key department_pkey {id} department;

Thinking Process:

TutorialD (master/main): grouped := employee group ({all but departmentId} as byDepartment) 

TutorialD (master/main): :showexpr grouped

┌───────────────────────────────────────────────────────────────┬─────────────────────┐                                                             
│byDepartment::relation {id::Integer,name::Text,salary::Integer}│departmentId::Integer│                                                             
├───────────────────────────────────────────────────────────────┼─────────────────────┤                                                             
│┌───────────┬──────────┬───────────────┐                       │1                    │                                                             
││id::Integer│name::Text│salary::Integer│                       │                     │                                                             
│├───────────┼──────────┼───────────────┤                       │                     │                                                             
││1          │"Joe"     │70000          │                       │                     │                                                             
││5          │"Max"     │90000          │                       │                     │                                                             
││2          │"Jim"     │90000          │                       │                     │                                                             
│└───────────┴──────────┴───────────────┘                       │                     │                                                             
│┌───────────┬──────────┬───────────────┐                       │2                    │                                                             
││id::Integer│name::Text│salary::Integer│                       │                     │                                                             
│├───────────┼──────────┼───────────────┤                       │                     │                                                             
││3          │"Henry"   │80000          │                       │                     │                                                             
││4          │"Sam"     │60000          │                       │                     │                                                             
│└───────────┴──────────┴───────────────┘                       │                     │
└───────────────────────────────────────────────────────────────┴─────────────────────┘

But I can't do this:

 

TutorialD (master/main): :showexpr grouped:{highest := @byDepartment where salary = max(@byDepartment{salary})}
____________________________________________________________________^
ERR:offset=44:
unexpected 'w'
expecting ',' or '}'

RelationAtom in an attribute can not be reused in AtomExpr. Maybe it's a good new feature. It will be like relational expression function mapped on RelationAtom attributes.

Second try, trying to make an extended attributes, trying to bypass group operation:

@ is not what I think.

I guess @departmentId is attributes of the most closed relavar.

TutorialD (master/main): :showexpr employee:{highest_salary_in_depart := max((employee where departmentId = @departmentId){salary})}
┌─────────────────────┬─────────────────────────────────┬───────────┬──────────┬───────────────┐
│departmentId::Integer│highest_salary_in_depart::Integer│id::Integer│name::Text│salary::Integer│
├─────────────────────┼─────────────────────────────────┼───────────┼──────────┼───────────────┤
│1                    │90000                            │2          │"Jim"     │90000          │
│1                    │90000                            │1          │"Joe"     │70000          │
│2                    │90000                            │3          │"Henry"   │80000          │
│2                    │90000                            │4          │"Sam"     │60000          │
│1                    │90000                            │5          │"Max"     │90000          │
└─────────────────────┴─────────────────────────────────┴───────────┴──────────┴───────────────┘

                                                                                                         

Okay, third time is a charm.

 

First, only focus on the relationship between departmentId and grouped salaries. Use extend to find max salary in group.

 

TutorialD (master/main): highest_in_depart := (((employee{departmentId, salary}) group ({salary} as salaries)):{highest_salary := max(@salaries)}){all but salaries}
 

TutorialD (master/main): :showexpr highest_in_depart                                                                                                                                             
┌─────────────────────┬───────────────────────┐                                                                                                     
│departmentId::Integer│highest_salary::Integer│                                                                                                     
├─────────────────────┼───────────────────────┤                                                                                                     
│1                    │90000                  │
│2                    │80000                  │                                                                                                     
└─────────────────────┴───────────────────────┘  

 

Then, join employee and highest_in_depart with departmetnId, then everyone get a highest number in their department to compare.

 

Third, leave people whose salary is the highest number in their own department.                                                                                                  
 

TutorialD (master/main): :showexpr (employee join highest_in_depart) where salary = @highest_salary                                                 
┌─────────────────────┬───────────────────────┬───────────┬──────────┬───────────────┐                                                               
│departmentId::Integer│highest_salary::Integer│id::Integer│name::Text│salary::Integer│                                                              
├─────────────────────┼───────────────────────┼───────────┼──────────┼───────────────┤                                                              
│1                    │90000                  │5          │"Max"     │90000          │                                                              
│1                    │90000                  │2          │"Jim"     │90000          │                                                              
│2                    │80000                  │3          │"Henry"   │80000          │                                                              
└─────────────────────┴───────────────────────┴───────────┴──────────┴───────────────┘