Coding questions · Meta
Write SQL with HAVING and efficient joins
This question evaluates proficiency in SQL aggregation and filtering (GROUP BY and HAVING), join selection and performance, date-range filtering, handling zero denominators and numeric rounding, plus schema indexing for query efficiency.
You are given two tables.
Schema
- interactions(product_id INT, buyer_id INT, seller_id INT, interaction_date DATE, interaction_type VARCHAR, interaction_count INT)
- products(product_id INT PRIMARY KEY, country VARCHAR, category VARCHAR)
Sample data
products
product_id | country | category
1 | US | electronics
2 | US | apparel
3 | CA | electronics
4 | US | home
interactions
product_id | buyer_id | seller_id | interaction_date | interaction_type | interaction_count
1 | 101 | 201 | 2025-08-26 | view | 3
1 | 102 | 201 | 2025-08-27 | validate | 2
1 | 101 | 201 | 2025-08-28 | click | 1
1 | 103 | 201 | 2025-08-29 | validate | 4
2 | 104 | 202 | 2025-08-30 | validate | 5
2 | 105 | 202 | 2025-08-31 | view | 2
2 | 106 | 202 | 2025-09-01 | validate | 1
4 | 107 | 204 | 2025-08-27 | view | 7
3 | 108 | 203 | 2025-08-26 | validate | 2
1 | 104 | 201 | 2025-08-30 | view | 1
Assume "today" is 2025-09-01. Answer the following with ANSI SQL and explain any assumptions: (a) Return the number of products whose distinct buyer count is > 3 and whose total interaction_count (summing across all rows and dates) is > 10. Be careful to count DISTINCT buyers per product across the full history, not per day. Use GROUP BY correctly and justify your use of HAVING vs WHERE for the thresholds. (b) Compute the percentage of 'validate' interactions for US products in the past 7 days (inclusive window 2025-08-26 through 2025-09-01): numerator = sum of interaction_count where interaction_type = 'validate'; denominator = sum of interaction_count across all interaction types; both restricted to US products and the 7-day window. Use an INNER JOIN to filter to US products and explain why INNER JOIN is preferable to LEFT JOIN here. Specify how you handle a zero denominator (return 0.0 vs NULL) and how you round. (c) Show the exact SQL for (a) and (b). Then list indexes you would add on each table to make these queries efficient, and explain why HAVING is required for post-aggregation filters while WHERE is not.
Count products by distinct buyers and total interactions
You are given two tables:
- interactions(product_id INT, buyer_id INT, seller_id INT, interaction_date DATE, interaction_type VARCHAR, interaction_count INT)
- products(product_id INT PRIMARY KEY, country VARCHAR, category VARCHAR)
Using the interactions table, return the number of products whose distinct buyer count across all time is greater than 3 and whose total interaction_count (summing across all rows, dates, and interaction types) is greater than 10.
Details:
- Count DISTINCT buyers per product across the full history (not per day).
- Apply both thresholds (distinct buyer count > 3 and total interaction_count > 10) at the product level.
- The output should be a single row with one column: product_count.
- Use GROUP BY on product_id and HAVING to apply the aggregated thresholds.
Assume the sample data below is representative of the full contents of the tables.
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY |
| country | VARCHAR(2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| category | country | product_id |
|---|---|---|
| electronics | US | 1 |
| apparel | US | 2 |
| electronics | CA | 3 |
| home | US | 4 |
Table interactions
| column | type | constraints |
|---|---|---|
| product_id | INT | NOT NULL |
| buyer_id | INT | NOT NULL |
| seller_id | INT | NOT NULL |
| interaction_date | DATE | NOT NULL |
| interaction_type | VARCHAR(20) | NOT NULL |
| interaction_count | INT | NOT NULL |
Sample data:
| buyer_id | interaction_count | interaction_date | interaction_type | product_id | seller_id |
|---|---|---|---|---|---|
| 101 | 3 | 2025-05-26 | view | 1 | 201 |
| 102 | 2 | 2025-05-27 | validate | 1 | 201 |
| 101 | 1 | 2025-05-28 | click | 1 | 201 |
| 103 | 4 | 2025-05-29 | validate | 1 | 201 |
| 104 | 5 | 2025-05-30 | validate | 2 | 202 |
| 105 | 2 | 2025-05-31 | view | 2 | 202 |
| 106 | 1 | 2025-06-01 | validate | 2 | 202 |
| 107 | 7 | 2025-05-27 | view | 4 | 204 |
| 108 | 2 | 2025-05-26 | validate | 3 | 203 |
| 104 | 1 | 2025-05-30 | view | 1 | 201 |
Expected output
Single row giving the count of products whose distinct buyer count is greater than 3 and whose total interaction_count is greater than 10, across all dates and interaction types.
| product_count |
|---|
| 1 |
Hints
- First aggregate by product_id to compute COUNT(DISTINCT buyer_id) and SUM(interaction_count).
- Use HAVING, not WHERE, to filter on the aggregated buyer and interaction counts, then count qualifying products in an outer query.
Percentage of validate interactions for US products over a 7-day window
Using the same two tables:
- interactions(product_id INT, buyer_id INT, seller_id INT, interaction_date DATE, interaction_type VARCHAR, interaction_count INT)
- products(product_id INT PRIMARY KEY, country VARCHAR, category VARCHAR)
Compute the percentage of 'validate' interactions for US products in the 7-day window from 2025-05-26 through 2025-06-01 (inclusive).
Definitions:
- Consider only interactions whose product is in the US (products.country = 'US') and whose interaction_date is between '2025-05-26' and '2025-06-01' inclusive.
- Numerator: sum of interaction_count where interaction_type = 'validate'.
- Denominator: sum of interaction_count across all interaction types.
- Both numerator and denominator must be restricted to US products in the given 7-day window.
Output:
- Return a single row with one column: validate_percentage.
- validate_percentage should be a numeric value representing the percentage from 0 to 100, rounded to two decimal places.
- If the denominator is zero, return 0.00 instead of NULL.
Implementation requirements:
- Use an INNER JOIN from interactions to products to restrict to US products.
- Filter the date range in a WHERE clause using explicit dates (no relative 'today' logic).
Assume the sample data below is representative of the full contents of the tables.
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY |
| country | VARCHAR(2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| category | country | product_id |
|---|---|---|
| electronics | US | 1 |
| apparel | US | 2 |
| electronics | CA | 3 |
| home | US | 4 |
Table interactions
| column | type | constraints |
|---|---|---|
| product_id | INT | NOT NULL |
| buyer_id | INT | NOT NULL |
| seller_id | INT | NOT NULL |
| interaction_date | DATE | NOT NULL |
| interaction_type | VARCHAR(20) | NOT NULL |
| interaction_count | INT | NOT NULL |
Sample data:
| buyer_id | interaction_count | interaction_date | interaction_type | product_id | seller_id |
|---|---|---|---|---|---|
| 101 | 3 | 2025-05-26 | view | 1 | 201 |
| 102 | 2 | 2025-05-27 | validate | 1 | 201 |
| 101 | 1 | 2025-05-28 | click | 1 | 201 |
| 103 | 4 | 2025-05-29 | validate | 1 | 201 |
| 104 | 5 | 2025-05-30 | validate | 2 | 202 |
| 105 | 2 | 2025-05-31 | view | 2 | 202 |
| 106 | 1 | 2025-06-01 | validate | 2 | 202 |
| 107 | 7 | 2025-05-27 | view | 4 | 204 |
| 108 | 2 | 2025-05-26 | validate | 3 | 203 |
| 104 | 1 | 2025-05-30 | view | 1 | 201 |
Expected output
Single row with the percentage (0–100) of interaction_count that comes from 'validate' interactions for US products between 2025-05-26 and 2025-06-01 inclusive, rounded to two decimal places.
| validate_percentage |
|---|
| 46.15 |
Hints
- Join interactions to products with an INNER JOIN, filter country = 'US' and the date window in the WHERE clause.
- Use SUM with a CASE expression to compute the validate-only numerator and the overall denominator, then compute a percentage and guard against a zero denominator with CASE.