Write dating profile report with final reviews
Overview
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.
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.