Calculate Daily Visibility Score for Each Shop
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
shop_events
| user_id | event_time | event_type | product_id | shop_id |
|---------|------------|------------|------------|---------|
| 101 | 2023-07-01 | impression | 5501 | 12 |
| 101 | 2023-07-01 | view | 5501 | 12 |
| 102 | 2023-07-01 | impression | 6602 | 12 |
| 103 | 2023-07-01 | view | 7703 | 15 |
| 104 | 2023-07-02 | impression | 5501 | 12 |
##### Scenario
The analytics team wants a daily visibility score for each shop, defined as views divided by impressions.
##### Question
Write a SQL query that, for every shop and calendar day, returns impressions, views, and visibility_ratio = views/impressions, ordered by event_date and shop_id.
##### Hints
Use CASE or FILTER clauses for conditional aggregation.
Overview: This question evaluates a candidate's ability to perform conditional aggregation and time-based grouping to compute derived metrics such as impressions, views, and a visibility ratio per shop and calendar day.
Given a table of shop events, write a SQL query that, for every shop and calendar day, returns the number of impressions, the number of views, and a visibility_ratio defined as views divided by impressions. If impressions is 0, visibility_ratio should be NULL. Order the result by event_date and shop_id.
Tables
shop_events(user_id INTEGER, event_time DATE, event_type VARCHAR, product_id INTEGER, shop_id INTEGER)
Hints
- Use CASE expressions for conditional aggregation (or FILTER if your SQL dialect supports it).
- Cast counts to a decimal type and use NULLIF(impressions, 0) in the denominator to avoid division by zero.