Analyze Seller Activity and Vehicle Listing Interactions
Overview
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.
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.