Analyze Seller Activity and Vehicle Listing Interactions

Q: Analyze Seller Activity and Vehicle Listing Interactions

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Analyze Seller Activity and Vehicle Listing Interactions

Scenario

Question

Hints

Comments (0)