Determine Product Buyer Count and Interaction Percentage

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.

Product interaction thresholds and U.S. validate interaction percentage

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.

Table interactions

column	type	constraints
seller_id	INTEGER
buyer_id	INTEGER
product_id	INTEGER
li	INTEGER
create_date	DATE

Sample data:

buyer_id	create_date	li	product_id	seller_id
101	2025-05-26	5	5001	1
102	2025-05-28	12	5002	2
103	2025-05-29	4	5001	1
101	2025-05-30	2	5003	3
104	2025-06-01	7	5002	2

Table products

column	type	constraints
product_id	INTEGER
country	VARCHAR
category	VARCHAR

Sample data:

category	country	product_id
validate	US	5001
search	CA	5002
validate	US	5003
explore	US	5004
validate	FR	5005

Expected output

A single-row result with two metrics: (1) product_count: the count of products whose distinct buyers > 3 and total interaction times (SUM(li)) > 10 over all interactions; (2) us_validate_interaction_pct_last_7_days: the percentage of interaction rows for U.S. products (country = 'US') between '2025-05-26' and '2025-06-01' inclusive whose category is 'validate', rounded to two decimals. Using the provided sample data, no product meets both thresholds, so product_count = 0. All U.S. product interactions in that date range are for category 'validate', so the percentage is 100.00%.

product_count	us_validate_interaction_pct_last_7_days
0	100

Hints

1. GROUP BY product_id and use HAVING with COUNT(DISTINCT buyer_id) and SUM(li).
2. INNER JOIN interactions to products to filter by product country and category.
3. Filter interactions between '2025-05-26' and '2025-06-01' using a date predicate on create_date.
4. Use conditional aggregation (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) to compute the validate percentage.
5. Combine the two metrics in a single SELECT via CTEs and a CROSS JOIN.