Compute DAU and rolling MAU with zero days

You have two tables in PostgreSQL:

Tables

users

• user_id (STRING / INT, PK)
• signup_date (DATE)

logins

• user_id (STRING / INT, FK → users.user_id)
• browser (TEXT)
• login_ts (TIMESTAMP) — assume UTC unless stated otherwise

Tasks

1. Daily Active Users (DAU): For a given date range (e.g., 2023-01-01 to 2023-12-31), compute DAU where DAU on a day = number of distinct user_id that logged in on that calendar day.

• Output columns: date, dau
• Requirement: Include all dates in the range, even if there were zero logins that day (show dau = 0).
• Follow-up: If you need a date dimension/calendar, explain how you would create it in PostgreSQL (e.g., via generate_series).

1. Rolling MAU by date: Compute a rolling-window MAU for each calendar date in the range.

• Define MAU as distinct users who logged in in the last 30 days including the current date (an L30D lookback).
• Output columns: date, mau_l30d
• Follow-up discussion: If the product is primarily used on weekdays (little/no weekend usage), what are potential issues with an L30D definition, and what alternative window definitions could be more appropriate?