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.
Overview: This question evaluates proficiency in relational data aggregation, joining tables, distinct-entity counting, time-window filtering, and calculating interaction proportions.
You are given two tables:
1) interactions(seller_id, buyer_id, product_id, li, create_date)
2) products(product_id, country, category)
Using these tables, write a single SQL query that returns one row with two metrics:
1. product_count: the number of products that have more than 3 distinct buyers and more than 10 total interaction times (SUM(li) > 10), considering all rows in interactions.
2. us_validate_interaction_pct_last_7_days: among interactions for products in the U.S. (country = 'US') with create_date between '2025-05-26' and '2025-06-01' inclusive, the percentage of interaction rows whose product category is 'validate'. Compute the percentage based on number of interaction rows (not li), and round to two decimal places.
Return both metrics in a single row.
Tables
interactions(seller_id INTEGER, buyer_id INTEGER, product_id INTEGER, li INTEGER, create_date DATE)
products(product_id INTEGER, country VARCHAR, category VARCHAR)
Hints
- GROUP BY product_id and use HAVING with COUNT(DISTINCT buyer_id) and SUM(li).
- INNER JOIN interactions to products to filter by product country and category.