PERCENTILE_CONT_and_PERCENTILE_DISC.SQL

CREATE SCHEMA DEVTEST;
DROP TABLE "DEVTEST"."MYEMPLOYEES" CASCADE;
CREATE COLUMN TABLE "DEVTEST"."MYEMPLOYEES" 
(
"EMPID" INTEGER,
"EMP" VARCHAR(20),
"ROLE" VARCHAR(20),
"SALARY" INTEGER
);

INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(1,'Christiano','Manager',510000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(2,'Eric','Manager',360000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(3,'Ryan','Director',450000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(4,'Bryan','Director',155000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(5,'Doreen','Manager',505000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(6,'Gabriel','Manager',355000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(7,'Angel','Director',455000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(8,'Robin','Director',160000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(9,'Sarah','Manager',500000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(10,'Peter','Manager',350000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(11,'Donnie','Salesperson',125000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(12,'Smitty','Salesperson',150000);
INSERT INTO "DEVTEST"."MYEMPLOYEES" VALUES(13,'Gern','Salesperson',130000);

SELECT *
FROM DEVTEST.MYEMPLOYEES
ORDER BY ROLE, SALARY;

SELECT DISTINCT
ROLE,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY) OVER (PARTITION BY ROLE) AS PERC_CONT,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SALARY) OVER (PARTITION BY ROLE) AS PERC_DISC
FROM DEVTEST.MYEMPLOYEES
ORDER BY ROLE