Coding questions · Meta
Compute shop visibility and intent metrics in SQL
This question evaluates SQL-based data manipulation and analytical competencies in the Data Manipulation (SQL/Python) domain, focusing on deduplication, time-zone-aware timestamp handling, window functions, aggregation, and construction of visibility, CTR, and intent metrics for shops.
Schema (PostgreSQL). Tables:
users(user_id)
shops(shop_id, shop_name, merchant_type)
posts(post_id, shop_id, is_shoppable BOOLEAN, created_at TIMESTAMP)
impressions(user_id, post_id, impression_time TIMESTAMP)
clicks(user_id, post_id, click_time TIMESTAMP, click_type TEXT) -- e.g., 'merchant','like','comment'
Sample data (subset, UTC):
users
+---------+
| user_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
+---------+
shops
+---------+-----------+---------------+
| shop_id | shop_name | merchant_type |
+---------+-----------+---------------+
| 100 | Alpha | brand |
| 101 | Beta | marketplace |
+---------+-----------+---------------+
posts
+---------+---------+--------------+---------------------+
| post_id | shop_id | is_shoppable | created_at |
+---------+---------+--------------+---------------------+
| 10 | 100 | 1 | 2025-08-30 12:00:00 |
| 11 | 100 | 0 | 2025-08-31 09:00:00 |
| 12 | 101 | 1 | 2025-09-01 07:30:00 |
+---------+---------+--------------+---------------------+
impressions
+---------+---------+---------------------+
| user_id | post_id | impression_time |
+---------+---------+---------------------+
| 1 | 10 | 2025-09-01 10:00:00 |
| 1 | 10 | 2025-09-01 10:05:00 |
| 2 | 12 | 2025-09-01 12:00:00 |
| 3 | 10 | 2025-09-01 09:00:00 |
| 3 | 11 | 2025-09-01 11:00:00 |
| 4 | 12 | 2025-09-01 08:30:00 |
+---------+---------+---------------------+
clicks
+---------+---------+---------------------+-------------+
| user_id | post_id | click_time | click_type |
+---------+---------+---------------------+-------------+
| 1 | 10 | 2025-09-01 10:06:00 | merchant |
| 2 | 12 | 2025-09-01 12:20:00 | like |
| 3 | 11 | 2025-09-01 11:05:00 | merchant |
| 4 | 12 | 2025-09-01 08:45:00 | merchant |
+---------+---------+---------------------+-------------+
Tasks (write SQL; use window functions and CASE WHEN; be explicit about deduping and time zones; treat "today" as 2025-09-01):
A) For each shop and calendar date, compute ShopVisibilityScore = 100 * (distinct users who saw ≥1 impression of a shoppable post from that shop that date) / (distinct users with any impression that date). Ensure multiple impressions of the same shop per user per date are deduped. Also report that shop’s CTR that date = merchant clicks on that shop’s shoppable posts / impressions of those posts. Return shop_id, dt, visibility_score, ctr, impressions, unique_viewers.
B) Compute a 7-day rolling visibility_score and CTR per shop ending on 2025-09-01 (inclusive), using windows that avoid leakage across shops, and show the absolute and percentage change vs. the prior 7-day window.
C) Define an implementable PurchaseIntentRate using only the provided tables. Example: among users who had a first shoppable impression for a given shop on a date, the fraction who clicked a 'merchant' link within 60 minutes of that first impression. Write SQL to compute this per shop per date, carefully handling multiple posts, multiple clicks, and users who clicked on non-shoppable posts (should they count or be excluded? justify and implement).
Daily Shop Visibility Score and CTR
Using the tables below, write a PostgreSQL query that, for each shop and each calendar date (in UTC), computes a daily ShopVisibilityScore and CTR. Define the reporting date dt from impression_time and click_time by casting the UTC timestamp to DATE. For each date dt, let the denominator be the number of distinct users who had at least one impression (on any post, any shop) on that date. For each shop_id and date dt, define: (1) unique_viewers = the number of distinct users who saw at least one impression of a shoppable post (is_shoppable = TRUE) from that shop on that date (dedupe multiple impressions of the same shop per user per date), (2) impressions = the total count of impressions of shoppable posts from that shop on that date (no dedup), (3) visibility_score = 100 * unique_viewers / (distinct users with any impression that date), (4) ctr = merchant clicks on that shop's shoppable posts on that date divided by impressions of those posts on that date. Count only clicks where click_type = 'merchant' and the clicked post is shoppable. Return one row per shop_id and dt with columns: shop_id, dt, visibility_score, ctr, impressions, unique_viewers. Use explicit deduping for users and make clear any assumptions about time zone (assume all timestamps are stored in UTC).
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
Sample data:
Table shops
| column | type | constraints |
|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| shop_name | VARCHAR(50) | NOT NULL |
| merchant_type | VARCHAR(20) | NOT NULL |
Sample data:
| merchant_type | shop_id | shop_name |
|---|
| brand | 100 | Alpha |
| marketplace | 101 | Beta |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| shop_id | INT | NOT NULL, FOREIGN KEY REFERENCES shops(shop_id) |
| is_shoppable | BOOLEAN | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | is_shoppable | post_id | shop_id |
|---|
| 2025-08-30 12:00:00 | true | 10 | 100 |
| 2025-08-31 09:00:00 | false | 11 | 100 |
| 2025-09-01 07:30:00 | true | 12 | 101 |
Table impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| impression_time | TIMESTAMP | NOT NULL |
Sample data:
| impression_time | post_id | user_id |
|---|
| 2025-09-01 10:00:00 | 10 | 1 |
| 2025-09-01 10:05:00 | 10 | 1 |
| 2025-09-01 12:00:00 | 12 | 2 |
| 2025-09-01 09:00:00 | 10 | 3 |
| 2025-09-01 11:00:00 | 11 | 3 |
| 2025-09-01 08:30:00 | 12 | 4 |
Table clicks
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| click_time | TIMESTAMP | NOT NULL |
| click_type | VARCHAR(20) | NOT NULL |
Sample data:
| click_time | click_type | post_id | user_id |
|---|
| 2025-09-01 10:06:00 | merchant | 10 | 1 |
| 2025-09-01 12:20:00 | like | 12 | 2 |
| 2025-09-01 11:05:00 | merchant | 11 | 3 |
| 2025-09-01 08:45:00 | merchant | 12 | 4 |
Expected output
One row per shop and calendar date (UTC) with the shop's daily visibility score and CTR over shoppable posts, plus supporting counts. For the sample data all impressions and clicks occur on 2025-09-01, so dt = '2025-09-01' for all rows.
| ctr | dt | impressions | shop_id | unique_viewers | visibility_score |
|---|
| 0.3333 | 2025-09-01 | 3 | 100 | 2 | 50 |
| 0.5 | 2025-09-01 | 2 | 101 | 2 | 50 |
Hints
- Derive the reporting date dt by casting impression_time to DATE in UTC and aggregate first by date, then by shop.
- Use COUNT(DISTINCT ...) for user-level deduplication and filtered aggregates plus a LEFT JOIN to bring in merchant clicks on shoppable posts.
7-Day Rolling Visibility and CTR with Window Functions
Using the same schema and data, first compute the daily per-shop visibility_score and ctr as defined in Question 1. Then, using those daily metrics, compute a 7-day rolling version of each metric per shop, using window functions that do not leak across shops (partition by shop_id). Define visibility_score_7d as the 7-day moving average of the daily visibility_score for that shop, and ctr_7d as the 7-day moving average of the daily ctr for that shop, where each window covers the current date and up to the 6 preceding dates for which there is data. Next, for each shop and window_end_date, compute the previous 7-day window's visibility_score_7d and ctr_7d by looking back 7 rows for that shop, and then calculate both the absolute and percentage change vs this prior window. Return, for each shop, only the row where window_end_date = DATE '2025-09-01', with columns: shop_id, window_end_date, visibility_score_7d, ctr_7d, prev_visibility_score_7d, prev_ctr_7d, visibility_score_change_abs, visibility_score_change_pct, ctr_change_abs, ctr_change_pct. Handle cases where there is no full prior window by returning NULL for the corresponding change metrics.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
Sample data:
Table shops
| column | type | constraints |
|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| shop_name | VARCHAR(50) | NOT NULL |
| merchant_type | VARCHAR(20) | NOT NULL |
Sample data:
| merchant_type | shop_id | shop_name |
|---|
| brand | 100 | Alpha |
| marketplace | 101 | Beta |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| shop_id | INT | NOT NULL, FOREIGN KEY REFERENCES shops(shop_id) |
| is_shoppable | BOOLEAN | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | is_shoppable | post_id | shop_id |
|---|
| 2025-08-30 12:00:00 | true | 10 | 100 |
| 2025-08-31 09:00:00 | false | 11 | 100 |
| 2025-09-01 07:30:00 | true | 12 | 101 |
Table impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| impression_time | TIMESTAMP | NOT NULL |
Sample data:
| impression_time | post_id | user_id |
|---|
| 2025-09-01 10:00:00 | 10 | 1 |
| 2025-09-01 10:05:00 | 10 | 1 |
| 2025-09-01 12:00:00 | 12 | 2 |
| 2025-09-01 09:00:00 | 10 | 3 |
| 2025-09-01 11:00:00 | 11 | 3 |
| 2025-09-01 08:30:00 | 12 | 4 |
Table clicks
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| click_time | TIMESTAMP | NOT NULL |
| click_type | VARCHAR(20) | NOT NULL |
Sample data:
| click_time | click_type | post_id | user_id |
|---|
| 2025-09-01 10:06:00 | merchant | 10 | 1 |
| 2025-09-01 12:20:00 | like | 12 | 2 |
| 2025-09-01 11:05:00 | merchant | 11 | 3 |
| 2025-09-01 08:45:00 | merchant | 12 | 4 |
Expected output
For each shop, the 7-day rolling average of daily visibility_score and ctr ending on 2025-09-01, along with the prior-window metrics and their absolute and percentage changes. With only one day of data in the sample, the 7-day rolling metrics equal the daily metrics and prior-window fields are NULL.
| ctr_7d | ctr_change_abs | ctr_change_pct | prev_ctr_7d | prev_visibility_score_7d | shop_id | visibility_score_7d | visibility_score_change_abs | visibility_score_change_pct | window_end_date |
|---|
| 0.3333 | NULL | NULL | NULL | NULL | 100 | 50 | NULL | NULL | 2025-09-01 |
| 0.5 | NULL | NULL | NULL | NULL | 101 | 50 | NULL | NULL | 2025-09-01 |
Hints
- Reuse the daily per-shop metrics from Question 1 as a subquery, then apply window functions partitioned by shop_id and ordered by dt.
- Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row window and LAG(...) to compare each 7-day window to the one ending 7 rows earlier, guarding against NULL or zero when computing percentage change.
Purchase Intent Rate from Shoppable Impressions and Merchant Clicks
Define a PurchaseIntentRate using only the provided tables and then implement it in SQL. For this question, define PurchaseIntentRate for a given shop_id and date dt (UTC) as follows: (1) Consider only impressions of shoppable posts (is_shoppable = TRUE). For each user_id and shop_id on a given date dt (based on impression_time::date), find that user's first shoppable impression from that shop on that date (first_shoppable_impression_time). (2) A user is counted as showing purchase intent for that shop on that date if they generate at least one click with click_type = 'merchant' on a shoppable post from the same shop, where click_time is between first_shoppable_impression_time and first_shoppable_impression_time + 60 minutes (inclusive of the start, exclusive of the end). Ignore clicks on non-shoppable posts for this metric, even if click_type = 'merchant'. (3) For each shop_id and dt, let users_with_shoppable_impression be the number of distinct users who had at least one shoppable impression from that shop on that date, and users_with_intent be the number of those users who had a qualifying merchant click within 60 minutes as defined above. Define purchase_intent_rate = users_with_intent / users_with_shoppable_impression. Write a PostgreSQL query that returns, for each shop_id and dt present in the data, the columns: shop_id, dt, users_with_shoppable_impression, users_with_intent, purchase_intent_rate. Use window functions to identify each user's first shoppable impression per shop per day and joins plus time filters to find qualifying clicks.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
Sample data:
Table shops
| column | type | constraints |
|---|
| shop_id | INT | PRIMARY KEY, NOT NULL |
| shop_name | VARCHAR(50) | NOT NULL |
| merchant_type | VARCHAR(20) | NOT NULL |
Sample data:
| merchant_type | shop_id | shop_name |
|---|
| brand | 100 | Alpha |
| marketplace | 101 | Beta |
Table posts
| column | type | constraints |
|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| shop_id | INT | NOT NULL, FOREIGN KEY REFERENCES shops(shop_id) |
| is_shoppable | BOOLEAN | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| created_at | is_shoppable | post_id | shop_id |
|---|
| 2025-08-30 12:00:00 | true | 10 | 100 |
| 2025-08-31 09:00:00 | false | 11 | 100 |
| 2025-09-01 07:30:00 | true | 12 | 101 |
Table impressions
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| impression_time | TIMESTAMP | NOT NULL |
Sample data:
| impression_time | post_id | user_id |
|---|
| 2025-09-01 10:00:00 | 10 | 1 |
| 2025-09-01 10:05:00 | 10 | 1 |
| 2025-09-01 12:00:00 | 12 | 2 |
| 2025-09-01 09:00:00 | 10 | 3 |
| 2025-09-01 11:00:00 | 11 | 3 |
| 2025-09-01 08:30:00 | 12 | 4 |
Table clicks
| column | type | constraints |
|---|
| user_id | INT | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| post_id | INT | NOT NULL, FOREIGN KEY REFERENCES posts(post_id) |
| click_time | TIMESTAMP | NOT NULL |
| click_type | VARCHAR(20) | NOT NULL |
Sample data:
| click_time | click_type | post_id | user_id |
|---|
| 2025-09-01 10:06:00 | merchant | 10 | 1 |
| 2025-09-01 12:20:00 | like | 12 | 2 |
| 2025-09-01 11:05:00 | merchant | 11 | 3 |
| 2025-09-01 08:45:00 | merchant | 12 | 4 |
Expected output
For each shop and date, the number of users with at least one shoppable impression, the number of those users who clicked a merchant link on a shoppable post from that shop within 60 minutes of their first shoppable impression that day, and the resulting purchase_intent_rate. All events in the sample occur on 2025-09-01, so dt = '2025-09-01' for all rows.
| dt | purchase_intent_rate | shop_id | users_with_intent | users_with_shoppable_impression |
|---|
| 2025-09-01 | 0.5 | 100 | 1 | 2 |
| 2025-09-01 | 0.5 | 101 | 1 | 2 |
Hints
- Use ROW_NUMBER() partitioned by user_id, shop_id, and impression date to find each user's first shoppable impression per shop per day.
- Join clicks back to these first impressions on user and shop, filter to merchant clicks on shoppable posts within a 60-minute window, then aggregate at shop-date level to compute the rate.