Consecutive publisher publish more than 2 books for 3 days, Sql Server

Consecutive publisher publish more than 2 books for 3 days

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'
CREATE TABLE Table1
    ([p_id] Char, [book_id] int, [p_date] datetime)
;
    
INSERT INTO Table1
    ([p_id], [book_id], [p_date])
VALUES
    ('A', 1, '2019-11-01'),
    ('A', 2, '2019-11-01'),
    ('A', 3, '2019-11-01'),
    ('A', 4, '2019-11-03'),
    ('A', 5, '2019-11-03'),
    ('A', 6, '2019-11-05'),
    ('B', 7, '2019-11-01'),
    ('B', 8, '2019-11-01'),
    ('B', 9, '2019-11-02'),
    ('B', 10, '2019-11-02'),
    ('B', 11, '2019-11-03'),
    ('B', 12, '2019-11-03'),
    ('B', 13, '2019-11-05'),
    ('B', 14, '2019-11-05'),
    ('C', 15, '2019-11-04'),
    ('C', 16, '2018-11-01'),
    ('C', 17, '2019-11-05'),
    ('C', 18, '2019-11-06')
;

WITH cte AS(
SELECT *, LAG(num, 1) OVER(PARTITION BY p_id ORDER BY p_date) AS pre,
LAG(num, 2) OVER(PARTITION BY p_id ORDER BY p_date) pre2,
LEAD(num, 1) OVER(PARTITION BY p_id ORDER BY p_date) aft,
LEAD(num, 2) OVER(PARTITION BY p_id ORDER BY p_date) aft2,
DATEDIFF(day, LAG(p_date, 1) OVER(PARTITION BY p_id ORDER BY p_date), p_date) d_before,
DATEDIFF(day, LAG(p_date, 2) OVER(PARTITION BY p_id ORDER BY p_date), p_date) d_before2,
DATEDIFF(day, p_date, LEAD(p_date, 1) OVER(PARTITION BY p_id ORDER BY p_date)) d_after,
DATEDIFF(day, p_date, LEAD(p_date, 2) OVER(PARTITION BY p_id ORDER BY p_date)) d_after2
FROM(
SELECT p_id, COUNT(book_id) AS num, p_date
FROM table1
GROUP BY p_id, p_date)p2)

SELECT p_id, num, p_date
FROM cte
WHERE 
  (d_before=1 AND d_before2=2 AND pre>=2 AND pre2>=2)
OR(d_before=1 AND d_after=1 AND pre>=2 AND aft>=2)
OR(d_after=1 AND d_after=2 AND aft>=2 AND aft2>=2)


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development