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.
Quick Answer: This question evaluates SQL- and Python-based data manipulation skills, particularly temporal filtering, joins, group aggregations, and categorical percentage calculations on transactional datasets, assessing competency in deriving per-entity metrics and summarizing interactions.