Skip to content

Instantly share code, notes, and snippets.

@nalgeon
Created September 23, 2021 13:45
Show Gist options
  • Save nalgeon/e012594111ce51f91590c4737e41a046 to your computer and use it in GitHub Desktop.
Save nalgeon/e012594111ce51f91590c4737e41a046 to your computer and use it in GitHub Desktop.
employees.en.db
select
dense_rank() over w as rank,
name, department, salary
from employees
window w as (order by salary desc)
order by rank, id;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment