Katkat
Back

Write SQL for app metrics

NextdoorData Engineer · Technical Screen
HardData EngineerTechnical ScreenCoding & Algorithms

Last updated

This question evaluates proficiency in SQL-based data engineering, focusing on time-zone-aware timestamp handling, aggregation of event and relationship data, cohort retention analysis, and computation of product metrics like DAU, new users, photo activity, and follower distributions.

You are given a simplified schema for a photo-sharing app:

  • users(user_id, user_name, joined_at)
  • photos(photo_id, user_id, created_at)
  • follows(follower_user_id, followed_user_id, created_at)
  • user_events(event_id, user_id, event_type, occurred_at)

Assume all timestamps are stored in UTC. Unless otherwise stated, reporting should use the America/Los_Angeles timezone. A row in follows represents a follow created at created_at and still active afterward. DAU means the number of distinct users with at least one row in user_events on that local calendar day.

Also assume the dashboard is backed by these aggregate tables:

  • kpi_daily_base(report_date, new_users, total_users_eod, dau, new_photos, total_follow_edges_eod)
  • follower_bucket_daily(report_date, bucket, user_count) where bucket is one of 0, 1_2, or 3_plus
  • retention_cohort_daily(cohort_date, cohort_size, retained_w1_users)

Write SQL for the following tasks:

  1. Count the total number of users.
  2. Find the calendar month with the highest number of new user signups.
  3. Find the user with the largest follower count.
  4. Populate the daily aggregate tables from the raw tables so the dashboard can report:
  • new users
  • change versus 7 days earlier
  • DAU
  • change versus 7 days earlier
  • new photos
  • new photos per DAU
  • change versus 7 days earlier
  • total follower relationships
  • percentage of users with 0 followers
  • percentage of users with 1-2 followers
  • percentage of users with 3+ followers
  1. Extend the SQL to compute week-1 retention, defined as: among users who signed up on day D, the share who had at least one event on days D+7 through D+13 in the reporting timezone.