Determine Old vs. New Users' Shop Visibility Changes
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
An e-commerce platform wants to know whether existing users are more likely than newly registered users to set a shop profile to invisible.
Using `SHOP_VISIBILITY_HISTORY`, classify each visibility action into a cohort based on the number of days between `user_signup_dt` and `CAST(action_timestamp AS DATE)`:
- `old`: the action occurred at least 30 days after signup
- `new`: the action occurred fewer than 30 days after signup
Return one row per cohort with these columns:
- `user_cohort`
- `total_actions`
- `invisible_actions`: actions where `visibility_status = 'invisible'`
- `invisible_rate`: `invisible_actions / total_actions`, rounded to 4 decimals
- `relative_to_new`: the cohort's invisible rate divided by the new cohort's invisible rate, rounded to 4 decimals
Order `old` before `new`.
Tables
SHOP_VISIBILITY_HISTORY(user_id INTEGER, user_signup_dt DATE, action_timestamp TIMESTAMP, visibility_status VARCHAR, shop_id INTEGER)
Hints
- Use date subtraction to classify old versus new users.
- Cast the numerator to numeric before dividing so the rate is not integer-divided.