This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.

DECLARE @Interval INT = 7

;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
    SELECT  ProductID,
            TransactionDate,
            ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
    FROM    Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
    SELECT      s.ProductID,
                s.TransactionDate AS EndDate,
                ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
    FROM        cteSingle AS s
    LEFT JOIN   cteSingle AS t ON t.ProductID = s.ProductID
                AND t.recID = s.recID - 1
    WHERE       DATEADD(DAY, @Interval, t.TransactionDate) < s.TransactionDate
                OR t.ProductID IS NULL
), cteUpper(ProductID, EndDate, recID)
AS (
    SELECT      s.ProductID,
                DATEADD(DAY, @Interval, s.TransactionDate) AS EndDate,
                ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
    FROM        cteSingle AS s
    LEFT JOIN   cteSingle AS t ON t.ProductID = s.ProductID
                AND t.recID = s.recID + 1
    WHERE       DATEADD(DAY, @Interval, s.TransactionDate) < t.TransactionDate
                OR t.ProductID IS NULL
)
SELECT      l.ProductID,
            l.StartDate,
            u.EndDate
FROM        cteLower AS l
INNER JOIN  cteUpper AS u ON u.ProductID = l.ProductID
WHERE       l.recID = u.recID
ORDER BY    l.ProductID,
            l.recID


Here are the measurements from SQL Profiler.

Peso
RowCount - 4020
CPU - 1560
Duration - 1856
Reads - 3367

Original
RowCount - 4020
CPU - 35303
Duration - 24216
Reads - 1976780