Determine Product Buyer Count and Interaction Percentage
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
interactions
+-----------+----------+------------+----+------------+
| seller_id | buyer_id | product_id | li | create_date|
+-----------+----------+------------+----+------------+
| 1 | 101 | 5001 | 5 | 2023-08-01 |
| 2 | 102 | 5002 | 12 | 2023-08-03 |
| 1 | 103 | 5001 | 4 | 2023-08-04 |
| 3 | 101 | 5003 | 2 | 2023-08-05 |
| 2 | 104 | 5002 | 7 | 2023-08-07 |
+-----------+----------+------------+----+------------+
products
+------------+---------+----------+
| product_id | country | category |
+------------+---------+----------+
| 5001 | US | validate |
| 5002 | CA | search |
| 5003 | US | validate |
| 5004 | US | explore |
| 5005 | FR | validate |
+------------+---------+----------+
##### Scenario
An e-commerce marketplace tracks buyer–seller interactions and wants SQL insights using the interactions and products tables.
##### Question
How many products have more than 3 distinct buyers and more than 10 total interaction times? What is the percentage of 'validate' interactions for U.S. products in the last 7 days?
##### Hints
Use GROUP BY with HAVING, COUNT(DISTINCT buyer_id), SUM(li), date filtering and an INNER JOIN between interactions and products.
Quick Answer: This question evaluates proficiency in relational data aggregation, joining tables, distinct-entity counting, time-window filtering, and calculating interaction proportions.