Write SQL with HAVING and efficient joins
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
Read the full Meta Data Scientist interview experience this question came from
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.
Tables
products(product_id INT, country VARCHAR(2), category VARCHAR(50))
interactions(product_id INT, buyer_id INT, seller_id INT, interaction_date DATE, interaction_type VARCHAR(20), interaction_count INT)
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.
Tables
products(product_id INT, country VARCHAR(2), category VARCHAR(50))
interactions(product_id INT, buyer_id INT, seller_id INT, interaction_date DATE, interaction_type VARCHAR(20), interaction_count INT)
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.