Model Data for Analytics, Reporting, and Applications

Problem Statement

In video platforms like TikTok, raw event logs must be cleansed, enriched with dimensional metadata, and aggregated into daily summary tables to support analytical dashboards and real-time recommendation features.

Implement the function build_daily_user_metrics(events, user_dim, report_date) that reads raw video watch events and user profile records, filters the events for a given calendar date (report_date), and produces an aggregated daily metrics dataset sorted by user_id in ascending order.

Function Signature

def build_daily_user_metrics(
    events: list[dict],
    user_dim: list[dict],
    report_date: str
) -> list[dict]:
    pass

Input Formats

• events: A list of dictionaries representing view events:
  • "event_id" (str): Unique event identifier.
  • "user_id" (str): ID of the user.
  • "video_id" (str): ID of the video watched.
  • "watch_seconds" (int): Seconds watched in this session (may contain corrupted values greater than video_duration).
  • "video_duration" (int): Total length of the video in seconds.
  • "timestamp" (str): UTC timestamp formatted as "YYYY-MM-DDTHH:MM:SSZ".
• user_dim: A list of dictionaries representing the user dimension table:
  • "user_id" (str): Unique user identifier.
  • "country" (str): User's country code.
  • "signup_date" (str): Date formatted as "YYYY-MM-DD".
• report_date: Target date string formatted as "YYYY-MM-DD".

Output Format

Return a list of dictionaries for all users who had at least one event on report_date, sorted by user_id ascending. Each record must contain:

• "user_id" (str)
• "country" (str): User's country from user_dim. If the user is missing from user_dim, use "UNKNOWN".
• "total_watch_seconds" (int): Sum of valid watch times on report_date. For any event where watch_seconds > video_duration, cap the watch time at video_duration. If watch_seconds = video_duration (with video_duration > 0).
• "is_new_user" (bool): True if signup_date == report_date, else False.

Constraints

• 0 = 0

Example 1

events = [
    {
        "event_id": "e1",
        "user_id": "u1",
        "video_id": "v1",
        "watch_seconds": 45,
        "video_duration": 30,
        "timestamp": "2023-10-15T08:00:00Z"
    },
    {
        "event_id": "e2",
        "user_id": "u1",
        "video_id": "v2",
        "watch_seconds": 10,
        "video_duration": 60,
        "timestamp": "2023-10-15T09:30:00Z"
    },
    {
        "event_id": "e3",
        "user_id": "u2",
        "video_id": "v1",
        "watch_seconds": 15,
        "video_duration": 30,
        "timestamp": "2023-10-14T23:59:59Z"
    }
]

user_dim = [
    {"user_id": "u1", "country": "US", "signup_date": "2023-10-15"},
    {"user_id": "u2", "country": "SG", "signup_date": "2023-01-01"}
]

report_date = "2023-10-15"

# Output:
[
    {
        "user_id": "u1",
        "country": "US",
        "total_watch_seconds": 40,      # min(45, 30) + 10 = 30 + 10
        "unique_videos_watched": 2,
        "completions": 1,               # e1 completed (45 >= 30)
        "is_new_user": True
    }
]

Explanation: Event e3 occurred on 2023-10-14 and is excluded. For u1, watch time for e1 is capped at 30, giving a total of 40 seconds across 2 distinct videos. u1 signed up on 2023-10-15, so is_new_user is True.

Edge Cases to Consider

• Users present in events but missing in user_dim (country becomes "UNKNOWN", is_new_user becomes False).
• Events with negative watch times or watch times exceeding video durations.
• Multiple views of the same video by the same user on the same day.