Design SQL Query for Shop Visibility and User Activity Metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
SHOP_VISIBILITY
+----------+---------+------------+------------+-------------+--------------+
| user_id | shop_id | event_date | is_visible | signup_date | action_type |
+----------+---------+------------+------------+-------------+--------------+
| 101 | 12 | 2023-06-01 | TRUE | 2023-05-20 | view |
| 102 | 18 | 2023-06-01 | FALSE | 2021-11-02 | view |
| 103 | 44 | 2023-06-02 | TRUE | 2023-06-01 | click |
| 104 | 12 | 2023-06-02 | TRUE | 2020-01-15 | purchase |
| 105 | 18 | 2023-06-02 | FALSE | 2023-05-30 | view |
+----------+---------+------------+------------+-------------+--------------+
##### Scenario
E-commerce ‘shop visibility’ dataset used to test SQL skills and metric design.
##### Question
Write a query that for each shop returns total visible days, ordered descending (GROUP BY, HAVING, ORDER BY). Extend it to include shop category with a JOIN and apply CASE logic for visibility buckets. Using window functions, rank shops by average daily visibility within their category. Define and implement a metric—using today’s snapshot—to compare activity of new versus old users by grouping users into tenure buckets and reporting activity rates.
##### Hints
Compute tenure as DATEDIFF(today, signup_date); activity = active_events / users.
Overview: This question evaluates proficiency in SQL-based data manipulation—aggregation, JOINs, CASE expressions, window functions, ranking, and metric design for user tenure segmentation and temporal activity comparisons.
Per-shop visible days
Compute the number of distinct days each shop was visible (is_visible = TRUE). Exclude shops with zero visible days and order results by visible_days descending, then shop_id ascending.
Tables
SHOP_VISIBILITY(user_id INTEGER, shop_id INTEGER, event_date DATE, is_visible BOOLEAN, signup_date DATE, action_type VARCHAR(20))
Hints
- Use COUNT(DISTINCT ...) on event_date conditioned by is_visible
- Filter out zero counts with HAVING
Visibility by category rank
For each shop, join its category, compute visible_days across all dates, derive avg_daily_visibility = visible_days / total_distinct_event_days, bucket visibility (None/Low/High), and rank shops within their category by avg_daily_visibility (ties get the same rank).
Tables
SHOP_VISIBILITY(user_id INTEGER, shop_id INTEGER, event_date DATE, is_visible BOOLEAN, signup_date DATE, action_type VARCHAR(20))
SHOP_DIM(shop_id INTEGER, category VARCHAR(50))
Hints
- Aggregate to shop-day first to avoid double counting
- Use total distinct event days as the denominator
Snapshot activity by tenure
## Snapshot activity by user tenure
Using the `SHOP_VISIBILITY` table and a **fixed snapshot date of `2023-06-02`**, summarize that day's activity broken down by each user's tenure bucket.
Consider **only the events that occurred on the snapshot date** (`event_date = DATE '2023-06-02'`). For each such event, compute the user's tenure in days as `snapshot_date - signup_date` and assign a tenure bucket:
- **New** — tenure `< 30` days
- **Established** — tenure `30` to `179` days (i.e. `>= 30` and `< 180`)
- **Old** — tenure `>= 180` days
Return **one row per tenure bucket that has at least one event on the snapshot date**, with the following columns:
- `snapshot_date` — the constant date `2023-06-02`
- `tenure_bucket` — `'New'`, `'Established'`, or `'Old'`
- `active_users` — count of **distinct** `user_id` values active in that bucket on the snapshot date
- `active_events` — total number of events (rows) in that bucket on the snapshot date
- `activity_rate` — `active_events / active_users` (events per active user), rounded to 4 decimal places
Order the result by `tenure_bucket` ascending (alphabetical).
Tables
SHOP_VISIBILITY(user_id INTEGER, shop_id INTEGER, event_date DATE, is_visible BOOLEAN, signup_date DATE, action_type VARCHAR(20))
SHOP_DIM(shop_id INTEGER, category VARCHAR(50))
Hints
- Pin the snapshot date in a CTE and join on event_date to keep only that day's events.
- In PostgreSQL, subtracting two DATEs (snapshot_date - signup_date) yields an integer day count - no DATEDIFF needed.