##### Question You are designing the analytical data model behind a **fast KPI dashboard** for a large-scale photo-sharing app. The dashboard is reported **daily** (one row per report date) and must stay fast even as the raw dataset grows very large. Raw source tables (all timestamps stored in **UTC**): - **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) — a row means a follow relationship was created at `created_at` and remains active afterward - **user_events**(event_id PK, user_id, event_type, occurred_at TIMESTAMP) Reporting is initially in `America/Los_Angeles` (Pacific). Define the required daily KPIs (one value per report date, in the reporting timezone): - **new_users**: users who signed up on the report date - **new_users_change_vs_7d_ago**: new_users(today) − new_users(today − 7 days) - **DAU**: distinct users who were "active" that day. Treat a user as active if they have any event in `user_events` that day (equivalently, posted a photo OR followed someone — align the exact definition with the interviewer). - **DAU_change_vs_7d_ago** - **new_photos**: photos created on the report date - **new_photos_per_DAU**: new_photos / DAU - **new_photos_change_vs_7d_ago** - **total_followers**: cumulative count of follow relationships as of end of the report date - **% users with 0 followers** (inbound follows, as of end of date) - **% users with 1–2 followers** - **% users with 3+ followers** Tasks: 1. **Data model / table design.** Design the derived table(s) that power this dashboard for a huge dataset (fast reads). Specify schemas (columns, grain, keys), what is precomputed vs derived, and key considerations (partitioning/clustering, incremental loads, idempotency, late-arriving data, correctness vs cost). 2. **Daily population strategy.** Describe how you would populate/refresh these tables every day from the raw tables (high-level ETL steps; SQL/pseudocode acceptable). 3. **Add week-1 retention.** For each signup cohort date D, what fraction of that cohort is active one week later (use the same "active" definition as DAU). Describe the required table changes and how to compute it efficiently. 4. **Switch to weekly rollups.** The dashboard should support **weekly** rollups instead of daily. Describe what changes in the table design and computation (date grain, how "change vs 7d ago" maps to weekly, additive vs non-additive metrics, partial weeks). 5. **Change the reporting timezone.** A New York office wants metrics reported in `America/New_York` (Eastern) instead of Pacific. Describe what must change (timestamp storage, date boundaries, ETL windows, backfills, multi-timezone support, and how to avoid double-counting at day boundaries). Focus on correctness, performance, and maintainability.

This Nextdoor data engineer system design screen asks you to design the analytical data model and incremental ETL behind a fast, large-scale KPI dashboard for a photo-sharing app. It covers derived aggregate table design (grain, keys, partitioning), DAU/new-user/new-photo and follower-distribution metrics, 7-day deltas, then extends to week-1 cohort retention, weekly rollups, and switching the reporting timezone. The focus is correctness, performance at scale, and maintainability with timezone handled as a reporting parameter.

How do I approach System Design interview questions?

System Design questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master system design interviews.

What difficulty level is this interview question?

This is a hard difficulty System Design question, commonly asked during Technical Screen rounds at Nextdoor.

What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Nextdoor during technical interviews.

Design KPI dashboard tables: retention, weekly rollups, timezone

Question

You are designing the analytical data model behind a fast KPI dashboard for a large-scale photo-sharing app. The dashboard is reported daily (one row per report date) and must stay fast even as the raw dataset grows very large.

Raw source tables (all timestamps stored in UTC):

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) — a row means a follow relationship was created at created_at and remains active afterward
user_events (event_id PK, user_id, event_type, occurred_at TIMESTAMP)

Reporting is initially in America/Los_Angeles (Pacific).

Define the required daily KPIs (one value per report date, in the reporting timezone):

new_users : users who signed up on the report date
new_users_change_vs_7d_ago : new_users(today) − new_users(today − 7 days)
DAU : distinct users who were "active" that day. Treat a user as active if they have any event in user_events that day (equivalently, posted a photo OR followed someone — align the exact definition with the interviewer).
DAU_change_vs_7d_ago
new_photos : photos created on the report date
new_photos_per_DAU : new_photos / DAU
new_photos_change_vs_7d_ago
total_followers : cumulative count of follow relationships as of end of the report date
% users with 0 followers (inbound follows, as of end of date)
% users with 1–2 followers
% users with 3+ followers

Tasks:

Data model / table design. Design the derived table(s) that power this dashboard for a huge dataset (fast reads). Specify schemas (columns, grain, keys), what is precomputed vs derived, and key considerations (partitioning/clustering, incremental loads, idempotency, late-arriving data, correctness vs cost).
Daily population strategy. Describe how you would populate/refresh these tables every day from the raw tables (high-level ETL steps; SQL/pseudocode acceptable).
Add week-1 retention. For each signup cohort date D, what fraction of that cohort is active one week later (use the same "active" definition as DAU). Describe the required table changes and how to compute it efficiently.
Switch to weekly rollups. The dashboard should support weekly rollups instead of daily. Describe what changes in the table design and computation (date grain, how "change vs 7d ago" maps to weekly, additive vs non-additive metrics, partial weeks).
Change the reporting timezone. A New York office wants metrics reported in America/New_York (Eastern) instead of Pacific. Describe what must change (timestamp storage, date boundaries, ETL windows, backfills, multi-timezone support, and how to avoid double-counting at day boundaries).

Focus on correctness, performance, and maintainability.

Question

Raw source tables (all timestamps stored in UTC):

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) — a row means a follow relationship was created at created_at and remains active afterward
user_events (event_id PK, user_id, event_type, occurred_at TIMESTAMP)

Reporting is initially in America/Los_Angeles (Pacific).

Define the required daily KPIs (one value per report date, in the reporting timezone):

new_users : users who signed up on the report date
new_users_change_vs_7d_ago : new_users(today) − new_users(today − 7 days)
DAU : distinct users who were "active" that day. Treat a user as active if they have any event in user_events that day (equivalently, posted a photo OR followed someone — align the exact definition with the interviewer).
DAU_change_vs_7d_ago
new_photos : photos created on the report date
new_photos_per_DAU : new_photos / DAU
new_photos_change_vs_7d_ago
total_followers : cumulative count of follow relationships as of end of the report date
% users with 0 followers (inbound follows, as of end of date)
% users with 1–2 followers
% users with 3+ followers

Tasks:

Data model / table design. Design the derived table(s) that power this dashboard for a huge dataset (fast reads). Specify schemas (columns, grain, keys), what is precomputed vs derived, and key considerations (partitioning/clustering, incremental loads, idempotency, late-arriving data, correctness vs cost).
Daily population strategy. Describe how you would populate/refresh these tables every day from the raw tables (high-level ETL steps; SQL/pseudocode acceptable).
Add week-1 retention. For each signup cohort date D, what fraction of that cohort is active one week later (use the same "active" definition as DAU). Describe the required table changes and how to compute it efficiently.
Switch to weekly rollups. The dashboard should support weekly rollups instead of daily. Describe what changes in the table design and computation (date grain, how "change vs 7d ago" maps to weekly, additive vs non-additive metrics, partial weeks).
Change the reporting timezone. A New York office wants metrics reported in America/New_York (Eastern) instead of Pacific. Describe what must change (timestamp storage, date boundaries, ETL windows, backfills, multi-timezone support, and how to avoid double-counting at day boundaries).

Focus on correctness, performance, and maintainability.

Design KPI dashboard tables: retention, weekly rollups, timezone

Quick Overview

Design KPI dashboard tables: retention, weekly rollups, timezone

Question

Submit Your Answer to Earn 20XP

Design KPI dashboard tables: retention, weekly rollups, timezone

Quick Overview

Design KPI dashboard tables: retention, weekly rollups, timezone

Question

Submit Your Answer to Earn 20XP