Latest Number of Consecutive Weeks

I'm not sure this version will work for you either, It still relies on the ROW_NUMBER window function. I don't have an Azure account to test with to know for sure...

Anyway... Give this a whirl and see if it'll execute...

IF OBJECT_ID('tempdb..#Habbit') IS NOT NULL DROP TABLE #Habbit
CREATE TABLE #Habbit (
        ID INT PRIMARY KEY,
        Name VARCHAR(255),
        [Target] INT 
        )
INSERT #Habbit (ID,Name,Target) VALUES
(1,'Three Day Thing', 3),
(2,'Four Day Thing', 4),
(3,'Five Day Thing', 5)
--=============================================

IF OBJECT_ID('tempdb..#HabitEntry') IS NOT NULL DROP TABLE #HabitEntry
CREATE TABLE #HabitEntry (
        UserID INT,
        HabbitID INT,
        EntryDate DATETIME
        )
INSERT #HabitEntry (UserID,HabbitID,EntryDate) VALUES 
(1, 1, '20150101 06:35:30'),
(1, 1, '20150102 08:35:30'),
(1, 1, '20150103 08:35:30'),
(1, 1, '20150110 09:35:30'),
(1, 1, '20150112 10:35:30'),
(1, 1, '20150115 22:35:30'),
(1, 1, '20150116 23:35:30'),
(1, 1, '20150117 00:35:30'),
(1, 1, '20150118 01:35:30'),
(1, 1, '20150122 04:35:30'),
(1, 1, '20150124 05:35:30'),
(1, 1, '20150126 15:35:30'),
(1, 1, '20150127 10:35:30'),
(2, 3, '20150101 06:35:30'),
(2, 3, '20150102 06:35:30'),
(2, 3, '20150103 02:35:30'),
(2, 3, '20150104 06:35:30'),
(2, 3, '20150105 06:35:30'),
(2, 3, '20150108 06:35:30'),
(2, 3, '20150110 06:35:30'),
(2, 3, '20150111 06:35:30'),
(2, 3, '20150112 06:35:30'),
(2, 3, '20150113 06:35:30'),
(2, 3, '20150114 06:35:30'),
(2, 3, '20150122 06:35:30'),
(2, 3, '20150123 06:35:30')
--=============================================

DECLARE @MinDate DATE, @RangeSize INT 
SELECT @MinDate = DATEADD(dd, -1, MIN(he.EntryDate)) FROM #HabitEntry he
SELECT @RangeSize = DATEDIFF(dd, @MinDate, MAX(he.EntryDate) + 1) FROM #HabitEntry he

IF OBJECT_ID('tempdb..#CalRanges') IS NOT NULL DROP TABLE #CalRanges
SELECT TOP (@RangeSize)
        DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @MinDate) AS [Date],
        DATEADD(hh, 3, DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CAST(@MinDate AS DATETIME))) AS BegRange,
        DATEADD(hh, 27, DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CAST(@MinDate AS DATETIME))) AS EndRange
        INTO #CalRanges
FROM sys.all_objects ao

--=============================================

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
        CREATE TABLE #temp (
                UserID INT,
                HabbitID INT,
                EntryDate DATETIME,
                AssignedDate DATE,
                RangeGroup INT,
                PRIMARY KEY (UserID, HabbitID, EntryDate)
                )

--=============================================

;WITH rn AS (
        SELECT
                he.*,
                cr.Date,
                ROW_NUMBER() OVER (PARTITION BY he.UserID, he.HabbitID ORDER BY he.EntryDate) AS rn
        FROM
                #HabitEntry AS he
                JOIN #CalRanges AS cr
                        ON he.EntryDate BETWEEN cr.BegRange AND cr.EndRange
)
        INSERT #temp (UserID,HabbitID,EntryDate,AssignedDate,RangeGroup)
        SELECT
                rn1.UserID,
                rn1.HabbitID,
                rn1.EntryDate,
                rn1.Date AS AssignedDate,
                CASE WHEN DATEADD(dd, -1, rn1.Date) <> COALESCE(rn2.Date, '19000101') THEN ROW_NUMBER() OVER (ORDER BY rn1.EntryDate) END AS RangeGroup
        FROM
                rn rn1
                LEFT JOIN rn rn2
                        ON rn1.UserID = rn2.UserID
                        AND rn1.HabbitID = rn2.HabbitID
                        AND rn1.rn = rn2.rn +1
        WHERE 
                rn1.Date <> COALESCE(rn2.Date, '19000101')
        ORDER BY 
                rn1.UserID,
                rn1.HabbitID,
                rn1.EntryDate

        DECLARE @rg INT

        UPDATE t SET 
        @rg = t.RangeGroup = COALESCE(t.RangeGroup, @rg)
        FROM
                #temp AS t

        
        SELECT 
                x.UserID,
                x.HabbitID,
                x.HabbitName,
                x.StartDate,
                x.EndDate,
                x.ConsecutiveDayCount
        FROM (
                        SELECT DISTINCT
                                t.UserID, 
                                t.HabbitID
                        FROM #temp AS t
                        ) duh
                CROSS APPLY (
                        SELECT TOP 1
                                t.UserID,
                                t.HabbitID,
                                h.Name AS HabbitName,
                                MIN(t.AssignedDate) AS StartDate,
                                MAX(t.AssignedDate) AS EndDate,
                                COUNT(*) AS ConsecutiveDayCount
                        FROM
                                #temp AS t
                                JOIN #Habbit AS h
                                        ON t.HabbitID = h.ID
                        WHERE 
                                duh.UserID = t.UserID
                                AND duh.HabbitID = t.HabbitID
                        GROUP BY 
                                t.UserID,
                                t.HabbitID,
                                h.Name,
                                t.RangeGroup,
                                h.Target
                        HAVING 
                                COUNT(*) >= h.Target
                        ORDER BY 
                                StartDate DESC
                        ) x

---

Jason Long