Design KPI tables, retention, rollups, timezone

You are building a fast KPI dashboard for **Nextagram**. The raw tables are: - **users**(user_id PK, username, created_at TIMESTAMP) - **photos**(photo_id PK, user_id, created_at TIMESTAMP) - **follows**(follower_user_id, followed_user_id, created_at TIMESTAMP) Assume raw timestamps are stored in **PST** initially. The dashboard is reported **daily** (one row per report date). Define the daily KPIs as: - **new_users**: users created on that report date - **new_users_change_vs_7d_ago**: new_users(today) − new_users(today − 7 days) - **DAU**: distinct users who performed at least one “active” action that day (define “active” as: posted a photo OR followed someone) - **DAU_change_vs_7d_ago** - **new_photos**: photos created on that report date - **new_photos_per_DAU**: new_photos / DAU - **new_photos_change_vs_7d_ago** - **followers**: total number of follow relationships as-of end of that report date (i.e., cumulative count of rows in follows with created_at <= end_of_day) - **%_users_with_0_followers**: percent of users whose follower count (inbound follows) is 0 as-of end of that date - **%_users_with_1_2_followers** - **%_users_with_3plus_followers** Tasks: **A) Data model / table design** - Design the derived table(s) that power this dashboard for a huge dataset (fast reads). - Specify table schemas (columns, grain, and keys), and what is precomputed. - List key considerations (partitioning/clustering, incremental loads, idempotency, late-arriving data, correctness vs cost, etc.). **B) Daily population strategy** - Describe how you would populate/refresh these tables every day from the raw tables (high-level ETL steps; SQL/pseudocode is acceptable). **C) Add 1-week retention** - Add a **1-week retention** metric to the dashboard: for each cohort day D (users who joined on D), what fraction are active on D+7 (using the same “active” definition as DAU). - Describe required table changes and how you would compute it efficiently. **D) Switch to weekly rollups** - The dashboard should support **weekly** rollups instead of daily. Describe what changes in the table design and computation (date grain, definitions of “change vs 7d ago”, handling partial weeks, etc.). **E) Report in a different timezone** - A New York office wants metrics reported in **EST** instead of PST. Describe what must change (timestamp storage, date boundaries, ETL windows, backfills, and how to avoid double-counting at day boundaries).