Calculate Total Interactions for Each Product
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Interactions
+----------+-----------+------------+--------------+
| buyer_id | seller_id | product_id | interactions |
+----------+-----------+------------+--------------+
| 101 | 201 | 501 | 3 |
| 102 | 202 | 502 | 5 |
| 103 | 201 | 501 | 2 |
| 101 | 203 | 503 | 1 |
+----------+-----------+------------+--------------+
Products
+------------+--------------+-----------+
| product_id | product_name | category |
+------------+--------------+-----------+
| 501 | Phone Case | Accessory |
| 502 | Headphones | Audio |
| 503 | Charger | Accessory |
+------------+--------------+-----------+
##### Scenario
Marketplace platform records every buyer–seller exchange. Table Interactions(buyer_id, seller_id, product_id, interactions) stores the counted exchanges; Products(product_id, product_name, category) stores catalog metadata.
##### Question
Write an SQL query that returns each product_id (and optionally product_name) with the total number of interactions across all buyers and sellers. Ensure you aggregate the interaction field correctly.
##### Hints
JOIN products and use SUM(interactions) rather than COUNT(*).
Quick Answer: This question evaluates a candidate's competence in SQL-based data aggregation and relational joins for computing product-level metrics from transaction logs.