185. Department Top Three Salaries

Tutd Schema:

employee :: { id Integer, name Text, salary Integer, departmentId Integer};
employee := relation{
 tuple{id 1, name "Joe", salary 85000, departmentId 1},
 tuple{id 2, name "Henry", salary 80000, departmentId 2},
 tuple{id 3, name "Sam", salary 60000, departmentId 2},
 tuple{id 4, name "Max", salary 90000, departmentId 1},
 tuple{id 5, name "Janet", salary 69000, departmentId 1},
 tuple{id 6, name "Randy", salary 85000, departmentId 1},
 tuple{id 7, name "Will", salary 70000, departmentId 1}};
key employee_ukey {id} employee;

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

Thinking Process:

-- DataFrame -> Relation is needed to bring top 3 back to a set

-- "map" departmentId by hand

topThreeDep1 := (:showdataframe ((employee where departmentId = 1){salary}) orderby {salary descending}
offset 0 limit 3){all but DF}

topThreeDep2 := (:showdataframe ((employee where departmentId = 2){salary}) orderby {salary descending}
offset 0 limit 3){all but DF}

whoEarnTopThreeIn1 := ((employee where departmentId = 1) join topThreeInDep1) join (de
partment rename {id as departmentId, name as departmentName})

whoEarnTopThreeIn2 := ((employee where departmentId = 2) join topThreeInDep2) join (de
partment rename {id as departmentId, name as departmentName})

answer = whoEarnTopThreeIn1 union whoEarnTopThreeIn2

-- If we can use "let", and bring dataframe back to relation, and test if an atom is in a single attribute, then ...

--pesudo code (And here I see the point that relvar should be allowed on the left side of equation.

:showexpr ((employee{departmentId}:{topThree := (let x = @departmentId in (dataframe (employee where departmentId = x){salary} offset 0 limit 3)})){all but DF} join employee) where relation{tuple{salary @salary}} join topThree = true

-- "where relation{tuple{salary @salary}} join topThree = true" just means that an Atom is in a single-attribute, same-type relation. It could be a useful predicate since It's very often that we want to know if a value is in a set.