Analyze Seller Activity and Vehicle Listing Interactions
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
listing_interaction
+-----------+-----------+------------+------------+----+
| buyer_id | seller_id | date | product_id | li |
+-----------+-----------+------------+------------+----+
| 123 | 456 | 2019-01-01 | 4325 | 7 |
| 32 | 789 | 2019-01-01 | 9395 | 3 |
| 456 | 32 | 2019-01-01 | 879 | 1 |
+-----------+-----------+------------+------------+----+
dim_all_product
+------------+----------+------------+------------+---------+
| product_id | category | date | create_date| country |
+------------+----------+------------+------------+---------+
| 123 | Vehicle | 2019-01-01 | 2018-12-21 | US |
| 32 | Home | 2019-01-01 | 2018-11-01 | CA |
| 456 | Housing | 2019-01-01 | 2018-12-15 | UK |
+------------+----------+------------+------------+---------+
##### Scenario
Marketplace SQL analysis for buyer–seller interactions and product categories.
##### Question
1A) For the last 3 calendar days, how many distinct sellers have more than three products where each product has li > 1? 1B) Among listings created in the US within the last 7 days, what percentage of total listing interactions (li) comes from the ‘Vehicle’ category?
##### Hints
Use windowed date filters, group by seller_id/product_id, and conditional aggregation for percentages.
Overview: This interview question evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer for Analyze Seller Activity and Vehicle Listing Interactions states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.
Solution
# Solution Alignment
The prompt asks for an implementation-level answer. The safest way to present it is to define the state, maintain clear invariants, then walk through complexity and tests.
## Problem Restatement
listing_interaction +-----------+-----------+------------+------------+----+ | buyer_id | seller_id | date | product_id | li | +-----------+-----------+------------+------------+----+ | 123 | 456 | 2019-01-01 | 4325 | 7 | | 32 | 789 | 2019-01-01 | 9395 | 3 | | 456 | 32 | 2019-01-01 | 879 | 1 | +-----------+-----------+------------+------------+----+ dim_all_product +------------+----------+------------+------------+---------+ | product_id | category | date | create_date| country | +------------+----------+------------+------------+---------+ | 123 | Vehicle | 2019-01-01 | 2018-12-21 | US | | 32 | Home | 2019-01-01 | 2018-11-01 | CA | | 456 | Housing | 2019-01-01 | 2018-12-15 | UK | +------...
## Recommended Approach
Start with a brute-force baseline to confirm correctness, then identify the repeated work or ordering property that enables a better data structure such as a hash map, heap, stack, queue, two pointers, prefix sums, BFS/DFS, or dynamic programming. Write the implementation around a small invariant and test that invariant directly.
## Correctness
The implementation should maintain an invariant after each loop or operation that directly matches the problem statement. At termination, that invariant implies the returned value has considered every valid candidate exactly once, or has preserved the required data-structure state after every API call.
## Complexity
State the baseline complexity and the optimized complexity. For most interview constraints, justify why the optimized approach meets the expected input size.
## Edge Cases and Tests
Empty and singleton inputs, duplicates, ties, invalid inputs, boundary values, and tests that exercise the main invariant.