SQLパズル
https://www.shoeisha.co.jp/book/download/

ISBN 978-4-7981-1413-2

■パズル1
DROP TABLE FiscalYearTablel;
CREATE TABLE FiscalYearTablel
(
FiscalYear NUMBER NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY(FiscalYear),
CONSTRAINT cons_chk_1 CHECK(start_date 1
GROUP BY t1.emp_id–,absent_date
;

WHERE t1.emp_id = t2.emp_id

SELECT t1.emp_id, SUM(t1.severity_points)
FROM Absenteeism t1
WHERE EXISTS
(SELECT *
FROM (SELECT emp_id,
(LEAD(absent_date) OVER (ORDER BY absent_date)-absent_date) AS DIFF
FROM Absenteeism
) t2
WHERE t1.emp_id = t2.emp_id
AND t2.diff <> 1
)
GROUP BY t1.emp_id
;

SELECT t1.emp_id, SUM(t1.severity_points)
FROM Absenteeism t1
WHERE EXISTS
(SELECT emp_id,
(LEAD(absent_date) OVER (ORDER BY absent_date)-absent_date) AS DIFF
FROM Absenteeism t2
WHERE t1.emp_id = t2.emp_id
)
GROUP BY t1.emp_id
;

——————————————————————————————————
○
SELECT t1.emp_id, SUM(t1.severity_points)
FROM Absenteeism t1
WHERE NOT EXISTS
(SELECT *
FROM (
SELECT emp_id,
( LEAD(t1.absent_date) OVER (ORDER BY t2.absent_date) – absent_date ) AS DIFF
FROM Absenteeism t2
) t3
WHERE t3.diff = 1
AND t1.emp_id = t3.emp_id
)
GROUP BY t1.emp_id
;

——————————————————————————————————

SELECT * FROM Absenteeism;

SELECT emp_id,
(LEAD(absent_date) OVER (ORDER BY absent_date)-absent_date) AS DIFF
FROM Absenteeism
;