Coding questions · Meta
Generate Daily Unique User Views for Each Shop
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.
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.
Daily unique shop views for the last 7 days
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.
Table shop_views
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | NOT NULL |
| shop_id | INTEGER | NOT NULL |
| view_time | TIMESTAMP | NOT NULL |
Sample data:
| shop_id | user_id | view_time |
|---|---|---|
| 1 | 101 | 2025-05-30 10:00:00 |
| 1 | 102 | 2025-05-30 11:12:00 |
| 2 | 101 | 2025-05-31 09:35:00 |
| 1 | 103 | 2025-06-01 14:00:00 |
| 3 | 104 | 2025-06-01 15:32:00 |
Expected output
One row per shop_id per calendar date between 2025-05-26 and 2025-06-01 (inclusive), with the count of distinct users who viewed that shop on that date. Include dates with zero views. Columns: shop_id (INTEGER), view_date (DATE), unique_user_views (INTEGER).
| shop_id | unique_user_views | view_date |
|---|---|---|
| 1 | 0 | 2025-05-26 |
| 1 | 0 | 2025-05-27 |
| 1 | 0 | 2025-05-28 |
| 1 | 0 | 2025-05-29 |
| 1 | 2 | 2025-05-30 |
| 1 | 0 | 2025-05-31 |
| 1 | 1 | 2025-06-01 |
| 2 | 0 | 2025-05-26 |
| 2 | 0 | 2025-05-27 |
| 2 | 0 | 2025-05-28 |
| 2 | 0 | 2025-05-29 |
| 2 | 0 | 2025-05-30 |
| 2 | 1 | 2025-05-31 |
| 2 | 0 | 2025-06-01 |
| 3 | 0 | 2025-05-26 |
| 3 | 0 | 2025-05-27 |
| 3 | 0 | 2025-05-28 |
| 3 | 0 | 2025-05-29 |
| 3 | 0 | 2025-05-30 |
| 3 | 0 | 2025-05-31 |
| 3 | 1 | 2025-06-01 |
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.
- Generate a calendar date series from '2025-05-26' to '2025-06-01' and LEFT JOIN it to the aggregated results to include dates with zero views.