# 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
)

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
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

```