Coding questions · Meta
Write SQL to compute shop visibility share
This question evaluates proficiency in SQL data manipulation and analytical metrics computation, including deduplication, session-level filtering, time-window aggregations, and click-through rate calculations within the Data Manipulation (SQL/Python) domain.
Assume today is 2025-09-01. Compute the top 3 shops by average daily visibility share over the last 7 days (2025-08-26 to 2025-09-01, inclusive) for US, non-bot traffic.
Definitions
- Eligible session (per day): a distinct session_id from a US user (country='US') with is_bot=FALSE that had at least one search on that day with any result in the top 10 positions.
- A shop is visible for a session on a day if it appears at least once in the top 10 in any search within that session on that day. Count each (shop, session_id, day) at most once, even if the shop appears multiple times or in multiple searches that day within the same session.
- Daily visibility share for a shop = visible_sessions_for_shop_that_day / total_eligible_sessions_that_day.
- Average daily visibility share = average of the daily visibility shares across the 7 days (treat missing days as zero visibility for that shop if the day had eligible sessions).
- Tie-breaker: higher average CTR over the same 7-day window, where CTR = clicks/impressions for that shop restricted to top-10 results within eligible sessions. Deduplicate exact duplicate rows in search_results before counting impressions/clicks.
- Ranking threshold: exclude shops with <100 eligible sessions total across the 7 days; for the sample data below, ignore this threshold but keep all other rules.
Schema (standard SQL)
users(user_id INT PRIMARY KEY, country STRING)
shops(shop_id INT PRIMARY KEY, shop_name STRING, category STRING)
searches(search_id INT PRIMARY KEY, user_id INT, session_id STRING, search_ts TIMESTAMP, query STRING, is_bot BOOLEAN)
search_results(search_id INT, position INT, shop_id INT, clicked BOOLEAN)
Sample data (minimal)
users
| user_id | country |
| 1 | US |
| 2 | US |
| 3 | CA |
shops
| shop_id | shop_name | category |
| 101 | Alpha Apparel | Clothing |
| 102 | Beta Bikes | Sports |
| 103 | Gamma Gadgets | Electronics |
searches
| search_id | user_id | session_id | search_ts | query | is_bot |
| 1001 | 1 | s1 | 2025-08-27 09:00:00 | jackets | FALSE |
| 1002 | 2 | s2 | 2025-08-27 09:05:00 | bike | FALSE |
| 1003 | 3 | s3 | 2025-08-27 09:10:00 | gadgets | FALSE |
| 1004 | 1 | s1 | 2025-08-27 09:12:00 | jackets | FALSE |
| 1005 | 2 | s4 | 2025-09-01 10:00:00 | helmet | FALSE |
| 1006 | 2 | s5 | 2025-09-01 10:02:00 | helmet | TRUE |
search_results (deduplicate exact duplicate rows before use)
| search_id | position | shop_id | clicked |
| 1001 | 1 | 101 | TRUE |
| 1001 | 2 | 103 | FALSE |
| 1002 | 1 | 102 | TRUE |
| 1003 | 1 | 103 | FALSE |
| 1004 | 1 | 101 | FALSE |
| 1004 | 2 | 103 | TRUE |
| 1005 | 1 | 102 | FALSE |
| 1005 | 11 | 103 | TRUE |
| 1005 | 1 | 102 | FALSE | <-- duplicate row to test dedup
| 1006 | 1 | 102 | TRUE |
Tasks
- Write a single standard-SQL query (CTEs allowed) that returns the top 3 shops by average daily visibility share over 2025-08-26..2025-09-01 for US, non-bot traffic, applying all the rules above. Output: shop_id, shop_name, avg_daily_visibility_share, tie_breaker_avg_ctr (both as decimals rounded to 4 d.p.).
- Briefly explain how your query avoids double-counting within a session/day and how it handles days with zero eligible sessions.
- Using just the sample data (and ignoring the 100-session filter), what result would your query return?
Top 3 shops by average daily visibility share (US, non-bot, top-10)
Compute the top 3 shops by average daily visibility share over the 7-day window 2025-05-26 to 2025-06-01 (inclusive) for US, non-bot traffic.
Definitions:
- Eligible session (per day): a distinct session_id from a US user (users.country='US') with searches.is_bot=FALSE that had at least one search on that day with any result in the top 10 positions (search_results.position <= 10).
- A shop is visible for a session on a day if it appears at least once in the top 10 in any search within that session on that day. Count each (shop, session_id, day) at most once, even if the shop appears multiple times or in multiple searches that day within the same session.
- Daily visibility share for a shop = visible_sessions_for_shop_that_day / total_eligible_sessions_that_day.
- Average daily visibility share = average of the daily visibility shares across the days in the window that have at least one eligible session. For any such day, if a shop has no visibility that day, treat its daily visibility share as 0.
- Tie-breaker: higher CTR over the same 7-day window, where CTR = clicks/impressions for that shop restricted to top-10 results within eligible sessions. Deduplicate exact duplicate rows in search_results before counting impressions/clicks.
- Ranking threshold: exclude shops with <100 eligible sessions total across the 7 days; for this exercise, ignore this threshold but keep all other rules.
Output columns:
- shop_id
- shop_name
- avg_daily_visibility_share (rounded to 4 decimals)
- tie_breaker_avg_ctr (rounded to 4 decimals)
Order by avg_daily_visibility_share DESC, tie_breaker_avg_ctr DESC, shop_id ASC and return only the top 3 rows.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | user_id |
|---|---|
| US | 1 |
| US | 2 |
| CA | 3 |
Table shops
| column | type | constraints |
|---|---|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| shop_name | VARCHAR(100) | NOT NULL |
| category | VARCHAR(50) |
Sample data:
| category | shop_id | shop_name |
|---|---|---|
| Clothing | 101 | Alpha Apparel |
| Sports | 102 | Beta Bikes |
| Electronics | 103 | Gamma Gadgets |
Table searches
| column | type | constraints |
|---|---|---|
| search_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
| search_ts | TIMESTAMP | NOT NULL |
| query | VARCHAR(200) | |
| is_bot | BOOLEAN | NOT NULL |
Sample data:
| is_bot | query | search_id | search_ts | session_id | user_id |
|---|---|---|---|---|---|
| false | jackets | 1001 | 2025-05-27 09:00:00 | s1 | 1 |
| false | bike | 1002 | 2025-05-27 09:05:00 | s2 | 2 |
| false | gadgets | 1003 | 2025-05-27 09:10:00 | s3 | 3 |
| false | jackets | 1004 | 2025-05-27 09:12:00 | s1 | 1 |
| false | electronics | 1008 | 2025-05-28 08:00:00 | s7 | 1 |
| false | sports gear | 1009 | 2025-05-28 08:05:00 | s8 | 2 |
| false | clearance | 1007 | 2025-05-30 11:00:00 | s6 | 1 |
| false | helmet | 1005 | 2025-06-01 10:00:00 | s4 | 2 |
| true | helmet | 1006 | 2025-06-01 10:02:00 | s5 | 2 |
Table search_results
| column | type | constraints |
|---|---|---|
| search_id | INT | NOT NULL |
| position | INT | NOT NULL |
| shop_id | INT | NOT NULL |
| clicked | BOOLEAN | NOT NULL |
Sample data:
| clicked | position | search_id | shop_id |
|---|---|---|---|
| true | 1 | 1001 | 101 |
| false | 2 | 1001 | 103 |
| true | 1 | 1002 | 102 |
| false | 1 | 1003 | 103 |
| false | 1 | 1004 | 101 |
| true | 2 | 1004 | 103 |
| false | 3 | 1008 | 103 |
| true | 7 | 1008 | 101 |
| true | 2 | 1009 | 103 |
| false | 1 | 1009 | 102 |
| false | 1 | 1005 | 102 |
| true | 11 | 1005 | 103 |
| false | 1 | 1005 | 102 |
| true | 1 | 1006 | 102 |
| true | 12 | 1007 | 101 |
Expected output
Top 3 shops ordered by avg_daily_visibility_share desc, then tie_breaker_avg_ctr desc. Both metrics are rounded to 4 decimals.
| avg_daily_visibility_share | shop_id | shop_name | tie_breaker_avg_ctr |
|---|---|---|---|
| 0.6667 | 102 | Beta Bikes | 0.3333 |
| 0.5 | 103 | Gamma Gadgets | 0.5 |
| 0.3333 | 101 | Alpha Apparel | 0.6667 |
Hints
- Build eligibility at the (session_id, day) grain first, then join searches/results back to that set.
- To avoid double counting visibility, SELECT DISTINCT (shop_id, session_id, day) before counting.
- Generate the 7-day calendar and cross join shops to eligible (nonzero) days to fill missing shop-days with zero share.