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.
Overview: 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.
You are given a table shop_views that records when users view shops. Write SQL to return, for every shop_id and each calendar date from 2025-05-26 through 2025-06-01 (inclusive), the number of unique users who viewed that shop on that date. Include dates with zero views for each shop.
Tables
shop_views(user_id INTEGER, shop_id INTEGER, view_time TIMESTAMP)
Hints
- Truncate timestamps to the calendar day (e.g., view_time::date or CAST(view_time AS DATE)).
- Count DISTINCT user_id per shop_id and per date.