Analyze New Shops' Activity Compared to Existing Ones
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
shops
+---------+------------+---------------+
| shop_id | created_at | category |
+---------+------------+---------------+
| 1 | 2024-01-03 | fashion |
| 2 | 2023-11-20 | home |
| 3 | 2024-02-10 | electronics |
| 4 | 2023-09-15 | fashion |
| 5 | 2024-03-01 | toys |
+---------+------------+---------------+
page_views
+----------+---------+---------------------+
| shop_id | user_id | viewed_at |
+----------+---------+---------------------+
| 1 | 501 | 2024-03-10 09:01:15 |
| 2 | 877 | 2024-03-10 09:05:11 |
| 1 | 612 | 2024-03-10 09:06:55 |
| 3 | 501 | 2024-03-10 09:07:15 |
| 4 | 933 | 2024-03-10 09:09:05 |
+----------+---------+---------------------+
##### Scenario
Marketplace wants to understand whether newly onboarded shops are more active and visible than existing ones.
##### Question
Given the tables below, write SQL to compute daily shop visibility (number of page_views per shop). Then propose and calculate a metric that demonstrates whether shops created in the last 30 days are more active than older shops.
##### Hints
Join page_views with shops, group by shop_id and date, compare average views of new-shop cohort (<30 days) versus older cohort; consider median to limit outlier impact.
Overview: This question evaluates a data scientist's skill in data manipulation, time-series aggregation, cohort analysis, and metric design using SQL or Python to quantify shop visibility via page view counts.
A marketplace wants to understand whether newly onboarded shops are more active and visible than existing shops.
Use `shops` and `page_views` to compute cohort metrics for each day that appears in `page_views`:
1. Treat a shop as `new` on a day if `created_at` is within the 30 days ending on that day, inclusive. Otherwise treat it as `old`.
2. For each active day, include every shop that had already been created by that day, even if it had zero page views that day.
3. For each `(day, cohort)`, return:
- `day`
- `cohort`
- `shops_in_cohort`
- `total_views`
- `avg_views_per_shop`, rounded to 3 decimals
- `median_views_per_shop`, rounded to 3 decimals
- `new_to_old_avg_ratio`: the new cohort average divided by the old cohort average for that same day, rounded to 3 decimals
Order by `day`, then `cohort`.
Tables
shops(shop_id INTEGER, created_at DATE, category VARCHAR(50))
page_views(shop_id INTEGER, user_id INTEGER, viewed_at TIMESTAMP)
Hints
- Generate the active day set from page_views first.
- Cross join active days to eligible shops so zero-view shops are included.