The dedup part is where I tripped up first.
Start by clarifying the definitions of 'shoppable post', 'unique viewer', and 'merchant-click', then outline a SQL pipeline that joins impressions to posts and shops, deduplicates impressions per user-post-day, and aggregates daily metrics. Compute the visibility score as the ratio of unique viewers who saw at least one shoppable post from the shop to the total unique viewers that day, and separately calculate CTR, total impressions, and unique viewers for those posts.
Pro tip: Explicitly state your deduplication strategy (e.g., using DISTINCT or ROW_NUMBER) and how you handle edge cases like users with multiple impressions or missing click data, as this demonstrates attention to data quality and metric reliability.
Confirm what constitutes a 'shoppable post', 'unique viewer', and 'merchant-click', and whether the visibility score is per shop per day across all users or only among users who saw any shoppable post.
Deduplicate impressions at the user-post-day level to ensure each user is counted once per post per day, using DISTINCT or ROW_NUMBER to handle duplicate impression records.
For each shop and calendar date, count distinct users who saw at least one shoppable post from that shop, and also compute the total distinct users active that day (or the relevant denominator).
Compute ShopVisibilityScore as the ratio of unique viewers of the shop's shoppable posts to total unique viewers that day; separately calculate merchant-click CTR (clicks/impressions), total impressions, and unique viewers for those posts.
Check for anomalies (e.g., CTR > 100%), ensure date ranges are complete, and present the final table with shop_id, date, visibility_score, ctr, total_impressions, and unique_viewers.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Window functions across shops tripped me up for a second because I initially wrote PARTITION BY shop_id but forgot the date ordering.
First, clarify the data model and confirm the grain (daily per shop). Then, use a window function partitioned by shop and ordered by date to compute the 7-day rolling sum for the current and prior windows, ensuring the window frame is correctly defined. Finally, calculate absolute and percentage changes, handling edge cases like missing days or zero denominators.
Pro tip: Always explicitly define the window frame (e.g., ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and use a date spine to fill gaps, as missing dates can silently break rolling calculations. Also, mention that percentage change should be computed as (current - prior) / NULLIF(prior, 0) to avoid division by zero.
Confirm the metric definition, date range, and that data is daily per shop. Check for missing dates or duplicate entries.
Generate a cross join of all shops and all dates in the range to ensure every shop has a row for each day, filling missing metrics with 0 or NULL as appropriate.
Use a window function partitioned by shop and ordered by date to calculate the 7-day rolling sum for the current window (ending 2025-09-01) and the prior 7-day window (ending 2025-08-25).
Compute the difference and percentage change between the two windows, using NULLIF to handle zero denominators.
Check for anomalies, ensure no cross-shop contamination, and present the final table with shop, current window sum, prior window sum, absolute change, and percentage change.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, clarify the metric definition and edge cases, such as the 60-minute window and handling of multiple impressions. Then, write SQL that identifies each user's first shoppable impression per shop per date, joins to click events within 60 minutes, and computes the fraction. Finally, justify the inclusion/exclusion of non-shoppable clicks based on the metric's intent to measure purchase intent from shoppable content.
Pro tip: Explicitly state your assumptions about the data model (e.g., event timestamps, user IDs) and consider using window functions like ROW_NUMBER() to efficiently get the first impression per user per shop per date.
Restate the metric definition and confirm details like time window, user identification, and what constitutes a 'shoppable impression' and 'merchant link click'. State any assumptions about the tables.
Use a subquery with ROW_NUMBER() partitioned by user, shop, and date, ordered by impression timestamp, to select the first shoppable impression for each user-shop-date.
Left join the first impressions to click events on user_id and shop_id, where click timestamp is between impression timestamp and impression timestamp + 60 minutes.
For each shop and date, calculate the fraction of users with at least one qualifying click divided by the total number of users with a first shoppable impression.
Argue that non-shoppable clicks should be excluded because the metric aims to measure intent from shoppable content; including them would conflate different user behaviors and dilute the metric's meaning.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.