Join datasets and compute conversion by assignment
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two CSVs. Create tables and write SQL to produce both visit-level and visitor-level conversion datasets, then aggregate conversion by assignment and country. Use the following schema and sample data.
Schema:
- visit(id_visitor BIGINT, ts TIMESTAMP, country STRING, assign TINYINT)
- booking(id_booking BIGINT, id_visitor BIGINT, ts TIMESTAMP)
Sample tables (timestamps are UTC):
visit
+------------+---------------------+---------+--------+
| id_visitor | ts | country | assign |
+------------+---------------------+---------+--------+
| 101 | 2025-01-03 09:12:00 | US | 1 |
| 101 | 2025-01-05 10:00:00 | US | 1 |
| 102 | 2025-01-04 14:30:00 | CA | 0 |
| 103 | 2025-01-04 15:00:00 | US | 1 |
| 104 | 2025-01-06 08:00:00 | GB | 0 |
| 105 | 2025-01-06 09:10:00 | US | 0 |
+------------+---------------------+---------+--------+
booking
+------------+------------+---------------------+
| id_booking | id_visitor | ts |
+------------+------------+---------------------+
| 5001 | 101 | 2025-01-05 12:00:00 |
| 5002 | 102 | 2025-01-04 16:00:00 |
| 5003 | 103 | 2025-01-10 09:00:00 |
| 5004 | 101 | 2025-01-03 08:00:00 |
| 5005 | 105 | 2025-02-01 10:00:00 |
+------------+------------+---------------------+
Requirements:
1) Visit-level dataset: one row per visit with columns (id_visitor, visit_ts, country, assign, booked_flag). booked_flag=1 if there exists a booking for the same id_visitor with booking.ts >= visit.ts and < min(next_visit.ts, visit.ts + INTERVAL 28 DAY); otherwise 0. Ensure a single booking is not double-counted across multiple visits for the same visitor.
2) Visitor-level dataset: one row per visitor with columns (id_visitor, first_visit_ts, country_at_first_visit, assign_at_first_visit, booked_flag_28d). booked_flag_28d=1 if any booking.ts is in [first_visit_ts, first_visit_ts + 28 days); otherwise 0. If a visitor has conflicting assign values across visits, use the earliest observed assign.
3) Aggregations: for each of the two datasets, output counts by (assign, country): visits_or_visitors, bookers, conversion = bookers / visits_or_visitors. Be explicit about handling duplicates and timezone assumptions.
Provide ANSI SQL (CTEs allowed) that runs on a typical data warehouse (e.g., BigQuery/Snowflake/Postgres) and produces the specified aggregations.
Overview: This question evaluates data manipulation skills around time-based joins, event attribution, deduplication, and conversion metric computation within the Data Manipulation (SQL/Python) domain.
Visit-Level Conversion Attribution by Booking Window
Build a visit-level conversion dataset from `visit` and `booking`. Each visit has a booking attribution window that starts at `visit.ts` inclusive and ends at the earlier of the next visit timestamp for that visitor or `visit.ts + INTERVAL '28 days'`, exclusive. Return one row per visit with `id_visitor`, formatted `visit_ts`, `country`, `assign`, and `booked_flag` set to 1 if at least one booking falls inside that window, otherwise 0. Order by visitor and visit timestamp.
Tables
visit(id_visitor BIGINT, ts TIMESTAMP, country VARCHAR(2), assign INTEGER)
booking(id_booking BIGINT, id_visitor BIGINT, ts TIMESTAMP)
Hints
- Use LEAD() over visits partitioned by id_visitor to find next_visit_ts.
- Build the booking window end as the lesser of next_visit_ts and visit_ts + INTERVAL '28' DAY, then left join bookings into that window and aggregate.
Visitor-Level 28-Day Conversion Flag
Build a visitor-level conversion dataset from `visit` and `booking`. For each visitor, use the earliest visit as `first_visit_ts`, carry its country and assign values, and set `booked_flag_28d` to 1 if the visitor has any booking in `[first_visit_ts, first_visit_ts + INTERVAL '28 days')`. Return one row per visitor ordered by `id_visitor`.
Tables
visit(id_visitor BIGINT, ts TIMESTAMP, country VARCHAR(2), assign INTEGER)
booking(id_booking BIGINT, id_visitor BIGINT, ts TIMESTAMP)
Hints
- Use ROW_NUMBER() partitioned by id_visitor and ordered by ts to pick each visitor's first visit.
- After identifying first visits, left join to booking on id_visitor and a 28-day time window, aggregating to a 0/1 flag.
Aggregating Visit- and Visitor-Level Conversion by Assignment and Country
Reconstruct both the visit-level and visitor-level conversion datasets, then aggregate each by assignment and country. Return `dataset_level`, `assign`, `country`, `visits_or_visitors`, `bookers`, and `conversion`, where conversion is bookers divided by the dataset row count rounded to 4 decimals.
Tables
visit(id_visitor BIGINT, ts TIMESTAMP, country VARCHAR(2), assign INTEGER)
booking(id_booking BIGINT, id_visitor BIGINT, ts TIMESTAMP)
Hints
- Reuse the CTEs that define visit-level and visitor-level datasets, then aggregate each by assign and country.
- Add a dataset_level column (e.g., 'visit' vs 'visitor') and UNION ALL the two aggregated result sets into a single output.