Coding questions · Meta
Join datasets and compute conversion by assignment
This question evaluates data manipulation skills around time-based joins, event attribution, deduplication, and conversion metric computation within the Data Manipulation (SQL/Python) domain.
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:
- 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.
- 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.
- 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.
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.
Table visit
| column | type | constraints |
|---|---|---|
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| assign | INTEGER | NOT NULL |
Sample data:
| assign | country | id_visitor | ts |
|---|---|---|---|
| 1 | US | 101 | 2025-01-03 09:12:00 |
| 1 | US | 101 | 2025-01-05 10:00:00 |
| 0 | CA | 102 | 2025-01-04 14:30:00 |
| 1 | US | 103 | 2025-01-04 15:00:00 |
| 0 | GB | 104 | 2025-01-06 08:00:00 |
| 0 | US | 105 | 2025-01-06 09:10:00 |
Table booking
| column | type | constraints |
|---|---|---|
| id_booking | BIGINT | PRIMARY KEY, NOT NULL |
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| 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 |
Expected output
One row per visit with its booking window attribution flag. Columns: id_visitor, visit_ts, country, assign, booked_flag.
| assign | booked_flag | country | id_visitor | visit_ts |
|---|---|---|---|---|
| 1 | 0 | US | 101 | 2025-01-03 09:12:00 |
| 1 | 1 | US | 101 | 2025-01-05 10:00:00 |
| 0 | 1 | CA | 102 | 2025-01-04 14:30:00 |
| 1 | 1 | US | 103 | 2025-01-04 15:00:00 |
| 0 | 0 | GB | 104 | 2025-01-06 08:00:00 |
| 0 | 1 | US | 105 | 2025-01-06 09:10:00 |
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.
Table visit
| column | type | constraints |
|---|---|---|
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| assign | INTEGER | NOT NULL |
Sample data:
| assign | country | id_visitor | ts |
|---|---|---|---|
| 1 | US | 101 | 2025-01-03 09:12:00 |
| 1 | US | 101 | 2025-01-05 10:00:00 |
| 0 | CA | 102 | 2025-01-04 14:30:00 |
| 1 | US | 103 | 2025-01-04 15:00:00 |
| 0 | GB | 104 | 2025-01-06 08:00:00 |
| 0 | US | 105 | 2025-01-06 09:10:00 |
Table booking
| column | type | constraints |
|---|---|---|
| id_booking | BIGINT | PRIMARY KEY, NOT NULL |
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| 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 |
Expected output
One row per visitor with their first visit and a 28-day booking flag. Columns: id_visitor, first_visit_ts, country_at_first_visit, assign_at_first_visit, booked_flag_28d.
| assign_at_first_visit | booked_flag_28d | country_at_first_visit | first_visit_ts | id_visitor |
|---|---|---|---|---|
| 1 | 1 | US | 2025-01-03 09:12:00 | 101 |
| 0 | 1 | CA | 2025-01-04 14:30:00 | 102 |
| 1 | 1 | US | 2025-01-04 15:00:00 | 103 |
| 0 | 0 | GB | 2025-01-06 08:00:00 | 104 |
| 0 | 1 | US | 2025-01-06 09:10:00 | 105 |
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.
Table visit
| column | type | constraints |
|---|---|---|
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| assign | INTEGER | NOT NULL |
Sample data:
| assign | country | id_visitor | ts |
|---|---|---|---|
| 1 | US | 101 | 2025-01-03 09:12:00 |
| 1 | US | 101 | 2025-01-05 10:00:00 |
| 0 | CA | 102 | 2025-01-04 14:30:00 |
| 1 | US | 103 | 2025-01-04 15:00:00 |
| 0 | GB | 104 | 2025-01-06 08:00:00 |
| 0 | US | 105 | 2025-01-06 09:10:00 |
Table booking
| column | type | constraints |
|---|---|---|
| id_booking | BIGINT | PRIMARY KEY, NOT NULL |
| id_visitor | BIGINT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| 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 |
Expected output
Aggregated conversion metrics for both visit-level and visitor-level datasets by assignment and country. One row per (dataset_level, assign, country).
| assign | bookers | conversion | country | dataset_level | visits_or_visitors |
|---|---|---|---|---|---|
| 0 | 1 | 1 | CA | visit | 1 |
| 0 | 0 | 0 | GB | visit | 1 |
| 0 | 1 | 1 | US | visit | 1 |
| 1 | 2 | 0.6667 | US | visit | 3 |
| 0 | 1 | 1 | CA | visitor | 1 |
| 0 | 0 | 0 | GB | visitor | 1 |
| 0 | 1 | 1 | US | visitor | 1 |
| 1 | 2 | 1 | US | visitor | 2 |
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.