Hi Community,
i have to convert one sql which is having windows aggregate function with case statement below is the documents for mongodb and SQL from oracle
i am using mongoDB db version : 8.4
db.employees.insertMany(
[
{“employee_id”:100,“first_name”:“Steven”,“last_name”:“King”,“email”:“SKING”,“phone_number”:“515.123.4567”,“hire_date”:“17-JUN-03”,“job_id”:“AD_PRES”,“salary”:24000,“department_id”:90}
,{“employee_id”:101,“first_name”:“Neena”,“last_name”:“Kochhar”,“email”:“NKOCHHAR”,“phone_number”:“515.123.4568”,“hire_date”:“21-SEP-05”,“job_id”:“AD_VP”,“salary”:17000,“manager_id”:100,“department_id”:90}
,{“employee_id”:102,“first_name”:“Lex”,“last_name”:“De Haan”,“email”:“LDEHAAN”,“phone_number”:“515.123.4569”,“hire_date”:“13-JAN-01”,“job_id”:“AD_VP”,“salary”:17000,“manager_id”:100,“department_id”:90}
,{“employee_id”:103,“first_name”:“Alexander”,“last_name”:“Hunold”,“email”:“AHUNOLD”,“phone_number”:“590.423.4567”,“hire_date”:“03-JAN-06”,“job_id”:“IT_PROG”,“salary”:9000,“manager_id”:102,“department_id”:60}
,{“employee_id”:104,“first_name”:“Bruce”,“last_name”:“Ernst”,“email”:“BERNST”,“phone_number”:“590.423.4568”,“hire_date”:“21-MAY-07”,“job_id”:“IT_PROG”,“salary”:6000,“manager_id”:103,“department_id”:60}
,{“employee_id”:105,“first_name”:“David”,“last_name”:“Austin”,“email”:“DAUSTIN”,“phone_number”:“590.423.4569”,“hire_date”:“25-JUN-05”,“job_id”:“IT_PROG”,“salary”:4800,“manager_id”:103,“department_id”:60}
,{“employee_id”:106,“first_name”:“Valli”,“last_name”:“Pataballa”,“email”:“VPATABAL”,“phone_number”:“590.423.4560”,“hire_date”:“05-FEB-06”,“job_id”:“IT_PROG”,“salary”:4800,“manager_id”:103,“department_id”:60}
,{“employee_id”:107,“first_name”:“Diana”,“last_name”:“Lorentz”,“email”:“DLORENTZ”,“phone_number”:“590.423.5567”,“hire_date”:“07-FEB-07”,“job_id”:“IT_PROG”,“salary”:4200,“manager_id”:103,“department_id”:60}
,{“employee_id”:108,“first_name”:“Nancy”,“last_name”:“Greenberg”,“email”:“NGREENBE”,“phone_number”:“515.124.4569”,“hire_date”:“17-AUG-02”,“job_id”:“FI_MGR”,“salary”:12008,“manager_id”:101,“department_id”:100}
,{“employee_id”:109,“first_name”:“Daniel”,“last_name”:“Faviet”,“email”:“DFAVIET”,“phone_number”:“515.124.4169”,“hire_date”:“16-AUG-02”,“job_id”:“FI_ACCOUNT”,“salary”:9000,“manager_id”:108,“department_id”:100}
,{“employee_id”:110,“first_name”:“John”,“last_name”:“Chen”,“email”:“JCHEN”,“phone_number”:“515.124.4269”,“hire_date”:“28-SEP-05”,“job_id”:“FI_ACCOUNT”,“salary”:8200,“manager_id”:108,“department_id”:100}
,{“employee_id”:111,“first_name”:“Ismael”,“last_name”:“Sciarra”,“email”:“ISCIARRA”,“phone_number”:“515.124.4369”,“hire_date”:“30-SEP-05”,“job_id”:“FI_ACCOUNT”,“salary”:7700,“manager_id”:108,“department_id”:100}
,{“employee_id”:112,“first_name”:“Jose Manuel”,“last_name”:“Urman”,“email”:“JMURMAN”,“phone_number”:“515.124.4469”,“hire_date”:“07-MAR-06”,“job_id”:“FI_ACCOUNT”,“salary”:7800,“manager_id”:108,“department_id”:100}
,{“employee_id”:113,“first_name”:“Luis”,“last_name”:“Popp”,“email”:“LPOPP”,“phone_number”:“515.124.4567”,“hire_date”:“07-DEC-07”,“job_id”:“FI_ACCOUNT”,“salary”:6900,“manager_id”:108,“department_id”:100}
,{“employee_id”:114,“first_name”:“Den”,“last_name”:“Raphaely”,“email”:“DRAPHEAL”,“phone_number”:“515.127.4561”,“hire_date”:“07-DEC-02”,“job_id”:“PU_MAN”,“salary”:11000,“manager_id”:100,“department_id”:30}
,{“employee_id”:115,“first_name”:“Alexander”,“last_name”:“Khoo”,“email”:“AKHOO”,“phone_number”:“515.127.4562”,“hire_date”:“18-MAY-03”,“job_id”:“PU_CLERK”,“salary”:3100,“manager_id”:114,“department_id”:30}
,{“employee_id”:116,“first_name”:“Shelli”,“last_name”:“Baida”,“email”:“SBAIDA”,“phone_number”:“515.127.4563”,“hire_date”:“24-DEC-05”,“job_id”:“PU_CLERK”,“salary”:2900,“manager_id”:114,“department_id”:30}
,{“employee_id”:117,“first_name”:“Sigal”,“last_name”:“Tobias”,“email”:“STOBIAS”,“phone_number”:“515.127.4564”,“hire_date”:“24-JUL-05”,“job_id”:“PU_CLERK”,“salary”:2800,“manager_id”:114,“department_id”:30}
,{“employee_id”:118,“first_name”:“Guy”,“last_name”:“Himuro”,“email”:“GHIMURO”,“phone_number”:“515.127.4565”,“hire_date”:“15-NOV-06”,“job_id”:“PU_CLERK”,“salary”:2600,“manager_id”:114,“department_id”:30}
,{“employee_id”:119,“first_name”:“Karen”,“last_name”:“Colmenares”,“email”:“KCOLMENA”,“phone_number”:“515.127.4566”,“hire_date”:“10-AUG-07”,“job_id”:“PU_CLERK”,“salary”:2500,“manager_id”:114,“department_id”:30}
,{“employee_id”:120,“first_name”:“Matthew”,“last_name”:“Weiss”,“email”:“MWEISS”,“phone_number”:“650.123.1234”,“hire_date”:“18-JUL-04”,“job_id”:“ST_MAN”,“salary”:8000,“manager_id”:100,“department_id”:50}
,{“employee_id”:121,“first_name”:“Adam”,“last_name”:“Fripp”,“email”:“AFRIPP”,“phone_number”:“650.123.2234”,“hire_date”:“10-APR-05”,“job_id”:“ST_MAN”,“salary”:8200,“manager_id”:100,“department_id”:50}
,{“employee_id”:122,“first_name”:“Payam”,“last_name”:“Kaufling”,“email”:“PKAUFLIN”,“phone_number”:“650.123.3234”,“hire_date”:“01-MAY-03”,“job_id”:“ST_MAN”,“salary”:7900,“manager_id”:100,“department_id”:50}
,{“employee_id”:123,“first_name”:“Shanta”,“last_name”:“Vollman”,“email”:“SVOLLMAN”,“phone_number”:“650.123.4234”,“hire_date”:“10-OCT-05”,“job_id”:“ST_MAN”,“salary”:6500,“manager_id”:100,“department_id”:50}
,{“employee_id”:124,“first_name”:“Kevin”,“last_name”:“Mourgos”,“email”:“KMOURGOS”,“phone_number”:“650.123.5234”,“hire_date”:“16-NOV-07”,“job_id”:“ST_MAN”,“salary”:5800,“manager_id”:100,“department_id”:50}
,{“employee_id”:125,“first_name”:“Julia”,“last_name”:“Nayer”,“email”:“JNAYER”,“phone_number”:“650.124.1214”,“hire_date”:“16-JUL-05”,“job_id”:“ST_CLERK”,“salary”:3200,“manager_id”:120,“department_id”:50}
,{“employee_id”:126,“first_name”:“Irene”,“last_name”:“Mikkilineni”,“email”:“IMIKKILI”,“phone_number”:“650.124.1224”,“hire_date”:“28-SEP-06”,“job_id”:“ST_CLERK”,“salary”:2700,“manager_id”:120,“department_id”:50}
,{“employee_id”:127,“first_name”:“James”,“last_name”:“Landry”,“email”:“JLANDRY”,“phone_number”:“650.124.1334”,“hire_date”:“14-JAN-07”,“job_id”:“ST_CLERK”,“salary”:2400,“manager_id”:120,“department_id”:50}
,{“employee_id”:128,“first_name”:“Steven”,“last_name”:“Markle”,“email”:“SMARKLE”,“phone_number”:“650.124.1434”,“hire_date”:“08-MAR-08”,“job_id”:“ST_CLERK”,“salary”:2200,“manager_id”:120,“department_id”:50}
,{“employee_id”:129,“first_name”:“Laura”,“last_name”:“Bissot”,“email”:“LBISSOT”,“phone_number”:“650.124.5234”,“hire_date”:“20-AUG-05”,“job_id”:“ST_CLERK”,“salary”:3300,“manager_id”:121,“department_id”:50}
,{“employee_id”:130,“first_name”:“Mozhe”,“last_name”:“Atkinson”,“email”:“MATKINSO”,“phone_number”:“650.124.6234”,“hire_date”:“30-OCT-05”,“job_id”:“ST_CLERK”,“salary”:2800,“manager_id”:121,“department_id”:50}
,{“employee_id”:131,“first_name”:“James”,“last_name”:“Marlow”,“email”:“JAMRLOW”,“phone_number”:“650.124.7234”,“hire_date”:“16-FEB-05”,“job_id”:“ST_CLERK”,“salary”:2500,“manager_id”:121,“department_id”:50}
,{“employee_id”:132,“first_name”:“TJ”,“last_name”:“Olson”,“email”:“TJOLSON”,“phone_number”:“650.124.8234”,“hire_date”:“10-APR-07”,“job_id”:“ST_CLERK”,“salary”:2100,“manager_id”:121,“department_id”:50}
,{“employee_id”:133,“first_name”:“Jason”,“last_name”:“Mallin”,“email”:“JMALLIN”,“phone_number”:“650.127.1934”,“hire_date”:“14-JUN-04”,“job_id”:“ST_CLERK”,“salary”:3300,“manager_id”:122,“department_id”:50}
,{“employee_id”:134,“first_name”:“Michael”,“last_name”:“Rogers”,“email”:“MROGERS”,“phone_number”:“650.127.1834”,“hire_date”:“26-AUG-06”,“job_id”:“ST_CLERK”,“salary”:2900,“manager_id”:122,“department_id”:50}
,{“employee_id”:135,“first_name”:“Ki”,“last_name”:“Gee”,“email”:“KGEE”,“phone_number”:“650.127.1734”,“hire_date”:“12-DEC-07”,“job_id”:“ST_CLERK”,“salary”:2400,“manager_id”:122,“department_id”:50}
,{“employee_id”:136,“first_name”:“Hazel”,“last_name”:“Philtanker”,“email”:“HPHILTAN”,“phone_number”:“650.127.1634”,“hire_date”:“06-FEB-08”,“job_id”:“ST_CLERK”,“salary”:2200,“manager_id”:122,“department_id”:50}
,{“employee_id”:137,“first_name”:“Renske”,“last_name”:“Ladwig”,“email”:“RLADWIG”,“phone_number”:“650.121.1234”,“hire_date”:“14-JUL-03”,“job_id”:“ST_CLERK”,“salary”:3600,“manager_id”:123,“department_id”:50}
,{“employee_id”:138,“first_name”:“Stephen”,“last_name”:“Stiles”,“email”:“SSTILES”,“phone_number”:“650.121.2034”,“hire_date”:“26-OCT-05”,“job_id”:“ST_CLERK”,“salary”:3200,“manager_id”:123,“department_id”:50}
,{“employee_id”:139,“first_name”:“John”,“last_name”:“Seo”,“email”:“JSEO”,“phone_number”:“650.121.2019”,“hire_date”:“12-FEB-06”,“job_id”:“ST_CLERK”,“salary”:2700,“manager_id”:123,“department_id”:50}
,{“employee_id”:140,“first_name”:“Joshua”,“last_name”:“Patel”,“email”:“JPATEL”,“phone_number”:“650.121.1834”,“hire_date”:“06-APR-06”,“job_id”:“ST_CLERK”,“salary”:2500,“manager_id”:123,“department_id”:50}
,{“employee_id”:141,“first_name”:“Trenna”,“last_name”:“Rajs”,“email”:“TRAJS”,“phone_number”:“650.121.8009”,“hire_date”:“17-OCT-03”,“job_id”:“ST_CLERK”,“salary”:3500,“manager_id”:124,“department_id”:50}
,{“employee_id”:142,“first_name”:“Curtis”,“last_name”:“Davies”,“email”:“CDAVIES”,“phone_number”:“650.121.2994”,“hire_date”:“29-JAN-05”,“job_id”:“ST_CLERK”,“salary”:3100,“manager_id”:124,“department_id”:50}
,{“employee_id”:143,“first_name”:“Randall”,“last_name”:“Matos”,“email”:“RMATOS”,“phone_number”:“650.121.2874”,“hire_date”:“15-MAR-06”,“job_id”:“ST_CLERK”,“salary”:2600,“manager_id”:124,“department_id”:50}
,{“employee_id”:144,“first_name”:“Peter”,“last_name”:“Vargas”,“email”:“PVARGAS”,“phone_number”:“650.121.2004”,“hire_date”:“09-JUL-06”,“job_id”:“ST_CLERK”,“salary”:2500,“manager_id”:124,“department_id”:50}
,{“employee_id”:145,“first_name”:“John”,“last_name”:“Russell”,“email”:“JRUSSEL”,“phone_number”:“011.44.1344.429268”,“hire_date”:“01-OCT-04”,“job_id”:“SA_MAN”,“salary”:14000,“commission_pct”:0.4,“manager_id”:100,“department_id”:80}
,{“employee_id”:146,“first_name”:“Karen”,“last_name”:“Partners”,“email”:“KPARTNER”,“phone_number”:“011.44.1344.467268”,“hire_date”:“05-JAN-05”,“job_id”:“SA_MAN”,“salary”:13500,“commission_pct”:0.3,“manager_id”:100,“department_id”:80}
,{“employee_id”:147,“first_name”:“Alberto”,“last_name”:“Errazuriz”,“email”:“AERRAZUR”,“phone_number”:“011.44.1344.429278”,“hire_date”:“10-MAR-05”,“job_id”:“SA_MAN”,“salary”:12000,“commission_pct”:0.3,“manager_id”:100,“department_id”:80}
,{“employee_id”:148,“first_name”:“Gerald”,“last_name”:“Cambrault”,“email”:“GCAMBRAU”,“phone_number”:“011.44.1344.619268”,“hire_date”:“15-OCT-07”,“job_id”:“SA_MAN”,“salary”:11000,“commission_pct”:0.3,“manager_id”:100,“department_id”:80}
,{“employee_id”:149,“first_name”:“Eleni”,“last_name”:“Zlotkey”,“email”:“EZLOTKEY”,“phone_number”:“011.44.1344.429018”,“hire_date”:“29-JAN-08”,“job_id”:“SA_MAN”,“salary”:10500,“commission_pct”:0.2,“manager_id”:100,“department_id”:80}
,{“employee_id”:150,“first_name”:“Peter”,“last_name”:“Tucker”,“email”:“PTUCKER”,“phone_number”:“011.44.1344.129268”,“hire_date”:“30-JAN-05”,“job_id”:“SA_REP”,“salary”:10000,“commission_pct”:0.3,“manager_id”:145,“department_id”:80}
,{“employee_id”:151,“first_name”:“David”,“last_name”:“Bernstein”,“email”:“DBERNSTE”,“phone_number”:“011.44.1344.345268”,“hire_date”:“24-MAR-05”,“job_id”:“SA_REP”,“salary”:9500,“commission_pct”:0.25,“manager_id”:145,“department_id”:80}
,{“employee_id”:152,“first_name”:“Peter”,“last_name”:“Hall”,“email”:“PHALL”,“phone_number”:“011.44.1344.478968”,“hire_date”:“20-AUG-05”,“job_id”:“SA_REP”,“salary”:9000,“commission_pct”:0.25,“manager_id”:145,“department_id”:80}
,{“employee_id”:153,“first_name”:“Christopher”,“last_name”:“Olsen”,“email”:“COLSEN”,“phone_number”:“011.44.1344.498718”,“hire_date”:“30-MAR-06”,“job_id”:“SA_REP”,“salary”:8000,“commission_pct”:0.2,“manager_id”:145,“department_id”:80}
,{“employee_id”:154,“first_name”:“Nanette”,“last_name”:“Cambrault”,“email”:“NCAMBRAU”,“phone_number”:“011.44.1344.987668”,“hire_date”:“09-DEC-06”,“job_id”:“SA_REP”,“salary”:7500,“commission_pct”:0.2,“manager_id”:145,“department_id”:80}
,{“employee_id”:155,“first_name”:“Oliver”,“last_name”:“Tuvault”,“email”:“OTUVAULT”,“phone_number”:“011.44.1344.486508”,“hire_date”:“23-NOV-07”,“job_id”:“SA_REP”,“salary”:7000,“commission_pct”:0.15,“manager_id”:145,“department_id”:80}
,{“employee_id”:156,“first_name”:“Janette”,“last_name”:“King”,“email”:“JKING”,“phone_number”:“011.44.1345.429268”,“hire_date”:“30-JAN-04”,“job_id”:“SA_REP”,“salary”:10000,“commission_pct”:0.35,“manager_id”:146,“department_id”:80}
,{“employee_id”:157,“first_name”:“Patrick”,“last_name”:“Sully”,“email”:“PSULLY”,“phone_number”:“011.44.1345.929268”,“hire_date”:“04-MAR-04”,“job_id”:“SA_REP”,“salary”:9500,“commission_pct”:0.35,“manager_id”:146,“department_id”:80}
,{“employee_id”:158,“first_name”:“Allan”,“last_name”:“McEwen”,“email”:“AMCEWEN”,“phone_number”:“011.44.1345.829268”,“hire_date”:“01-AUG-04”,“job_id”:“SA_REP”,“salary”:9000,“commission_pct”:0.35,“manager_id”:146,“department_id”:80}
,{“employee_id”:159,“first_name”:“Lindsey”,“last_name”:“Smith”,“email”:“LSMITH”,“phone_number”:“011.44.1345.729268”,“hire_date”:“10-MAR-05”,“job_id”:“SA_REP”,“salary”:8000,“commission_pct”:0.3,“manager_id”:146,“department_id”:80}
,{“employee_id”:160,“first_name”:“Louise”,“last_name”:“Doran”,“email”:“LDORAN”,“phone_number”:“011.44.1345.629268”,“hire_date”:“15-DEC-05”,“job_id”:“SA_REP”,“salary”:7500,“commission_pct”:0.3,“manager_id”:146,“department_id”:80}
,{“employee_id”:161,“first_name”:“Sarath”,“last_name”:“Sewall”,“email”:“SSEWALL”,“phone_number”:“011.44.1345.529268”,“hire_date”:“03-NOV-06”,“job_id”:“SA_REP”,“salary”:7000,“commission_pct”:0.25,“manager_id”:146,“department_id”:80}
,{“employee_id”:162,“first_name”:“Clara”,“last_name”:“Vishney”,“email”:“CVISHNEY”,“phone_number”:“011.44.1346.129268”,“hire_date”:“11-NOV-05”,“job_id”:“SA_REP”,“salary”:10500,“commission_pct”:0.25,“manager_id”:147,“department_id”:80}
,{“employee_id”:163,“first_name”:“Danielle”,“last_name”:“Greene”,“email”:“DGREENE”,“phone_number”:“011.44.1346.229268”,“hire_date”:“19-MAR-07”,“job_id”:“SA_REP”,“salary”:9500,“commission_pct”:0.15,“manager_id”:147,“department_id”:80}
,{“employee_id”:164,“first_name”:“Mattea”,“last_name”:“Marvins”,“email”:“MMARVINS”,“phone_number”:“011.44.1346.329268”,“hire_date”:“24-JAN-08”,“job_id”:“SA_REP”,“salary”:7200,“commission_pct”:0.1,“manager_id”:147,“department_id”:80}
,{“employee_id”:165,“first_name”:“David”,“last_name”:“Lee”,“email”:“DLEE”,“phone_number”:“011.44.1346.529268”,“hire_date”:“23-FEB-08”,“job_id”:“SA_REP”,“salary”:6800,“commission_pct”:0.1,“manager_id”:147,“department_id”:80}
,{“employee_id”:166,“first_name”:“Sundar”,“last_name”:“Ande”,“email”:“SANDE”,“phone_number”:“011.44.1346.629268”,“hire_date”:“24-MAR-08”,“job_id”:“SA_REP”,“salary”:6400,“commission_pct”:0.1,“manager_id”:147,“department_id”:80}
,{“employee_id”:167,“first_name”:“Amit”,“last_name”:“Banda”,“email”:“ABANDA”,“phone_number”:“011.44.1346.729268”,“hire_date”:“21-APR-08”,“job_id”:“SA_REP”,“salary”:6200,“commission_pct”:0.1,“manager_id”:147,“department_id”:80}
,{“employee_id”:168,“first_name”:“Lisa”,“last_name”:“Ozer”,“email”:“LOZER”,“phone_number”:“011.44.1343.929268”,“hire_date”:“11-MAR-05”,“job_id”:“SA_REP”,“salary”:11500,“commission_pct”:0.25,“manager_id”:148,“department_id”:80}
,{“employee_id”:169,“first_name”:“Harrison”,“last_name”:“Bloom”,“email”:“HBLOOM”,“phone_number”:“011.44.1343.829268”,“hire_date”:“23-MAR-06”,“job_id”:“SA_REP”,“salary”:10000,“commission_pct”:0.2,“manager_id”:148,“department_id”:80}
,{“employee_id”:170,“first_name”:“Tayler”,“last_name”:“Fox”,“email”:“TFOX”,“phone_number”:“011.44.1343.729268”,“hire_date”:“24-JAN-06”,“job_id”:“SA_REP”,“salary”:9600,“commission_pct”:0.2,“manager_id”:148,“department_id”:80}
,{“employee_id”:171,“first_name”:“William”,“last_name”:“Smith”,“email”:“WSMITH”,“phone_number”:“011.44.1343.629268”,“hire_date”:“23-FEB-07”,“job_id”:“SA_REP”,“salary”:7400,“commission_pct”:0.15,“manager_id”:148,“department_id”:80}
,{“employee_id”:172,“first_name”:“Elizabeth”,“last_name”:“Bates”,“email”:“EBATES”,“phone_number”:“011.44.1343.529268”,“hire_date”:“24-MAR-07”,“job_id”:“SA_REP”,“salary”:7300,“commission_pct”:0.15,“manager_id”:148,“department_id”:80}
,{“employee_id”:173,“first_name”:“Sundita”,“last_name”:“Kumar”,“email”:“SKUMAR”,“phone_number”:“011.44.1343.329268”,“hire_date”:“21-APR-08”,“job_id”:“SA_REP”,“salary”:6100,“commission_pct”:0.1,“manager_id”:148,“department_id”:80}
,{“employee_id”:174,“first_name”:“Ellen”,“last_name”:“Abel”,“email”:“EABEL”,“phone_number”:“011.44.1644.429267”,“hire_date”:“11-MAY-04”,“job_id”:“SA_REP”,“salary”:11000,“commission_pct”:0.3,“manager_id”:149,“department_id”:80}
,{“employee_id”:175,“first_name”:“Alyssa”,“last_name”:“Hutton”,“email”:“AHUTTON”,“phone_number”:“011.44.1644.429266”,“hire_date”:“19-MAR-05”,“job_id”:“SA_REP”,“salary”:8800,“commission_pct”:0.25,“manager_id”:149,“department_id”:80}
,{“employee_id”:176,“first_name”:“Jonathon”,“last_name”:“Taylor”,“email”:“JTAYLOR”,“phone_number”:“011.44.1644.429265”,“hire_date”:“24-MAR-06”,“job_id”:“SA_REP”,“salary”:8600,“commission_pct”:0.2,“manager_id”:149,“department_id”:80}
,{“employee_id”:177,“first_name”:“Jack”,“last_name”:“Livingston”,“email”:“JLIVINGS”,“phone_number”:“011.44.1644.429264”,“hire_date”:“23-APR-06”,“job_id”:“SA_REP”,“salary”:8400,“commission_pct”:0.2,“manager_id”:149,“department_id”:80}
,{“employee_id”:178,“first_name”:“Kimberely”,“last_name”:“Grant”,“email”:“KGRANT”,“phone_number”:“011.44.1644.429263”,“hire_date”:“24-MAY-07”,“job_id”:“SA_REP”,“salary”:7000,“commission_pct”:0.15,“manager_id”:149}
,{“employee_id”:179,“first_name”:“Charles”,“last_name”:“Johnson”,“email”:“CJOHNSON”,“phone_number”:“011.44.1644.429262”,“hire_date”:“04-JAN-08”,“job_id”:“SA_REP”,“salary”:6200,“commission_pct”:0.1,“manager_id”:149,“department_id”:80}
,{“employee_id”:180,“first_name”:“Winston”,“last_name”:“Taylor”,“email”:“WTAYLOR”,“phone_number”:“650.507.9876”,“hire_date”:“24-JAN-06”,“job_id”:“SH_CLERK”,“salary”:3200,“manager_id”:120,“department_id”:50}
,{“employee_id”:181,“first_name”:“Jean”,“last_name”:“Fleaur”,“email”:“JFLEAUR”,“phone_number”:“650.507.9877”,“hire_date”:“23-FEB-06”,“job_id”:“SH_CLERK”,“salary”:3100,“manager_id”:120,“department_id”:50}
,{“employee_id”:182,“first_name”:“Martha”,“last_name”:“Sullivan”,“email”:“MSULLIVA”,“phone_number”:“650.507.9878”,“hire_date”:“21-JUN-07”,“job_id”:“SH_CLERK”,“salary”:2500,“manager_id”:120,“department_id”:50}
,{“employee_id”:183,“first_name”:“Girard”,“last_name”:“Geoni”,“email”:“GGEONI”,“phone_number”:“650.507.9879”,“hire_date”:“03-FEB-08”,“job_id”:“SH_CLERK”,“salary”:2800,“manager_id”:120,“department_id”:50}
,{“employee_id”:184,“first_name”:“Nandita”,“last_name”:“Sarchand”,“email”:“NSARCHAN”,“phone_number”:“650.509.1876”,“hire_date”:“27-JAN-04”,“job_id”:“SH_CLERK”,“salary”:4200,“manager_id”:121,“department_id”:50}
,{“employee_id”:185,“first_name”:“Alexis”,“last_name”:“Bull”,“email”:“ABULL”,“phone_number”:“650.509.2876”,“hire_date”:“20-FEB-05”,“job_id”:“SH_CLERK”,“salary”:4100,“manager_id”:121,“department_id”:50}
,{“employee_id”:186,“first_name”:“Julia”,“last_name”:“Dellinger”,“email”:“JDELLING”,“phone_number”:“650.509.3876”,“hire_date”:“24-JUN-06”,“job_id”:“SH_CLERK”,“salary”:3400,“manager_id”:121,“department_id”:50}
,{“employee_id”:187,“first_name”:“Anthony”,“last_name”:“Cabrio”,“email”:“ACABRIO”,“phone_number”:“650.509.4876”,“hire_date”:“07-FEB-07”,“job_id”:“SH_CLERK”,“salary”:3000,“manager_id”:121,“department_id”:50}
,{“employee_id”:188,“first_name”:“Kelly”,“last_name”:“Chung”,“email”:“KCHUNG”,“phone_number”:“650.505.1876”,“hire_date”:“14-JUN-05”,“job_id”:“SH_CLERK”,“salary”:3800,“manager_id”:122,“department_id”:50}
,{“employee_id”:189,“first_name”:“Jennifer”,“last_name”:“Dilly”,“email”:“JDILLY”,“phone_number”:“650.505.2876”,“hire_date”:“13-AUG-05”,“job_id”:“SH_CLERK”,“salary”:3600,“manager_id”:122,“department_id”:50}
,{“employee_id”:190,“first_name”:“Timothy”,“last_name”:“Gates”,“email”:“TGATES”,“phone_number”:“650.505.3876”,“hire_date”:“11-JUL-06”,“job_id”:“SH_CLERK”,“salary”:2900,“manager_id”:122,“department_id”:50}
,{“employee_id”:191,“first_name”:“Randall”,“last_name”:“Perkins”,“email”:“RPERKINS”,“phone_number”:“650.505.4876”,“hire_date”:“19-DEC-07”,“job_id”:“SH_CLERK”,“salary”:2500,“manager_id”:122,“department_id”:50}
,{“employee_id”:192,“first_name”:“Sarah”,“last_name”:“Bell”,“email”:“SBELL”,“phone_number”:“650.501.1876”,“hire_date”:“04-FEB-04”,“job_id”:“SH_CLERK”,“salary”:4000,“manager_id”:123,“department_id”:50}
,{“employee_id”:193,“first_name”:“Britney”,“last_name”:“Everett”,“email”:“BEVERETT”,“phone_number”:“650.501.2876”,“hire_date”:“03-MAR-05”,“job_id”:“SH_CLERK”,“salary”:3900,“manager_id”:123,“department_id”:50}
,{“employee_id”:194,“first_name”:“Samuel”,“last_name”:“McCain”,“email”:“SMCCAIN”,“phone_number”:“650.501.3876”,“hire_date”:“01-JUL-06”,“job_id”:“SH_CLERK”,“salary”:3200,“manager_id”:123,“department_id”:50}
,{“employee_id”:195,“first_name”:“Vance”,“last_name”:“Jones”,“email”:“VJONES”,“phone_number”:“650.501.4876”,“hire_date”:“17-MAR-07”,“job_id”:“SH_CLERK”,“salary”:2800,“manager_id”:123,“department_id”:50}
,{“employee_id”:196,“first_name”:“Alana”,“last_name”:“Walsh”,“email”:“AWALSH”,“phone_number”:“650.507.9811”,“hire_date”:“24-APR-06”,“job_id”:“SH_CLERK”,“salary”:3100,“manager_id”:124,“department_id”:50}
,{“employee_id”:197,“first_name”:“Kevin”,“last_name”:“Feeney”,“email”:“KFEENEY”,“phone_number”:“650.507.9822”,“hire_date”:“23-MAY-06”,“job_id”:“SH_CLERK”,“salary”:3000,“manager_id”:124,“department_id”:50}
,{“employee_id”:198,“first_name”:“Donald”,“last_name”:“OConnell”,“email”:“DOCONNEL”,“phone_number”:“650.507.9833”,“hire_date”:“21-JUN-07”,“job_id”:“SH_CLERK”,“salary”:2600,“manager_id”:124,“department_id”:50}
,{“employee_id”:199,“first_name”:“Douglas”,“last_name”:“Grant”,“email”:“DGRANT”,“phone_number”:“650.507.9844”,“hire_date”:“13-JAN-08”,“job_id”:“SH_CLERK”,“salary”:2600,“manager_id”:124,“department_id”:50}
,{“employee_id”:200,“first_name”:“Jennifer”,“last_name”:“Whalen”,“email”:“JWHALEN”,“phone_number”:“515.123.4444”,“hire_date”:“17-SEP-03”,“job_id”:“AD_ASST”,“salary”:4400,“manager_id”:101,“department_id”:10}
,{“employee_id”:201,“first_name”:“Michael”,“last_name”:“Hartstein”,“email”:“MHARTSTE”,“phone_number”:“515.123.5555”,“hire_date”:“17-FEB-04”,“job_id”:“MK_MAN”,“salary”:13000,“manager_id”:100,“department_id”:20}
,{“employee_id”:202,“first_name”:“Pat”,“last_name”:“Fay”,“email”:“PFAY”,“phone_number”:“603.123.6666”,“hire_date”:“17-AUG-05”,“job_id”:“MK_REP”,“salary”:6000,“manager_id”:201,“department_id”:20}
,{“employee_id”:203,“first_name”:“Susan”,“last_name”:“Mavris”,“email”:“SMAVRIS”,“phone_number”:“515.123.7777”,“hire_date”:“07-JUN-02”,“job_id”:“HR_REP”,“salary”:6500,“manager_id”:101,“department_id”:40}
,{“employee_id”:204,“first_name”:“Hermann”,“last_name”:“Baer”,“email”:“HBAER”,“phone_number”:“515.123.8888”,“hire_date”:“07-JUN-02”,“job_id”:“PR_REP”,“salary”:10000,“manager_id”:101,“department_id”:70}
,{“employee_id”:205,“first_name”:“Shelley”,“last_name”:“Higgins”,“email”:“SHIGGINS”,“phone_number”:“515.123.8080”,“hire_date”:“07-JUN-02”,“job_id”:“AC_MGR”,“salary”:12008,“manager_id”:101,“department_id”:110}
,{“employee_id”:206,“first_name”:“William”,“last_name”:“Gietz”,“email”:“WGIETZ”,“phone_number”:“515.123.8181”,“hire_date”:“07-JUN-02”,“job_id”:“AC_ACCOUNT”,“salary”:8300,“manager_id”:205,“department_id”:110}
]);
ORACLE SQL :
SELECT DEPARTMENT_ID,
HIRE_DATE,
eomassignedsum,
CASE
WHEN total > 12
THEN sum(decode (previousSum,null,decode(total,1,eomassignedsum,0), eomassignedsum-previousSum))
OVER ( partition by DEPARTMENT_ID ) / decode(total,1,1,total-1)
WHEN total > 0 AND total <= 12
THEN sum(decode (previousSum,null,decode(total,1,eomassignedsum,0), eomassignedsum-previousSum))
OVER ( partition by DEPARTMENT_ID ) / decode(total,1,1,total)
END AS avg_eom,
r1
from (
SELECT
DEPARTMENT_ID,
HIRE_DATE,
SALARY,
sum(salary) over () as eomassignedsum,
COUNT() OVER ( partition by DEPARTMENT_ID ) total,
CASE
WHEN COUNT() OVER ( partition by DEPARTMENT_ID ) > 5
THEN LAG(SALARY,1,null) over (partition by DEPARTMENT_ID order by HIRE_DATE )
ELSE
LAG(SALARY,1,0) over (partition by DEPARTMENT_ID order by HIRE_DATE )
END AS previousSum,
row_number()over (partition by DEPARTMENT_ID order by HIRE_DATE ) AS r1
FROM employees
);