Coding questions · Meta
Define and compute shop visibility in SQL
This question evaluates the ability to define and compute shop-level visibility KPIs in SQL, testing skills in metric design (visibility_rate and impression_share), deduplication, session/window filtering, and data-quality concerns such as late-arriving events and bot filtering.
You own the 'shop visibility' KPI for a marketplace. Define a precise metric and write SQL to compute it over the last 7 days (use today = 2025-09-01, so the window is 2025-08-26 to 2025-09-01 inclusive). Provide two related definitions: (A) visibility_rate = unique US users who saw ≥1 active product from a shop ÷ active US users; (B) impression_share = impressions of a shop's active products ÷ total impressions of all active products. Exclude deactivated products, deduplicate multiple impressions of the same product by the same user within a session, and restrict the denominator to 'active users' (users with ≥1 session in the window). Return: shop_id, visibility_rate, impression_share, unique_viewers, active_users, window_start, window_end. Then: (1) show how you'd parameterize this per-surface (feed vs shoppage), (2) handle late-arriving impressions and bot traffic, and (3) outline indexes or clustering to make the query efficient at 1B+ rows.
Schema and small ASCII samples you must use:
users(user_id INT, country STRING)
+---------+---------+
| user_id | country |
+---------+---------+
| 1 | US |
| 2 | US |
| 3 | CA |
| 4 | US |
+---------+---------+
shops(shop_id INT, name STRING)
+---------+----------+
| shop_id | name |
+---------+----------+
| 10 | Alpha |
| 20 | Beta |
+---------+----------+
products(product_id INT, shop_id INT, is_active BOOLEAN, created_at DATE)
+------------+---------+-----------+------------+
| product_id | shop_id | is_active | created_at |
+------------+---------+-----------+------------+
| 100 | 10 | true | 2025-08-01 |
| 101 | 10 | true | 2025-08-20 |
| 102 | 20 | true | 2025-07-15 |
| 103 | 20 | false | 2025-06-10 |
+------------+---------+-----------+------------+
impressions(impression_id BIGINT, ts TIMESTAMP, user_id INT, product_id INT, surface STRING)
+---------------+---------------------+---------+------------+----------+
| impression_id | ts | user_id | product_id | surface |
+---------------+---------------------+---------+------------+----------+
| 1 | 2025-08-26 10:00:00 | 1 | 100 | feed |
| 2 | 2025-08-27 11:00:00 | 2 | 100 | feed |
| 3 | 2025-08-27 12:00:00 | 2 | 101 | shoppage |
| 4 | 2025-08-28 09:00:00 | 4 | 102 | feed |
| 5 | 2025-09-01 13:00:00 | 1 | 102 | feed |
+---------------+---------------------+---------+------------+----------+
sessions(session_id BIGINT, user_id INT, session_start TIMESTAMP)
+------------+---------+---------------------+
| session_id | user_id | session_start |
+------------+---------+---------------------+
| 500 | 1 | 2025-08-28 08:00:00 |
| 501 | 2 | 2025-08-29 09:00:00 |
| 502 | 3 | 2025-08-30 10:00:00 |
| 503 | 4 | 2025-08-31 12:00:00 |
+------------+---------+---------------------+
Assume a SQL dialect with CTEs and DATE/TIMESTAMP functions. Write the full query for (A) and (B) and briefly explain how you'd adapt it for incremental daily jobs and backfills.
Compute shop visibility_rate and impression_share (US, active users, last 7 days)
You own the “shop visibility” KPI for a marketplace.
Using today = 2025-09-01, compute metrics for the window 2025-08-26 to 2025-09-01 (inclusive).
Definitions (US only): (A) visibility_rate = (unique active US users who saw ≥1 active product from a given shop) ÷ (active US users) (B) impression_share = (deduplicated impressions of a shop’s active products) ÷ (deduplicated impressions of all active products)
Rules:
- Exclude deactivated products (products.is_active = true only).
- Restrict the denominator to “active users”: US users with ≥1 session in the window.
- Deduplicate multiple impressions of the same product by the same user within a session. Because impressions has no session_id, approximate “session” as (user_id, DATE(ts)). Deduplicate using (user_id, product_id, DATE(ts), surface) so repeated impressions in the same day/session count once.
Return one row per shop with these columns: shop_id, visibility_rate, impression_share, unique_viewers, active_users, window_start, window_end
Notes:
- visibility_rate and impression_share should be decimals.
- Include shops even if they have zero impressions in the window (metrics should be 0 in that case).
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 |
| US | 4 |
Table shops
| column | type | constraints |
|---|---|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(50) | NOT NULL |
Sample data:
| name | shop_id |
|---|---|
| Alpha | 10 |
| Beta | 20 |
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| shop_id | INT | NOT NULL |
| is_active | BOOLEAN | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | is_active | product_id | shop_id |
|---|---|---|---|
| 2025-08-01 | true | 100 | 10 |
| 2025-08-20 | true | 101 | 10 |
| 2025-07-15 | true | 102 | 20 |
| 2025-06-10 | false | 103 | 20 |
Table impressions
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY, NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| user_id | INT | NOT NULL |
| product_id | INT | NOT NULL |
| surface | VARCHAR(20) | NOT NULL |
Sample data:
| impression_id | product_id | surface | ts | user_id |
|---|---|---|---|---|
| 1 | 100 | feed | 2025-08-26 10:00:00 | 1 |
| 2 | 100 | feed | 2025-08-27 11:00:00 | 2 |
| 3 | 101 | shoppage | 2025-08-27 12:00:00 | 2 |
| 4 | 102 | feed | 2025-08-28 09:00:00 | 4 |
| 5 | 102 | feed | 2025-09-01 13:00:00 | 1 |
Table sessions
| column | type | constraints |
|---|---|---|
| session_id | BIGINT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| session_start | TIMESTAMP | NOT NULL |
Sample data:
| session_id | session_start | user_id |
|---|---|---|
| 500 | 2025-08-28 08:00:00 | 1 |
| 501 | 2025-08-29 09:00:00 | 2 |
| 502 | 2025-08-30 10:00:00 | 3 |
| 503 | 2025-08-31 12:00:00 | 4 |
Expected output
One row per shop. active_users is the count of US users with ≥1 session in the window. unique_viewers is the distinct count of those active US users who saw at least one active product from the shop (after dedup). visibility_rate = unique_viewers/active_users. impression_share = shop_dedup_impressions / total_dedup_impressions across all shops (active products only, active US users only).
| active_users | impression_share | shop_id | unique_viewers | visibility_rate | window_end | window_start |
|---|---|---|---|---|---|---|
| 3 | 0.6 | 10 | 2 | 0.6666666667 | 2025-09-01 | 2025-08-26 |
| 3 | 0.4 | 20 | 2 | 0.6666666667 | 2025-09-01 | 2025-08-26 |
Hints
- Build an active US user set from sessions first; it will be the denominator for visibility_rate.
- Deduplicate impressions before joining to products; then filter products.is_active = true.
- Use LEFT JOIN from shops to include shops with zero impressions, and guard divides with NULLIF.
Parameterize the KPI by surface (example: compute only for surface = 'feed')
Compute the same output as in Question 1, but parameterized for a single surface.
Use surface = 'feed' for this question. Apply the surface filter to impressions before deduplication.
Return:
shop_id, surface, visibility_rate, impression_share, unique_viewers, active_users, window_start, window_end
Notes:
- Active users are still defined from sessions (US users with ≥1 session in the window), independent of surface.
- Include shops even if they have zero impressions on that surface in the window.
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 |
| US | 4 |
Table shops
| column | type | constraints |
|---|---|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(50) | NOT NULL |
Sample data:
| name | shop_id |
|---|---|
| Alpha | 10 |
| Beta | 20 |
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| shop_id | INT | NOT NULL |
| is_active | BOOLEAN | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | is_active | product_id | shop_id |
|---|---|---|---|
| 2025-08-01 | true | 100 | 10 |
| 2025-08-20 | true | 101 | 10 |
| 2025-07-15 | true | 102 | 20 |
| 2025-06-10 | false | 103 | 20 |
Table impressions
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY, NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| user_id | INT | NOT NULL |
| product_id | INT | NOT NULL |
| surface | VARCHAR(20) | NOT NULL |
Sample data:
| impression_id | product_id | surface | ts | user_id |
|---|---|---|---|---|
| 1 | 100 | feed | 2025-08-26 10:00:00 | 1 |
| 2 | 100 | feed | 2025-08-27 11:00:00 | 2 |
| 3 | 101 | shoppage | 2025-08-27 12:00:00 | 2 |
| 4 | 102 | feed | 2025-08-28 09:00:00 | 4 |
| 5 | 102 | feed | 2025-09-01 13:00:00 | 1 |
Table sessions
| column | type | constraints |
|---|---|---|
| session_id | BIGINT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| session_start | TIMESTAMP | NOT NULL |
Sample data:
| session_id | session_start | user_id |
|---|---|---|
| 500 | 2025-08-28 08:00:00 | 1 |
| 501 | 2025-08-29 09:00:00 | 2 |
| 502 | 2025-08-30 10:00:00 | 3 |
| 503 | 2025-08-31 12:00:00 | 4 |
Expected output
Same as Question 1, but restricted to impressions where surface = 'feed'. impression_share is computed within the filtered (feed-only) impression universe.
| active_users | impression_share | shop_id | surface | unique_viewers | visibility_rate | window_end | window_start |
|---|---|---|---|---|---|---|---|
| 3 | 0.5 | 10 | feed | 2 | 0.6666666667 | 2025-09-01 | 2025-08-26 |
| 3 | 0.5 | 20 | feed | 2 | 0.6666666667 | 2025-09-01 | 2025-08-26 |
Hints
- Treat surface as a parameter (e.g., a one-row CTE or bind variable) and filter impressions early.
- Compute impression_share relative to the total impressions after applying the same surface filter.
- Left join aggregates to shops so shops with 0 impressions still appear.