employees.en.db

query.sql

select
  dense_rank() over w as rank,
  name, department, salary
from employees
window w as (order by salary desc)
order by rank, id;

schema.sql

BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
CREATE TABLE IF NOT EXISTS employees (
    id integer primary key,
    name text,
    city text,
    department text,
    salary integer
);
CREATE TABLE IF NOT EXISTS expenses (
    year integer,
    month integer,
    income integer,
    expense integer
);
INSERT INTO "employees" VALUES(11,'Diane','London','hr',70);
INSERT INTO "employees" VALUES(12,'Bob','London','hr',78);
INSERT INTO "employees" VALUES(21,'Emma','London','it',84);
INSERT INTO "employees" VALUES(22,'Grace','Berlin','it',90);
INSERT INTO "employees" VALUES(23,'Henry','London','it',104);
INSERT INTO "employees" VALUES(24,'Irene','Berlin','it',104);
INSERT INTO "employees" VALUES(25,'Frank','Berlin','it',120);
INSERT INTO "employees" VALUES(31,'Cindy','Berlin','sales',96);
INSERT INTO "employees" VALUES(32,'Dave','London','sales',96);
INSERT INTO "employees" VALUES(33,'Alice','Berlin','sales',100);
INSERT INTO "expenses" VALUES(2020,1,94,82);
INSERT INTO "expenses" VALUES(2020,2,94,75);
INSERT INTO "expenses" VALUES(2020,3,94,104);
INSERT INTO "expenses" VALUES(2020,4,100,94);
INSERT INTO "expenses" VALUES(2020,5,100,99);
INSERT INTO "expenses" VALUES(2020,6,100,105);
INSERT INTO "expenses" VALUES(2020,7,100,95);
INSERT INTO "expenses" VALUES(2020,8,100,110);
INSERT INTO "expenses" VALUES(2020,9,104,104);
PRAGMA writable_schema=OFF;
COMMIT;