Determine Old vs. New Users' Shop Visibility Changes

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.

Old vs New Invisible Action Rate and Rate Ratio

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.

Table SHOP_VISIBILITY_HISTORY

column	type	constraints
user_id	INTEGER
user_signup_dt	DATE
action_timestamp	TIMESTAMP
visibility_status	VARCHAR
shop_id	INTEGER

Sample data:

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

Expected output

One row per cohort with action counts, invisible action rate, and rate ratio versus the new cohort.

invisible_actions	invisible_rate	relative_to_new	total_actions	user_cohort
2	0.6667	1.3333	3	old
1	0.5	1	2	new

Hints

1. Use date subtraction to classify old versus new users.
2. Cast the numerator to numeric before dividing so the rate is not integer-divided.
3. Compute the new cohort rate separately, then divide each cohort rate by it with NULLIF.