Generate Daily Unique User Views for Each Shop

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Generate Daily Unique User Views for Each Shop