Generate Daily Unique User Views for Each Shop
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
shop_views
+---------+---------+---------------------+
| user_id | shop_id | view_time |
+---------+---------+---------------------+
| 101 | 1 | 2023-05-01 10:00:00 |
| 102 | 1 | 2023-05-01 11:12:00 |
| 101 | 2 | 2023-05-02 09:35:00 |
| 103 | 1 | 2023-05-02 14:00:00 |
| 104 | 3 | 2023-05-02 15:32:00 |
+---------+---------+---------------------+
##### Scenario
The commerce team wants a daily report of how many unique users saw each shop in the last 7 days.
##### Question
Write SQL to return, for every shop_id and calendar date in the past 7 days, the number of unique users who viewed that shop.
##### Hints
Use DATE_TRUNC or CAST to day, DISTINCT user_id, and filter on current_date-7.
Quick Answer: This question evaluates a candidate's ability to perform data manipulation and aggregation using SQL (and optionally Python), focusing on counting unique users per shop by calendar date and handling date-based filtering.