cswork / libraryOpen practice workspace

Coding questions · Meta

Define and query shop visibility

This question evaluates a candidate's ability to design and compute a session‑level visibility metric using SQL and data manipulation techniques, covering joins, date/window handling, deduplication, aggregations, and metric validation.

You are given the following schema. Use only the columns provided; do not introduce new fields or labels.

Tables and columns:

  • shops(shop_id INT, shop_name TEXT, country TEXT)
  • product_impressions(session_id TEXT, ts TIMESTAMP, product_id INT, shop_id INT, page TEXT, rank INT)

Sample data (ASCII):

shops
+---------+-----------+---------+
| shop_id | shop_name | country |
+---------+-----------+---------+
| 1 | Alpha | US |
| 2 | Beta | US |
| 3 | Gamma | CA |
| 4 | Delta | GB |
+---------+-----------+---------+

product_impressions
+------------+---------------------+------------+---------+-----------+------+
| session_id | ts | product_id | shop_id | page | rank |
+------------+---------------------+------------+---------+-----------+------+
| s1 | 2025-08-31 10:00:00 | 101 | 1 | home_feed | 1 |
| s1 | 2025-08-31 10:00:05 | 102 | 2 | home_feed | 7 |
| s2 | 2025-08-31 11:20:00 | 103 | 1 | home_feed | 3 |
| s3 | 2025-08-30 09:00:00 | 104 | 2 | home_feed | 2 |
| s3 | 2025-08-30 09:00:03 | 105 | 3 | home_feed | 6 |
| s4 | 2025-08-25 15:00:00 | 106 | 3 | home_feed | 5 |
| s5 | 2025-08-29 12:00:00 | 107 | 1 | search | 1 |
| s6 | 2025-08-31 14:00:00 | 108 | 2 | home_feed | 4 |
+------------+---------------------+------------+---------+-----------+------+

Define the daily Shop Visibility Score (SVS) for a shop as: among sessions that had at least one home_feed impression on that calendar date (UTC), the fraction of distinct sessions that saw ≥1 impression from that shop with rank <= 5 on home_feed the same date. A session can contribute to multiple shops' numerators if it saw multiple shops in top-5; the denominator is the distinct session count with any home_feed impression that date.

Tasks:

  1. Write a single SQL query (CTEs allowed) that returns, for 2025-08-25 to 2025-08-31 inclusive, one row per (date, shop_id, SVS, numerator_sessions, denominator_sessions). Use only product_impressions and shops.
  2. From your result, return the top 3 shops by 7-day average SVS; break ties by higher total home_feed impressions (same window).
  3. Suppose rank can have gaps (e.g., no rank=1 for a given session). Explain how your query still correctly counts top-5 visibility or adjust it without adding columns.
  4. Identify two failure modes where this SVS could be gamed or biased using only the given schema (e.g., repeated impressions within a session), and propose an SQL-only mitigation for each.

Compute Daily Shop Visibility Score (SVS)

You are given two tables: shops and product_impressions.

Daily Shop Visibility Score (SVS) for a shop on a given UTC calendar date is defined as:

  • Consider only sessions that had at least one home_feed impression on that date.
  • The denominator_sessions is the number of distinct such sessions (regardless of shop or rank).
  • The numerator_sessions for a shop is the number of distinct sessions that saw at least one home_feed impression from that shop with rank <= 5 on that date.
  • A session can contribute to multiple shops' numerators if it saw multiple shops in the top-5; the denominator is shared across all shops for that date.
  • SVS = numerator_sessions / denominator_sessions. If there are no home_feed sessions that day (denominator_sessions = 0), return SVS as NULL.

Write a single SQL query (CTEs allowed) that returns, for every date from 2025-08-25 to 2025-08-31 inclusive and every shop, one row with:

  • impression_date (DATE)
  • shop_id
  • svs (the daily Shop Visibility Score)
  • numerator_sessions
  • denominator_sessions

Use only the shops and product_impressions tables and only their existing columns. Do not assume the existence of any other tables.

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY, NOT NULL
shop_nameVARCHAR(50)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryshop_idshop_name
US1Alpha
US2Beta
CA3Gamma
GB4Delta

Table product_impressions

columntypeconstraints
session_idVARCHAR(50)NOT NULL
tsTIMESTAMPNOT NULL
product_idINTNOT NULL
shop_idINTNOT NULL
pageVARCHAR(50)NOT NULL
rankINTNOT NULL

Sample data:

pageproduct_idranksession_idshop_idts
home_feed1011s112025-08-31 10:00:00
home_feed1027s122025-08-31 10:00:05
home_feed1033s212025-08-31 11:20:00
home_feed1042s322025-08-30 09:00:00
home_feed1056s332025-08-30 09:00:03
home_feed1065s432025-08-25 15:00:00
search1071s512025-08-29 12:00:00
home_feed1084s622025-08-31 14:00:00

Expected output

One row per (impression_date, shop_id) between 2025-08-25 and 2025-08-31 inclusive, including shops and dates with zero home_feed activity. SVS is numerator_sessions / denominator_sessions when denominator_sessions > 0, else NULL.

denominator_sessionsimpression_datenumerator_sessionsshop_idsvs
12025-08-25010
12025-08-25020
12025-08-25131
12025-08-25040
02025-08-2601NULL
02025-08-2602NULL
02025-08-2603NULL
02025-08-2604NULL
02025-08-2701NULL
02025-08-2702NULL
02025-08-2703NULL
02025-08-2704NULL
02025-08-2801NULL
02025-08-2802NULL
02025-08-2803NULL
02025-08-2804NULL
02025-08-2901NULL
02025-08-2902NULL
02025-08-2903NULL
02025-08-2904NULL
12025-08-30010
12025-08-30121
12025-08-30030
12025-08-30040
32025-08-31210.6667
32025-08-31120.3333
32025-08-31030
32025-08-31040

Hints

  1. First compute, per date, how many distinct sessions had any home_feed impression to form the denominator.
  2. Then compute, per date and shop_id, how many distinct sessions had a home_feed impression with rank <= 5, and join this to a calendar of dates and the shops table.

Top Shops by 7-Day SVS and Impressions

Using the same tables and the SVS definition from Question 1, consider the date range 2025-08-25 to 2025-08-31 inclusive.

Define each shop's 7-day SVS over this window as:

  • total_numerator_sessions = sum of numerator_sessions over all days in the window
  • total_denominator_sessions = sum of denominator_sessions over all days in the window (this is the same for all shops)
  • svs_7d = total_numerator_sessions / total_denominator_sessions (NULL if total_denominator_sessions = 0)

Also define total_home_feed_impressions as the total count of rows in product_impressions for that shop where page = 'home_feed' and ts is in the same date range.

Write a single SQL query (CTEs allowed) that returns the top 3 shops ordered by:

  1. svs_7d in descending order, and
  2. if svs_7d is tied, by total_home_feed_impressions in descending order, 3) if still tied, by shop_id ascending.

The output should include:

  • shop_id
  • shop_name
  • country
  • svs_7d
  • total_home_feed_impressions

Use only the shops and product_impressions tables.

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY, NOT NULL
shop_nameVARCHAR(50)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryshop_idshop_name
US1Alpha
US2Beta
CA3Gamma
GB4Delta

Table product_impressions

columntypeconstraints
session_idVARCHAR(50)NOT NULL
tsTIMESTAMPNOT NULL
product_idINTNOT NULL
shop_idINTNOT NULL
pageVARCHAR(50)NOT NULL
rankINTNOT NULL

Sample data:

pageproduct_idranksession_idshop_idts
home_feed1011s112025-08-31 10:00:00
home_feed1027s122025-08-31 10:00:05
home_feed1033s212025-08-31 11:20:00
home_feed1042s322025-08-30 09:00:00
home_feed1056s332025-08-30 09:00:03
home_feed1065s432025-08-25 15:00:00
search1071s512025-08-29 12:00:00
home_feed1084s622025-08-31 14:00:00

Expected output

Top 3 shops over 2025-08-25 to 2025-08-31 by svs_7d (sum of numerator_sessions divided by sum of denominator_sessions across the window), breaking ties by total_home_feed_impressions and then shop_id.

countryshop_idshop_namesvs_7dtotal_home_feed_impressions
US2Beta0.43
US1Alpha0.42
CA3Gamma0.22

Hints

  1. Reuse the per-day numerator and denominator logic from Question 1, then sum over days.
  2. Compute total denominator once (it is common to all shops), and join per-shop total numerators and total home_feed impressions to the shops table before ranking.

Handling Gaps in Rank for Top-5 Visibility

Assume that the rank column in product_impressions can have gaps within a session and date. For example, a session on a given date might only have rows with rank = 2 and rank = 6, with no rank = 1, 3, 4, or 5.

Without adding any new columns or tables, write or justify an SQL version of your SVS computation from Question 1 that still correctly treats home_feed impressions with rank <= 5 as "top-5" positions, even if some rank values are missing on a page.

Produce a query that computes the same daily SVS output as in Question 1 and explain (in comments or reasoning) why it is still correct when rank values are not contiguous.

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY, NOT NULL
shop_nameVARCHAR(50)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryshop_idshop_name
US1Alpha
US2Beta
CA3Gamma
GB4Delta

Table product_impressions

columntypeconstraints
session_idVARCHAR(50)NOT NULL
tsTIMESTAMPNOT NULL
product_idINTNOT NULL
shop_idINTNOT NULL
pageVARCHAR(50)NOT NULL
rankINTNOT NULL

Sample data:

pageproduct_idranksession_idshop_idts
home_feed1011s112025-08-31 10:00:00
home_feed1027s122025-08-31 10:00:05
home_feed1033s212025-08-31 11:20:00
home_feed1042s322025-08-30 09:00:00
home_feed1056s332025-08-30 09:00:03
home_feed1065s432025-08-25 15:00:00
search1071s512025-08-29 12:00:00
home_feed1084s622025-08-31 14:00:00

Expected output

The query should produce the same daily SVS output as in Question 1 for the given sample data. The key point is that using a filter like rank <= 5 counts all impressions in positions 1 through 5 as top-5, regardless of whether any intermediate rank values are missing. Therefore, gaps in rank do not require any change to the logic as long as you filter on rank <= 5.

denominator_sessionsimpression_datenumerator_sessionsshop_idsvs
12025-08-25010
12025-08-25020
12025-08-25131
12025-08-25040
02025-08-2601NULL
02025-08-2602NULL
02025-08-2603NULL
02025-08-2604NULL
02025-08-2701NULL
02025-08-2702NULL
02025-08-2703NULL
02025-08-2704NULL
02025-08-2801NULL
02025-08-2802NULL
02025-08-2803NULL
02025-08-2804NULL
02025-08-2901NULL
02025-08-2902NULL
02025-08-2903NULL
02025-08-2904NULL
12025-08-30010
12025-08-30121
12025-08-30030
12025-08-30040
32025-08-31210.6667
32025-08-31120.3333
32025-08-31030
32025-08-31040

Hints

  1. Think about what "top-5" means: it usually means any item with rank 1 through 5, regardless of whether all values are present.
  2. If you already use a simple filter like WHERE rank <= 5, you do not need to re-rank rows to handle gaps in rank values.

SVS Metric Biases and SQL Mitigations

The SVS definition based on sessions and ranks can be gamed or biased in several ways using only the existing schema (shops and product_impressions).

Identify two distinct failure modes where a shop could artificially inflate or bias its SVS or its tie-breaker metric (total_home_feed_impressions), using only behaviors that manifest in the given tables (for example, repeated impressions within a session, or sessions that only ever show one shop). For each failure mode, propose an SQL-only mitigation that uses only the existing columns.

Write a query that returns two rows, each describing:

  • issue_id (1 or 2)
  • issue (short text description of the failure mode)
  • mitigation_sql_idea (short text describing how you would change the SVS or ranking query in SQL to mitigate that issue)

This query is explanatory: it can use constant strings in the SELECT to describe the issues and mitigations, but it must be valid SQL against the provided schema.

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY, NOT NULL
shop_nameVARCHAR(50)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryshop_idshop_name
US1Alpha
US2Beta
CA3Gamma
GB4Delta

Table product_impressions

columntypeconstraints
session_idVARCHAR(50)NOT NULL
tsTIMESTAMPNOT NULL
product_idINTNOT NULL
shop_idINTNOT NULL
pageVARCHAR(50)NOT NULL
rankINTNOT NULL

Sample data:

pageproduct_idranksession_idshop_idts
home_feed1011s112025-08-31 10:00:00
home_feed1027s122025-08-31 10:00:05
home_feed1033s212025-08-31 11:20:00
home_feed1042s322025-08-30 09:00:00
home_feed1056s332025-08-30 09:00:03
home_feed1065s432025-08-25 15:00:00
search1071s512025-08-29 12:00:00
home_feed1084s622025-08-31 14:00:00

Expected output

Two rows describing two different ways SVS or its tie-breaker could be gamed using only the available schema, plus high-level SQL mitigation ideas. The query uses literal strings to describe the issues and mitigations.

issueissue_idmitigation_sql_idea
Bot or synthetic sessions that only ever see a single shop on home_feed can artificially inflate that shop's SVS.1When computing denominators and numerators, only include sessions where COUNT(DISTINCT shop_id) > 1 on home_feed for that date, e.g., filter sessions in a subquery with HAVING COUNT(DISTINCT shop_id) > 1.
A shop can spam repeated home_feed impressions within the same session to inflate total_home_feed_impressions used as a tie-breaker.2For the tie-breaker, count distinct (session_id, shop_id) or (session_id, product_id) pairs instead of raw impressions, e.g., use COUNT(DISTINCT session_id || '|' || product_id) in the ranking query.

Hints

  1. Think about sessions that are too "pure" (only one shop) and repeated impressions that inflate counts without adding new exposure.
  2. Your query can simply return constant strings that describe each issue and an example SQL pattern that would mitigate it.