Coding questions · Meta
Calculate Total Interactions for Each Product
This question evaluates a candidate's competence in SQL-based data aggregation and relational joins for computing product-level metrics from transaction logs.
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(*).
Total interactions per product
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.
Table Interactions
| column | type | constraints |
|---|---|---|
| buyer_id | INTEGER | |
| seller_id | INTEGER | |
| product_id | INTEGER | |
| interactions | INTEGER |
Sample data:
| buyer_id | interactions | product_id | seller_id |
|---|---|---|---|
| 101 | 3 | 501 | 201 |
| 102 | 5 | 502 | 202 |
| 103 | 2 | 501 | 201 |
| 101 | 1 | 503 | 203 |
Table Products
| column | type | constraints |
|---|---|---|
| product_id | INTEGER | |
| product_name | VARCHAR | |
| category | VARCHAR |
Sample data:
| category | product_id | product_name |
|---|---|---|
| Accessory | 501 | Phone Case |
| Audio | 502 | Headphones |
| Accessory | 503 | Charger |
Expected output
One row per product that appears in Interactions with the sum of its interactions across all buyer-seller pairs, including the product name.
| product_id | product_name | total_interactions |
|---|---|---|
| 501 | Phone Case | 5 |
| 502 | Headphones | 5 |
| 503 | Charger | 1 |
Hints
- JOIN Products to Interactions on product_id.
- Use SUM(interactions) to aggregate the interaction counts rather than COUNT(*).