Island Puzzle — a solution and another better one



 CREATE TABLE SalesByMonth
(
	SalesMonth date PRIMARY KEY,
	Sales int
)

INSERT INTO SalesByMonth VALUES
('20140101', 4000),
('20140201', 4000),
('20140301', 4000),

 --('20140401', 4000),

('20140501', 4000),
('20140601', 100),
('20140701', 100),
('20140801', 4000),
('20140901', 4000),
('20141001', 4000),
('20141101', 4000),
('20141201', 4000),
('20150101', 4000),
('20150201', 4000),
('20150301', 4000)

 
 

--Mine
 ;with mycte as (


 Select dateadd(month,n, dt) dt from ( select Cast('20140101' as date) dt ) src
 cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n)
 )

,mycte1 as (
select  *, SUM( case when Sales>=4000 Then 1 else 0 End ) Over(Order by dt ROWS BETWEEN 3 preceding AND CURRENT ROW) slidingTotal4
from mycte m left join SalesByMonth s on m.dt=s.SalesMonth 
)
 
 , mycte2 AS
(
	SELECT 
		SalesMonth,	ROW_NUMBER() OVER (PARTITION BY slidingTotal4 ORDER BY SalesMonth) AS rn
	FROM mycte1
	WHERE  slidingTotal4=4
)	
 

SELECT SalesMonth AS GiftSalesMonth, rn
FROM mycte2
WHERE    rn % 4 = 1


---Another better one from Jesus Lopez

;WITH B AS
(
	SELECT 
		SalesMonth, Sales,
		DATEADD(MONTH, -DENSE_RANK() OVER (ORDER BY SalesMonth), SalesMonth) AS IslandId
	FROM 
		SalesByMonth
	WHERE 
		Sales >= 4000
)
, C AS
(
	SELECT 
		B.SalesMonth, 
		ROW_NUMBER() OVER (PARTITION BY IslandId ORDER BY SalesMonth) AS RowNumberInIsland
	FROM B
)	
SELECT
	C.SalesMonth AS GiftSalesMonth
FROM 
	C
WHERE
	RowNumberInIsland % 4 = 0

drop table SalesByMonth


--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4c8aba6-7e23-41fd-96c1-c61a4d60fcfa/interesting-puzzle?forum=transactsql#61bb76e5-8895-4f55-909e-4920a8e9c02c
 



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s