Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
BC3TOSQL
t1
sql jueves
Mywall
Primeiro
SQL Interview Questions : Customer Orders
tesing
28-02 3
Madelin
20181CSE0068