Write SQL with HAVING and efficient joins

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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)

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview