cswork / libraryOpen practice workspace

Coding questions · TikTok

Analyze shopping funnel with joins and windows

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.

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.

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
countryVARCHAR(2)NOT NULL

Sample data:

countryuser_id
US1
US2
CA3
US4

Table sessions

columntypeconstraints
session_idINTPRIMARY KEY
user_idINTNOT NULL
session_startTIMESTAMPNOT NULL

Sample data:

session_idsession_startuser_id
102025-08-01 10:00:001
112025-08-02 09:00:001
122025-08-02 12:00:002
132025-08-03 18:00:003
142025-08-03 20:00:004

Table events

columntypeconstraints
event_idBIGINTPRIMARY KEY
session_idINTNOT NULL
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_typeVARCHAR(30)NOT NULL
product_idVARCHAR(10)NOT NULL

Sample data:

event_idevent_timeevent_typeproduct_idsession_iduser_id
12025-08-01 10:01:00view_productA101
22025-08-01 10:02:00add_to_cartA101
32025-08-01 10:05:00checkout_startA101
42025-08-01 10:06:00purchaseA101
52025-08-02 09:05:00view_productB111
62025-08-02 09:06:00add_to_cartB111
72025-08-02 09:06:00add_to_cartB111
82025-08-02 09:10:00checkout_startB111
92025-08-02 12:01:00view_productC122
102025-08-02 12:02:00add_to_cartC122
112025-08-02 12:20:00view_productD122
122025-08-03 18:10:00view_productA133
132025-08-03 20:03:00view_productE144
142025-08-03 20:05:00add_to_cartE144
152025-08-03 20:06:00purchaseE144

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
session_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
revenueNUMERIC(10,2)NOT NULL

Sample data:

order_idorder_timerevenuesession_iduser_id
5012025-08-01 10:06:0039.99101
5022025-08-03 20:06:0012144

Expected output

One row per (country, event_date) with step counts, conversion counts, and conversion rates for funnels that started (view_product) on that date. Rates are NULL when the denominator is 0.

add_cntadd_to_checkout_cntadd_to_checkout_ratecheckout_cntcheckout_to_purchase_cntcheckout_to_purchase_ratecountryevent_dateview_cntview_to_add_cntview_to_add_rate
111111US2025-08-01111
210.5100US2025-08-02320.6667
00NULL00NULLCA2025-08-03100
10000NULLUS2025-08-03111

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.
  3. Use NULLIF(denominator, 0) to avoid division-by-zero and return NULL rates when the denominator is 0.

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
countryVARCHAR(2)NOT NULL

Sample data:

countryuser_id
US1
US2
CA3
US4

Table sessions

columntypeconstraints
session_idINTPRIMARY KEY
user_idINTNOT NULL
session_startTIMESTAMPNOT NULL

Sample data:

session_idsession_startuser_id
102025-08-01 10:00:001
112025-08-02 09:00:001
122025-08-02 12:00:002
132025-08-03 18:00:003
142025-08-03 20:00:004

Table events

columntypeconstraints
event_idBIGINTPRIMARY KEY
session_idINTNOT NULL
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_typeVARCHAR(30)NOT NULL
product_idVARCHAR(10)NOT NULL

Sample data:

event_idevent_timeevent_typeproduct_idsession_iduser_id
12025-08-01 10:01:00view_productA101
22025-08-01 10:02:00add_to_cartA101
32025-08-01 10:05:00checkout_startA101
42025-08-01 10:06:00purchaseA101
52025-08-02 09:05:00view_productB111
62025-08-02 09:06:00add_to_cartB111
72025-08-02 09:06:00add_to_cartB111
82025-08-02 09:10:00checkout_startB111
92025-08-02 12:01:00view_productC122
102025-08-02 12:02:00add_to_cartC122
112025-08-02 12:20:00view_productD122
122025-08-03 18:10:00view_productA133
132025-08-03 20:03:00view_productE144
142025-08-03 20:05:00add_to_cartE144
152025-08-03 20:06:00purchaseE144

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
session_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
revenueNUMERIC(10,2)NOT NULL

Sample data:

order_idorder_timerevenuesession_iduser_id
5012025-08-01 10:06:0039.99101
5022025-08-03 20:06:0012144

Expected output

One row per user, showing the most frequent drop-off step (with tie-break by furthest step), the number of drop-off session/product instances at that step, and the median time from the previous step to that drop-off (seconds). Users with no drop-offs return NULL step and 0 count.

countrydrop_off_sessionsdrop_off_stepmedian_elapsed_secondsuser_id
US1checkout_start2401
US1add_to_cart602
CA1view_productNULL3
US0NULLNULL4

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.
  3. After computing a drop_off_step per session/product, aggregate by (user_id, drop_off_step), then rank to pick the top step with a deterministic tie-break.

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
countryVARCHAR(2)NOT NULL

Sample data:

countryuser_id
US1
US2
CA3
US4

Table sessions

columntypeconstraints
session_idINTPRIMARY KEY
user_idINTNOT NULL
session_startTIMESTAMPNOT NULL

Sample data:

session_idsession_startuser_id
102025-08-01 10:00:001
112025-08-02 09:00:001
122025-08-02 12:00:002
132025-08-03 18:00:003
142025-08-03 20:00:004

Table events

columntypeconstraints
event_idBIGINTPRIMARY KEY
session_idINTNOT NULL
user_idINTNOT NULL
event_timeTIMESTAMPNOT NULL
event_typeVARCHAR(30)NOT NULL
product_idVARCHAR(10)NOT NULL

Sample data:

event_idevent_timeevent_typeproduct_idsession_iduser_id
12025-08-01 10:01:00view_productA101
22025-08-01 10:02:00add_to_cartA101
32025-08-01 10:05:00checkout_startA101
42025-08-01 10:06:00purchaseA101
52025-08-02 09:05:00view_productB111
62025-08-02 09:06:00add_to_cartB111
72025-08-02 09:06:00add_to_cartB111
82025-08-02 09:10:00checkout_startB111
92025-08-02 12:01:00view_productC122
102025-08-02 12:02:00add_to_cartC122
112025-08-02 12:20:00view_productD122
122025-08-03 18:10:00view_productA133
132025-08-03 20:03:00view_productE144
142025-08-03 20:05:00add_to_cartE144
152025-08-03 20:06:00purchaseE144

Table orders

columntypeconstraints
order_idINTPRIMARY KEY
user_idINTNOT NULL
session_idINTNOT NULL
order_timeTIMESTAMPNOT NULL
revenueNUMERIC(10,2)NOT NULL

Sample data:

order_idorder_timerevenuesession_iduser_id
5012025-08-01 10:06:0039.99101
5022025-08-03 20:06:0012144

Expected output

Rows by (country, cohort_date) for days that have at least one deduped add_to_cart cohort member; includes daily cohort conversion and a 7-day rolling conversion rate ending on that date (using window sums).

add_user_productscohort_conversion_ratecohort_dateconverted_user_productscountryrolling_7d_conversion_rate
112025-08-011US1
202025-08-020US0.333333
112025-08-031US0.5

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).
  3. Compute daily counts, then use window SUM(...) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to get the rolling 7-day numerator/denominator.