Interview Question Bank · Gaps and islands on weeks
4. Longest weekly posting streak
HardSocial mediaQuestion #132
Concept: Gaps and islands on weeksshow
Gaps and islands works on any regular step, not just days. Bucket each event into its week with date_trunc('week', ts) (Postgres weeks start on Monday), dedupe to one row per creator and week, then subtract 7 days times ROW_NUMBER: every week in an unbroken run lands on the same anchor date. Avoid week numbers like EXTRACT(WEEK), which restart every year, and the last days of December can already be week 1, so a streak over New Year breaks.
Example (different data):
WITH m AS (
SELECT DISTINCT store_id, date_trunc('month', sold_at)::date AS month
FROM sales
)
SELECT store_id, month,
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY month) AS rn
FROM m;Your task
Murmurly's creator program rewards consistency: a creator's streak is the number of consecutive calendar weeks in which they posted at least once.
Weeks start on Monday, as in date_trunc('week', posted_at). Return creator_id and longest_streak_weeks (the most consecutive weeks with at least one post) for every creator in mkt_creator_posts. Sort by longest_streak_weeks highest first, then creator_id.
Starting Postgres in your browser…
Checking your access…