streaks_v1.sql

CREATE TABLE "public"."streaks" (
	"user_id" int4 NOT NULL,
	"current_streak" int4 NOT NULL DEFAULT 0,
	"longest_streak" int4 NOT NULL DEFAULT 0,
	"last_checkin_dt" date NOT NULL,
	CONSTRAINT "streaks_pkey" PRIMARY KEY ("user_id") NOT DEFERRABLE INITIALLY IMMEDIATE
)

-- update query
UPDATE streaks
SET current_streak = (
    CASE
        WHEN %(date)s - last_checkin_dt = 1 THEN current_streak + 1
        ELSE 1
    END
),
longest_streak = GREATEST(longest_streak, (
    CASE
        WHEN %(date)s - last_checkin_dt = 1 THEN current_streak + 1
        ELSE 1
    END
)),
last_checkin_dt = %(date)s
WHERE user_id = %(user_id)s AND %(date)s > last_checkin_dt
RETURNING *