Returning vs New Users — Solution & ExplanationProblems / Returning vs New Users / EditorialApproach

Dedupe to user-days. SELECT DISTINCT user_id, DATE_TRUNC('day', event_time). All later logic assumes one row per user per active day.

Attach the first day with a window. MIN(day) OVER (PARTITION BY user_id) labels every user-day row with that user's debut. A GROUP BY could compute the same number, but then you'd need a join to get it back onto the rows — the window function does both at once.

Classify and count. day = first_day → new, day > first_day → returning; conditional counts per day. Note the two categories are exhaustive and disjoint, so new + returning = DAU — a nice self-check.

Key Insight"New vs returning" is a comparison between a row's value and a per-partition aggregate — the exact shape window functions exist for. The equivalent join formulation (daily activity ⋈ first-day-per-user) is longer and easy to get subtly wrong (joining back at the event grain re-introduces duplicates). When a classification needs *the row* and *a fact about the row's group* side by side, reach for agg OVER (PARTITION BY group) before reaching for a self-join.Hints

Work at the (user, day) grain, not the event grain — SELECT DISTINCT the truncated day first, or users with many events inflate both counts.

Each row needs to know that user's first-ever day. A window MIN — MIN(day) OVER (PARTITION BY user_id) — attaches it without collapsing the rows, unlike GROUP BY.

Once every row carries (day, first_day), the classification is pure comparison: equal means new, greater means returning. Conditional counts finish it.

A user's first day makes them 'new' even if they were also active later the same day — day-level dedup already handled that.

Solve Returning vs New Users yourself →