Determine Old vs. New Users' Shop Visibility Changes

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Determine Old vs. New Users' Shop Visibility Changes