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(*).
Overview: This question evaluates a candidate's competence in SQL-based data aggregation and relational joins for computing product-level metrics from transaction logs.
Given the tables Interactions(buyer_id, seller_id, product_id, interactions) and Products(product_id, product_name, category), write an SQL query that returns each product_id and its product_name with the total number of interactions across all buyers and sellers. Aggregate the interactions column correctly so that each product appears once with the sum of its interactions.
Tables
Interactions(buyer_id INTEGER, seller_id INTEGER, product_id INTEGER, interactions INTEGER)
Products(product_id INTEGER, product_name VARCHAR, category VARCHAR)
Hints
- JOIN Products to Interactions on product_id.
- Use SUM(interactions) to aggregate the interaction counts rather than COUNT(*).