Quick Overview

This question evaluates SQL-based data manipulation skills—specifically event deduplication, temporal ordering, joins, and window-function usage for multi-step funnel conversion analysis with session- and user/product-level scoping, and is categorized as Data Manipulation (SQL/Python) for a data scientist role.

Analyze shopping funnel with joins and windows

Company: TikTok

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Write SQL (PostgreSQL) to analyze a 4-step shopping funnel: view_product → add_to_cart → checkout_start → purchase. Use the schema and sample data below. Assume all timestamps are UTC and duplicates can occur. Schema: users(user_id INT, country TEXT); sessions(session_id INT, user_id INT, session_start TIMESTAMP); events(session_id INT, user_id INT, event_time TIMESTAMP, event_type TEXT, product_id TEXT); orders(order_id INT, user_id INT, session_id INT, order_time TIMESTAMP, revenue NUMERIC(10,2)). Sample rows: users: [ (1,'US'), (2,'US'), (3,'CA'), (4,'US') ]; sessions: [ (10,1,'2025-08-01 10:00'), (11,1,'2025-08-02 09:00'), (12,2,'2025-08-02 12:00'), (13,3,'2025-08-03 18:00'), (14,4,'2025-08-03 20:00') ]; events: [ (10,1,'2025-08-01 10:01','view_product','A'), (10,1,'2025-08-01 10:02','add_to_cart','A'), (10,1,'2025-08-01 10:05','checkout_start','A'), (10,1,'2025-08-01 10:06','purchase','A'), (11,1,'2025-08-02 09:05','view_product','B'), (11,1,'2025-08-02 09:06','add_to_cart','B'), (11,1,'2025-08-02 09:06','add_to_cart','B'), (11,1,'2025-08-02 09:10','checkout_start','B'), (12,2,'2025-08-02 12:01','view_product','C'), (12,2,'2025-08-02 12:02','add_to_cart','C'), (12,2,'2025-08-02 12:20','view_product','D'), (13,3,'2025-08-03 18:10','view_product','A'), (14,4,'2025-08-03 20:03','view_product','E'), (14,4,'2025-08-03 20:05','add_to_cart','E'), (14,4,'2025-08-03 20:06','purchase','E') ]; orders: [ (501,1,10,'2025-08-01 10:06',39.99), (502,4,14,'2025-08-03 20:06',12.00) ]. Tasks: 1) By country and event_date (DATE(event_time)), compute step-to-step conversion rates view→add, add→checkout, checkout→purchase for 2025-08-01 to 2025-08-07. Deduplicate to the first occurrence of each step per (user_id, session_id, product_id). Only count a step-to-step conversion if both steps exist in timestamp order within the same (user_id, session_id, product_id). 2) For each user, in 2025-08, find their most frequent drop-off step (the last step reached in a session without reaching the next step either in the same session or within 24 hours by the same user and product). Also compute the median elapsed time from the preceding step to that drop-off across their sessions. 3) For each day in 2025-08, compute a 7-day rolling conversion rate from add_to_cart to purchase by country. Denominator: unique (user_id, product_id) add_to_cart events on day D. Numerator: those with a purchase by the same user and product within 7 days after the add_to_cart timestamp (across any session). Use window functions for deduping, step-chaining, and rolling calculations; be explicit about tie-breaking when multiple events share the same timestamp.

Overview: This question evaluates SQL-based data manipulation skills—specifically event deduplication, temporal ordering, joins, and window-function usage for multi-step funnel conversion analysis with session- and user/product-level scoping, and is categorized as Data Manipulation (SQL/Python) for a data scientist role.

Daily funnel conversion rates by country (dedupe + step chaining)

You are analyzing a 4-step shopping funnel in PostgreSQL: view_product → add_to_cart → checkout_start → purchase. Using the tables and sample data below, compute daily step-to-step conversion rates by country for dates 2025-08-01 through 2025-08-07 (inclusive). Requirements: 1) Define event_date as DATE(view_product_time) for each (user_id, session_id, product_id) funnel instance. 2) Deduplicate events to the FIRST occurrence of each step per (user_id, session_id, product_id, event_type). When multiple events share the same timestamp, break ties by smaller event_id. 3) Only count a step-to-step conversion if BOTH steps exist and are in strict timestamp order within the same (user_id, session_id, product_id): - view→add: add_time > view_time - add→checkout: checkout_time > add_time - checkout→purchase: purchase_time > checkout_time 4) Return one row per (country, event_date) where at least one view_product exists, with: - view_cnt, view_to_add_cnt, view_to_add_rate - add_cnt, add_to_checkout_cnt, add_to_checkout_rate - checkout_cnt, checkout_to_purchase_cnt, checkout_to_purchase_rate Use NULL for rates where the denominator is 0.

Tables

users(user_id INT, country VARCHAR(2))

sessions(session_id INT, user_id INT, session_start TIMESTAMP)

events(event_id BIGINT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(30), product_id VARCHAR(10))

orders(order_id INT, user_id INT, session_id INT, order_time TIMESTAMP, revenue NUMERIC(10,2))

Hints

  1. Use ROW_NUMBER() to keep the first occurrence per (user_id, session_id, product_id, event_type), ordering by (event_time, event_id).
  2. Pivot the four steps into columns and then use conditional COUNT(*) FILTER (...) to compute denominators/numerators.

Most frequent drop-off step per user + median time to drop-off

For each user (including users with zero drop-offs), analyze sessions in August 2025 (2025-08-01 through 2025-08-31) and find: 1) Their most frequent drop-off step, where a drop-off step is defined per (user_id, session_id, product_id) as: - The last funnel step reached in that session (view_product, add_to_cart, checkout_start) - SUCH THAT the next step in the funnel does NOT occur within 24 hours after that step time for the same (user_id, product_id), even if it happens in a different session. - If the session reaches purchase, it is NOT a drop-off. 2) The median elapsed time (in seconds) from the preceding step to that drop-off step, across all of the user's session/product drop-offs of that step. - For add_to_cart drop-offs: elapsed = add_time - view_time (within the same session/product) - For checkout_start drop-offs: elapsed = checkout_time - add_time - For view_product drop-offs: elapsed is NULL (no preceding step) Additional requirements: - Deduplicate events to the first occurrence per (user_id, session_id, product_id, event_type) using ORDER BY (event_time, event_id). - If there is a tie for most frequent drop-off step for a user, break ties by choosing the furthest step in the funnel: checkout_start > add_to_cart > view_product. - If a user has no drop-offs, return drop_off_step = NULL, drop_off_sessions = 0, and median_elapsed_seconds = NULL. Return columns: user_id, country, drop_off_step, drop_off_sessions, median_elapsed_seconds.

Tables

users(user_id INT, country VARCHAR(2))

sessions(session_id INT, user_id INT, session_start TIMESTAMP)

events(event_id BIGINT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(30), product_id VARCHAR(10))

orders(order_id INT, user_id INT, session_id INT, order_time TIMESTAMP, revenue NUMERIC(10,2))

Hints

  1. Build per-(user,session,product) timestamps for each step using a deduped events CTE.
  2. Use EXISTS to test whether the next step occurs within 24 hours, across any session, for the same user and product.

7-day rolling add→purchase conversion by country (daily cohorts + rolling window)

For each country and each day D in August 2025 (2025-08-01 through 2025-08-31), compute an add_to_cart→purchase conversion metric as follows: Step A (daily cohort conversion): - Denominator (daily): the number of UNIQUE (user_id, product_id) add_to_cart events on day D. - If the same user adds the same product multiple times on the same day, keep only the earliest add_to_cart on that day; if timestamps tie, keep smaller event_id. - Numerator (daily): among those denominator rows, count how many have at least one purchase event by the same user and product with event_time > add_time and event_time <= add_time + 7 days (purchases may occur in any session). Step B (7-day rolling conversion rate ending on D): - For each (country, D), compute a rolling 7-day conversion rate using the previous 6 cohort days plus day D: rolling_rate(D) = SUM(daily_numerator) over days [D-6 .. D] / SUM(daily_denominator) over days [D-6 .. D] Return rows for days that have at least one add_to_cart event, with columns: country, cohort_date, add_user_products, converted_user_products, cohort_conversion_rate, rolling_7d_conversion_rate.

Tables

users(user_id INT, country VARCHAR(2))

sessions(session_id INT, user_id INT, session_start TIMESTAMP)

events(event_id BIGINT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(30), product_id VARCHAR(10))

orders(order_id INT, user_id INT, session_id INT, order_time TIMESTAMP, revenue NUMERIC(10,2))

Hints

  1. First dedupe add_to_cart to one row per (user_id, product_id, day) using ROW_NUMBER().
  2. Use EXISTS to label whether that add had a purchase within 7 days (across any session).

Loading coding console...