Write dating profile report with final reviews
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
Today is 2025-09-01. You need a daily dating-profile quality and engagement report that only includes profiles whose latest version has a final approved review as of 2025-09-01 00:00:00 UTC and are not deleted. Use the invented schema and sample data below, then: 1) Write SQL to produce a snapshot table with one row per qualifying profile showing user_id, country, profile_id, latest_version_id, approved_at, photos_count, bio_len, last_7d_views, last_7d_likes, and last_7d_like_rate (likes/views) using events from the 7 days ending 2025-09-01 (inclusive). 2) Aggregate country-level metrics: profiles_approved, pct_with_photo (photos_count>=1), pct_with_bio_10+ (LENGTH(bio_text)>=10), last_7d_views, last_7d_likes, and last_7d_like_rate. 3) Add validation queries that (a) flag profiles whose final review references a non-latest version, (b) detect multiple final reviews per profile, (c) surface profiles with late-arriving events after snapshot time, and (d) ensure idempotent reruns by time-bounding to the snapshot. Assume all timestamps are UTC.
Schema and small ASCII samples:
users
+----+-------------+---------+
| id | signup_date | country |
+----+-------------+---------+
| 1 | 2025-08-20 | US |
| 2 | 2025-08-25 | US |
| 3 | 2025-08-28 | CA |
| 4 | 2025-07-15 | US |
+----+-------------+---------+
profiles
+------------+---------+---------------------+------------+
| profile_id | user_id | created_at | is_deleted |
+------------+---------+---------------------+------------+
| 101 | 1 | 2025-08-21 10:00:00 | 0 |
| 102 | 2 | 2025-08-25 09:00:00 | 0 |
| 103 | 3 | 2025-08-28 12:30:00 | 1 |
| 104 | 4 | 2025-07-16 08:15:00 | 0 |
+------------+---------+---------------------+------------+
profile_versions
+-------------+------------+---------------------+-----------+--------------+
| version_id | profile_id | created_at | bio_text | photos_count |
+-------------+------------+---------------------+-----------+--------------+
| 1001 | 101 | 2025-08-21 10:05:00 | "Hi" | 2 |
| 1002 | 101 | 2025-08-29 07:00:00 | "Hello" | 3 |
| 1003 | 102 | 2025-08-25 09:05:00 | "Traveler"| 1 |
| 1004 | 103 | 2025-08-28 12:35:00 | "Foodie" | 0 |
| 1005 | 104 | 2025-08-20 18:00:00 | "Hiker" | 4 |
+-------------+------------+---------------------+-----------+--------------+
profile_reviews
+-----------+------------+-------------+----------+---------------------+----------+
| review_id | profile_id | version_id | status | reviewed_at | is_final |
+-----------+------------+-------------+----------+---------------------+----------+
| 5001 | 101 | 1001 | approved | 2025-08-21 12:00:00 | 0 |
| 5002 | 101 | 1002 | approved | 2025-08-29 12:00:00 | 1 |
| 5003 | 102 | 1003 | rejected | 2025-08-25 10:00:00 | 1 |
| 5004 | 103 | 1004 | approved | 2025-08-28 14:00:00 | 1 |
| 5005 | 104 | 1005 | pending | 2025-08-20 19:00:00 | 0 |
+-----------+------------+-------------+----------+---------------------+----------+
profile_events
+-----+------------+------------+---------------------+
| id | profile_id | event_type | event_time |
+-----+------------+------------+---------------------+
| 9001| 101 | view | 2025-08-31 10:00:00 |
| 9002| 101 | like | 2025-08-31 10:05:00 |
| 9003| 102 | view | 2025-08-30 09:30:00 |
| 9004| 104 | view | 2025-08-26 12:00:00 |
| 9005| 104 | like | 2025-08-31 14:00:00 |
+-----+------------+------------+---------------------+
Assumptions: latest version is the max(created_at) per profile at or before 2025-09-01 00:00:00; qualifying profiles must have is_deleted=0 and a final review with status='approved' on that latest version by snapshot time; events window is [2025-08-26 00:00:00, 2025-09-01 23:59:59]. Provide the SQL (CTEs + window functions) and the validation queries.
Quick Answer: This question evaluates SQL and data-manipulation proficiency, including temporal joins, versioning, time-bounded snapshotting, windowed aggregations, and validation checks for data quality and idempotency.