Katkat
Back

Determine Old vs. New Users' Shop Visibility Changes

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question evaluates SQL-focused data manipulation skills and analytical competency in cohort definition and metric selection for comparing behavioral propensities, specifically assessing how to measure the likelihood of old versus new users switching a shop profile to invisible.

SHOP_VISIBILITY_HISTORY

+----------+----------------+---------------------+-------------------+---------+ | user_id | user_signup_dt | action_timestamp | visibility_status | shop_id | +----------+----------------+---------------------+-------------------+---------+ | 101 | 2020-03-10 | 2023-07-01 10:04:11 | invisible | 555 | | 102 | 2023-06-20 | 2023-07-02 08:22:37 | visible | 556 | | 103 | 2021-11-05 | 2023-07-01 14:15:02 | invisible | 557 | | 104 | 2023-07-01 | 2023-07-02 16:45:09 | invisible | 555 | | 105 | 2020-01-18 | 2023-07-03 09:30:00 | visible | 558 | +----------+----------------+---------------------+-------------------+---------+

Scenario

E-commerce platform wants to know whether existing users are more likely than newly registered users to set a shop profile to invisible.

Question

Given historical shop visibility actions, write a SQL query that computes a metric of your choice that compares the propensity of old users versus new users to switch a shop profile to invisible. Briefly justify why the metric you chose is appropriate.

Hints

Define "old" vs "new" users, then calculate the proportion of invisible actions per group.